Subqueries & CTEs

scalar and list subqueries

-- scalar: returns one value, usable anywhere an expression is
SELECT name, price, price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- list: returns multiple rows, usable with IN
SELECT name FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = true);

correlated subqueries

A correlated subquery references a column from the outer query — it can't be run on its own, and conceptually re-runs once per outer row (the query planner usually rewrites this into something faster, but the logical meaning is per-row).

SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.amount > 1000
);
-- customers who have at least one order over 1000

EXISTS vs. IN

	EXISTS	IN
NULL handling	unaffected by NULLs in the subquery	`NOT IN` with any NULL in the list returns zero rows — a classic gotcha
Performance	typically stops at the first match	materializes the full list first (though modern planners often optimize this away)
Style	preferred for existence checks	preferred for small, fixed, known lists

The NOT IN + NULL trap is worth internalizing on its own: WHERE id NOT IN (SELECT id FROM t WHERE ...) silently returns zero rows if that subquery can ever produce a NULL. NOT EXISTS doesn't have this problem — prefer it for "not in this set" queries.

CTEs: WITH

WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 10000
)
SELECT c.name, h.total
FROM high_value_customers h
JOIN customers c ON c.id = h.customer_id
ORDER BY h.total DESC;

A CTE names an intermediate result so the main query can read like a sequence of steps instead of a nest of subqueries. In modern PostgreSQL (12+), a non-recursive CTE isn't automatically an "optimization fence" the way it used to be — the planner can inline it, so use CTEs for readability without assuming they force materialization (use MATERIALIZED explicitly if you need that guarantee).

recursive CTEs

WITH RECURSIVE org_chart AS (
  -- base case: the top of the tree
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case: join back to org_chart itself
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

A recursive CTE is how SQL handles hierarchical or graph-shaped data — org charts, category trees, bill-of-materials explosions. It's not actually recursive in the function-call sense; it repeatedly executes the recursive term against the previous iteration's output until that produces zero new rows.

where to go from here

Joins Explained — the fan-out problem a per-metric subquery often solves.
Window Functions — often a cleaner alternative to a correlated subquery for running totals/ranks.