Subqueries and CTEs

A subquery is a query used as a value, a table or a condition inside another. They are how SQL expresses "customers whose latest order was refunded" without an application loop, and they are where two of SQL's oldest traps live: NOT IN against a NULL, and a correlated subquery that runs once per row of a large table. Common table expressions give the same power a shape people can read, and recursive ones walk trees the way a loop would.

#Scalar and table subqueries

A scalar subquery returns one value and can stand anywhere an expression can:

SELECT o.id, o.total,
       o.total - (SELECT AVG(total) FROM orders) AS vs_average
FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders);

It must return at most one row; a second row is a runtime error, and no row becomes NULL. An uncorrelated scalar subquery like the average above runs once. A correlated one references the outer row and conceptually runs once per row:

SELECT c.name,
       (SELECT MAX(placed_at) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM customers c;

This is fine with an index on orders(customer_id, placed_at); the engine does an index lookup per customer. Without the index it is a full scan of orders per customer, which is the quadratic query that was fine at a thousand customers and is not at a million. Both planners can rewrite some correlated subqueries into joins, but not all; the Query optimisation lesson shows how to see whether yours was.

A subquery in FROM is a derived table and needs an alias. It is the tool for "aggregate first, then join", as the Aggregation lesson showed.

#EXISTS versus IN

Both express membership. EXISTS asks "is there at least one row"; IN asks "is this value in that list":

SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'REFUNDED');
 
SELECT c.* FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders WHERE status = 'REFUNDED');

For the positive case both engines produce the same semi-join plan, and either is fine. EXISTS reads better when the condition involves more than one column, and it stops at the first match by definition. Prefer it in application code for consistency with the negative case, where the two are not interchangeable.

#NOT IN and the NULL that returns nothing

SELECT c.* FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);   -- one NULL customer_id in orders → empty result

NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL. The last term is unknown, unknown AND anything is never true, and the WHERE rejects every row. The query does not fail; it returns nothing, and a "no customers without orders" report looks plausible. Nullable foreign keys, optional assignees, a soft-deleted parent: the column is NULL somewhere in most real tables. The rule is absolute: negative membership is NOT EXISTS.

SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

#Common table expressions

A CTE names a subquery at the top of the statement so the body reads top-down:

WITH recent AS (
  SELECT * FROM orders WHERE placed_at >= now() - interval '30 days'
),
per_customer AS (
  SELECT customer_id, COUNT(*) AS n, SUM(total) AS revenue FROM recent GROUP BY customer_id
)
SELECT c.name, p.n, p.revenue
FROM per_customer p JOIN customers c ON c.id = p.customer_id
WHERE p.n >= 3
ORDER BY p.revenue DESC;

Whether a CTE is a performance boundary depends on the engine and its version, and this is the thing to know before an interview or an incident. PostgreSQL 11 and earlier always materialised a CTE: it ran once into a temporary result, and predicates from the outer query could not be pushed into it. That made WITH big AS (SELECT * FROM huge) SELECT * FROM big WHERE id = 5 scan the whole table. PostgreSQL 12 and later inline a CTE that is referenced once and has no side effects, exactly like a subquery, and the keywords MATERIALIZED and NOT MATERIALIZED override the choice. MySQL 8 supports CTEs and its optimiser decides between merging and materialising similarly. A CTE that is referenced twice is materialised on both, which is sometimes the point: compute an expensive set once and use it in two places.

Data-modifying CTEs (WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved) are PostgreSQL only and are the clean way to move rows between tables in one statement.

#Recursive CTEs

Hierarchies live in tables as parent_id columns, and "everyone under this manager" is a loop. A recursive CTE is that loop in SQL:

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 7   -- anchor
  UNION ALL
  SELECT e.id, e.name, e.manager_id, chain.depth + 1                    -- step
  FROM employees e
  JOIN chain ON e.manager_id = chain.id
  WHERE chain.depth < 20                                                -- cycle guard
)
SELECT * FROM chain ORDER BY depth, name;

The anchor seeds the working set; the step joins the table to the rows found so far and appends the result; it stops when a step produces no rows. A cycle in the data (an employee who manages their own manager, through a bad import) makes it run forever, which is why the depth guard is not optional; PostgreSQL 14 adds a CYCLE clause that detects one by tracking the path. Recursive CTEs walk category trees, bill-of-materials explosions, and dependency graphs, and they replace the N+1 loop an ORM would otherwise generate. Both engines support them; MySQL uses the same WITH RECURSIVE syntax.

For a tree read far more often than it changes, consider storing the path (/1/7/42/) or using PostgreSQL's ltree; a recursive query on every page view is a cost you may not want to pay.

#Under the hood: what the planner does with a subquery

