Subqueries & CTEs

**Subquery types**: **scalar subquery** (returns one value, usable as a column expression), **row subquery** (returns one row of multiple columns), **table subquery** (returns rows + columns, usable in FROM), **correlated subquery** (references outer query columns — evaluated per outer row, often expensive).

Correlated subquery example: SELECT c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) FROM customers c. Runs the inner query once per customer → O(N × M) in worst case. The optimizer may rewrite it as a join, but don't rely on it; write the JOIN yourself when it's cleaner.

EXISTS vs IN: WHERE EXISTS (SELECT 1 FROM o WHERE o.cid = c.id) — stops at the first match, NULL-safe, efficient. WHERE id IN (SELECT cid FROM o) — equivalent when cid is not NULL; NOT IN is broken if the subquery returns any NULL. Prefer EXISTS / NOT EXISTS in general.

CTEs (WITH clause): WITH name AS (query) SELECT ... FROM name. Multiple CTEs: WITH a AS (...), b AS (... FROM a) SELECT ... FROM b. Used for linearization of complex logic; modern optimizers (Postgres 12+, SQL Server) inline non-recursive CTEs just like subqueries — no penalty. Pre-PG12, WITH was an optimization fence (materialized), sometimes useful to force planning.

**Materialized vs inline CTEs**: Postgres offers explicit hints `WITH ... AS MATERIALIZED (query)` / `AS NOT MATERIALIZED`. Materialize when the CTE is expensive AND reused many times (computed once). Inline otherwise (allows predicate pushdown into the CTE).

Recursive CTE structure: WITH RECURSIVE t AS (anchor_query UNION ALL recursive_query_referencing_t) SELECT * FROM t. Must have a termination condition (typically join through a parent-child FK until no match). Use for org hierarchies, category trees, BOM explosion, shortest path on small graphs.

**Lateral subqueries (LATERAL JOIN)**: `FROM customers c, LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1) latest_order`. Each outer row feeds the subquery. Solves 'top-N per group' naturally in Postgres / SQL Server.