Databases

Basic SELECT Syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column ASC|DESC
LIMIT n;

Selecting Columns

-- All columns (avoid in production – over-fetching)

-- Specific columns SELECT id, first_name, last_name, email FROM customers;

-- Column alias SELECT first_name AS name, email AS contact FROM customers;

-- Computed column SELECT product_name, price, price * 0.9 AS discounted_price FROM products; ```

WHERE Clause Operators

DISTINCT

-- Remove duplicate rows
SELECT DISTINCT country FROM customers;

NULL Handling

NULL is not a value — it means absence of value. Use `IS NULL` and `IS NOT NULL`, never `= NULL`.

JOIN Fundamentals

A JOIN combines rows from two or more tables based on a related column.

INNER JOIN

Returns only rows where the condition is true in both tables.

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table plus matched rows from the right. Unmatched right rows become NULL.

-- All customers, even those with no orders
SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

RIGHT JOIN

Returns all rows from the right table. Rarely used — you can always rewrite as LEFT JOIN by swapping tables.

FULL OUTER JOIN

Returns all rows from both tables. Unmatched sides are NULL.

SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

CROSS JOIN

Cartesian product — every row from left combined with every row from right.

-- All combinations of sizes and colors
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

Self JOIN

Join a table to itself — useful for hierarchical data.

-- Employee and their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOIN on Multiple Conditions

SELECT *
FROM products p
JOIN price_history ph
  ON ph.product_id = p.id
  AND ph.valid_from <= CURRENT_DATE
  AND ph.valid_to > CURRENT_DATE;

What is a Subquery?

A subquery (inner query) is a SELECT statement nested inside another SQL statement. It runs first and its result is used by the outer query.

Subquery in WHERE

-- Customers who spent more than average
SELECT name, email
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE total > (SELECT AVG(total) FROM orders)
);

Correlated Subquery

A correlated subquery references a column from the outer query. It runs once per row of the outer query — can be slow on large datasets.

-- Customers whose last order was more than 90 days ago
SELECT c.name, c.email
FROM customers c
WHERE (
  SELECT MAX(created_at)
  FROM orders
  WHERE customer_id = c.id
) < NOW() - INTERVAL '90 days';

EXISTS and NOT EXISTS

More efficient than IN for large datasets — stops scanning as soon as one match is found.

-- Customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders WHERE customer_id = c.id

-- Customers with no orders SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders WHERE customer_id = c.id ); ```

CTEs (Common Table Expressions)

CTEs make complex queries readable by naming intermediate results.

WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
),
prev_month AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) AS growth_pct
FROM prev_month
ORDER BY month;

Aggregate Functions

GROUP BY

Groups rows with the same values in specified columns into summary rows.

SELECT country, COUNT(*) AS customer_count, SUM(mrr) AS total_mrr
FROM customers
GROUP BY country
ORDER BY total_mrr DESC;

Rule: every column in SELECT must either be in GROUP BY or wrapped in an aggregate function.

HAVING

Filters groups after aggregation (WHERE filters rows before aggregation).

-- Countries with more than 100 customers
SELECT country, COUNT(*) AS cnt
FROM customers
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY cnt DESC;

Combining WHERE and HAVING

-- Countries with more than 10 enterprise customers
SELECT country, COUNT(*) AS enterprise_count
FROM customers
WHERE plan = 'enterprise'      -- filter rows first
GROUP BY country
HAVING COUNT(*) > 10           -- then filter groups
ORDER BY enterprise_count DESC;

ROLLUP and CUBE (Subtotals)

-- Subtotals per country and plan
SELECT country, plan, SUM(mrr) AS mrr
FROM customers
GROUP BY ROLLUP(country, plan);
-- Produces: (country, plan), (country, NULL), (NULL, NULL) rows

What is an Index?

An index is a data structure that speeds up data retrieval at the cost of extra storage and slower writes. Think of it as a book's index — instead of scanning every page, you jump directly to the entry.

B-Tree Index (Default)

CREATE INDEX idx_customers_email ON customers (email);
CREATE INDEX idx_orders_created ON orders (created_at);

Composite (Multi-column) Index

Index on multiple columns — most effective when queries filter on all indexed columns.

CREATE INDEX idx_orders_status_date ON orders (status, created_at);
-- Efficiently serves: WHERE status = 'completed' AND created_at > '2024-01-01'
-- Also serves: WHERE status = 'completed' (leftmost prefix rule)
-- Does NOT efficiently serve: WHERE created_at > '2024-01-01' (only right column)

Partial Index

Index only a subset of rows — smaller, faster.

-- Index only active users (saves space if most users are inactive)
CREATE INDEX idx_users_active ON users (email) WHERE is_active = true;

Covering Index (Index-Only Scan)

Include additional columns so the query can be answered from the index alone.

CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total, status);
-- Query: SELECT total, status FROM orders WHERE customer_id = 42
-- Satisfied entirely from index — no table heap access needed

When NOT to Use Indexes

• Small tables (< 1000 rows): sequential scan is faster.
• Columns with very low cardinality (e.g., boolean `is_active` with 95% true).
• Columns that are rarely used in WHERE, JOIN, or ORDER BY.
• Write-heavy tables where insert/update cost outweighs read benefit.

EXPLAIN ANALYZE

Always verify with execution plans:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'completed' AND created_at > '2024-01-01';
-- Look for: Seq Scan (bad for large tables) vs Index Scan (good)
-- Check actual rows, actual time, and loops