SQLsubqueryctewithrecursiveexists

Subqueries, CTEs, EXISTS, UNION, CASE

A subquery is a query used as a value, a list or a table; a CTE names one; EXISTS asks "is there at least one"; and the difference between a correlated and an uncorrelated subquery is the difference between one execution and one per row.

Try queriesInterview questionSee how this works internally →
Progress

Three shapes of subquery

A scalar subquery returns one value and can go anywhere an expression can: SELECT (SELECT max(total) FROM orders) AS biggest. A list subquery feeds IN or = ANY. A table subquery sits in FROM under an alias, often called a derived table. All three are ordinary queries; what changes is how the outer query consumes the result.

A subquery that references a column of the outer query is correlated: it has to be evaluated once per outer row, because its result depends on that row. An uncorrelated one is evaluated once. In EXPLAIN, a correlated subquery shows as a SubPlan with loops = outer rows. A thousand-row outer query with two correlated scalar subqueries runs two thousand queries. The rewrite is almost always a join with GROUP BY.

EXISTS, IN, and the NULL trap

EXISTS (subquery) is true if the subquery returns any row; it stops at the first one. x IN (subquery) is true if x equals any returned value. For positive tests they are interchangeable and the planner usually rewrites both into a semi-join. For negative tests they are not: NOT IN with a subquery that returns a single NULL yields NULL for every row — no error, no rows. NOT EXISTS is NULL-safe. Use it.

The safe and unsafe anti-join
1-- returns NOTHING if any orders.user_id is NULL:
2SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
3 
4-- correct regardless of NULLs:
5SELECT * FROM users u
6WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

CTEs: named subqueries, and when they materialise

WITH name AS (SELECT …) names a subquery so the main query can read like prose and reuse it. Historically PostgreSQL always materialised a CTE — computed it once into a temporary result — which made it an optimisation fence: a WHERE in the outer query could not be pushed into the CTE. Since PostgreSQL 12 a CTE referenced once is inlined like a subquery; MATERIALIZED / NOT MATERIALIZED overrides. Know which you are getting, because it changes the plan.

A recursive CTE is a base query, UNION ALL, and a step query that references the CTE itself. The engine runs the step against the previous step’s output until it produces no rows. That is a breadth-first traversal expressed in SQL: category trees, org charts, bill-of-materials, "all followers of followers". Always ensure the step terminates — a cycle without a depth guard runs until the statement timeout.

Recursion with a depth guard
1WITH RECURSIVE reports AS (
2  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = 42
3  UNION ALL
4  SELECT e.id, e.manager_id, e.name, r.depth + 1
5  FROM employees e JOIN reports r ON e.manager_id = r.id
6  WHERE r.depth < 20              -- the guard against cycles
7)
8SELECT * FROM reports;

UNION, CASE, LATERAL

UNION concatenates two results and removes duplicates, which costs a hash or sort of everything. UNION ALL just concatenates — use it whenever the inputs are disjoint or duplicates are acceptable. INTERSECT and EXCEPT are set intersection and difference.

CASE WHEN … THEN … ELSE … END is SQL’s conditional expression. It turns a continuous value into buckets, drives conditional aggregation (sum(CASE WHEN status = 'paid' THEN total ELSE 0 END)), and pivots rows into columns.

LATERAL lets a subquery in FROM reference columns from tables to its left — a correlated subquery that returns a table. Its signature use is top-N-per-group: for each user, the three newest orders. PostgreSQL supports it; not every engine does.

Top 3 orders per user with LATERAL
1SELECT u.name, o.id, o.total
2FROM users u
3CROSS JOIN LATERAL (
4  SELECT id, total FROM orders
5  WHERE user_id = u.id
6  ORDER BY total DESC LIMIT 3
7) o;

Key points

  • Scalar, list and table subqueries; correlated ones run once per outer row and show as SubPlan loops.
  • NOT IN with a nullable subquery is a silent empty result; NOT EXISTS is safe.
  • CTEs name subqueries; whether they materialise changes the plan. Recursive CTEs are BFS in SQL and need a termination guard.
  • UNION deduplicates at a cost; UNION ALL does not. CASE is the conditional expression. LATERAL is a correlated table subquery.

Try it in the playground

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth, name AS path FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c JOIN tree t ON t.id = c.parent_id
)
SELECT depth, path FROM tree ORDER BY path;
Recursive CTE walking a tree. →
EXPLAIN ANALYZE
SELECT u.id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 3000);
A correlated EXISTS — note the loops. →

When to use — and when not

Use it when
  • Breaking a complex query into readable named steps.
  • Existence tests and anti-joins.
  • Hierarchies and graphs of bounded depth.
Avoid it when
  • Correlated scalar subqueries in the select list of a large query — write the join.
  • Recursive CTEs over deep or cyclic graphs without a guard.

Failure modes

  • NOT IN returning nothing because of one NULL.
  • A CTE that fences off a filter and forces a full scan.
  • Correlated subquery executed 100,000 times.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

How does the planner turn a subquery into a plan?
↓ Database Internals → The Planner: Enumerating Ways to Answer
← Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELFWindow Functions →