A subquery is not executed the way it is written. The planner classifies it and rewrites. An uncorrelated scalar subquery becomes an InitPlan: run once before the main query, its value cached as a parameter. A subquery in FROM is usually pulled up (flattened) into the outer query so its predicates can move and its tables can join with the rest, unless it has GROUP BY, DISTINCT, LIMIT or a window function, which makes it a barrier the outer query treats as a single input. EXISTS and IN subqueries become semi-joins, NOT EXISTS an anti-join, both then subject to the same nested-loop/hash/merge choice as any join; that is why the positive IN and EXISTS produce identical plans and why NOT IN cannot, since its NULL semantics do not match an anti-join.

A correlated subquery is the one that may stay a loop. PostgreSQL turns it into a SubPlan evaluated per outer row unless it can decorrelate it into a join, which it does for EXISTS/IN and for some simple aggregates; MySQL 8 goes further with subquery-to-derived-table transformation for scalar correlated subqueries. When decorrelation fails, EXPLAIN shows the subplan node with loops=<outer row count>, and the fix is to write the join or window yourself. A per-row subplan with an index on the correlated column is one probe per row and fine for thousands; without it, or over millions, it is the quadratic query.

CTEs sit on top of all this. On PostgreSQL 12+ a single-reference, side-effect-free CTE is inlined into the outer query before planning, so it is pulled up, decorrelated and indexed like any subquery. A CTE referenced more than once, or marked MATERIALIZED, or containing a data-modifying statement, is planned as a CTE Scan over a work table filled once: predicates from the outer query do not reach inside it, and the entire result is held (spilling to temp files past work_mem) whether the outer query uses one row or all of them. WITH RECURSIVE is always a work table: the engine runs the anchor into it, then repeats the step against the rows added in the previous iteration only (not the whole table so far), appending each round until an iteration adds nothing.

#Walkthrough: the CTE that was fast until the upgrade

A search endpoint had a query with a CTE that filtered a 200-million-row events table by a user id, then joined the result twice.

WITH mine AS (SELECT * FROM events WHERE user_id = :uid)
SELECT m.*, r.label
FROM mine m
JOIN reactions r ON r.event_id = m.id
WHERE m.kind = 'post'
UNION ALL
SELECT m.*, NULL
FROM mine m
WHERE m.kind = 'comment';

1. On PostgreSQL 11 the CTE was materialised: one index scan on events(user_id) into a work table of a few hundred rows, then two cheap scans over it. Fifteen milliseconds.
2. The team upgraded to 14. The CTE was still materialised, because it is referenced twice, so nothing changed for this query. But a sibling query with a single-reference CTE, which had been written the same way as an optimisation fence, got inlined: WITH recent AS (SELECT * FROM events ORDER BY at DESC LIMIT 1000) SELECT ... FROM recent WHERE user_id = :uid now pushed user_id inside, past the LIMIT, and returned different rows: the user's 1,000 most recent, not the user's rows within the global 1,000.
3. That was a correctness change caught in production, not a performance one, because the CTE's LIMIT had been relied on as a fence. EXPLAIN on 11 showed CTE Scan; on 14 it showed the index scan with the filter inside.
4. Marking that CTE MATERIALIZED restored the old semantics; a review of every WITH in the codebase found four more written as fences and three written for readability, and only the fences got the keyword.
5. The team's rule afterwards: a CTE that depends on being a barrier says MATERIALIZED; one that is meant to be a name says nothing; and any CTE with LIMIT or ORDER BY inside is checked for which it is.

Inlining is right almost always, and the exceptions are the CTEs whose author was using materialisation as a feature without saying so.

#Try it yourself

SELECT p.id, p.title,
       (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS n
FROM posts p WHERE p.author_id = 7;

WITH RECURSIVE n AS (
  SELECT 1 AS v
  UNION ALL
  SELECT v * 2 FROM n WHERE v < 20
)
SELECT * FROM n;

#Misconceptions

• "A subquery runs as written, inner first." The planner flattens, decorrelates and converts to joins; only what it cannot rewrite runs as a per-row subplan.
• "CTEs are always an optimisation fence." Only on PostgreSQL 11 and earlier, or when referenced more than once, or when marked MATERIALIZED. Modern engines inline single-use CTEs.
• "A recursive CTE re-scans everything it has found." Each step sees only the previous iteration's output; the union accumulates the result.
• "IN is slower than EXISTS." They plan to the same semi-join. The difference that matters is NOT IN versus NOT EXISTS, and it is correctness.
• "A correlated subquery with an index is bad practice." It is one probe per outer row, often the best plan for a small outer set; the problem is a large outer set or no index.

#Going deeper

• PostgreSQL manual, "WITH Queries", the paragraphs on inlining and MATERIALIZED, and the PostgreSQL 12 release notes on CTE inlining.
• PostgreSQL manual, "Subquery Expressions" and the EXPLAIN documentation on InitPlan, SubPlan and CTE Scan nodes.
• MySQL reference, "Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions".
• Bruce Momjian, "Explaining the Postgres Query Optimizer" (slides), for the rewrite passes.
• Joe Celko, Trees and Hierarchies in SQL, for adjacency lists, path enumeration and nested sets.