CTEs and Subqueries

TL;DR: A CTE (WITH name AS (...)) names an intermediate result so a query reads as a linear pipeline instead of subqueries nested three deep. Know the splits: a correlated subquery references the outer row and runs per-row (often slow), an uncorrelated one runs once; a recursive CTE walks hierarchies and graphs; and in some engines a CTE is an optimization fence that is materialized and blocks the planner from pushing predicates through it.

A CTE is a named pipeline stage

A common table expression names a query result you can reference later in the same statement. The payoff is readability: instead of reading a query inside-out, you read it top to bottom, each WITH block a stage. Compare a nested mess to the staged version.

-- nested: read inside-out
SELECT * FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM (SELECT * FROM orders WHERE status = 'paid') p
  GROUP BY customer_id
) t WHERE total > 1000;

-- staged: read top-down
WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
),
totals AS (
  SELECT customer_id, SUM(amount) AS total FROM paid GROUP BY customer_id
)
SELECT * FROM totals WHERE total > 1000;

Same plan in most modern engines, but the second is reviewable. Naming each stage is also how you debug: select from any CTE in isolation to see what it produces. That is why the messier analytics queries are written this way: a cohort retention analysis is one CTE for each user's signup cohort, one for activity with its period offset, one for cohort sizes, and a final join, each stage checkable on its own.

Correlated vs uncorrelated subqueries

An uncorrelated subquery is self-contained and the engine evaluates it once: WHERE region_id IN (SELECT id FROM regions WHERE active). A correlated subquery references a column from the outer query, so conceptually it runs once per outer row:

SELECT o.id
FROM orders o
WHERE o.amount > (
  SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id  -- correlated
);

The o.customer_id reference ties the inner query to each outer row. Planners often rewrite correlated subqueries into joins or window functions, but not always, and a correlated subquery in a SELECT list over a large table is a classic latency surprise. The usual fix is a window function (AVG(amount) OVER (PARTITION BY customer_id)) or a join to a pre-aggregated CTE, both of which compute the per-group value once. The same substitution answers the top-N rows per group question, where ROW_NUMBER() OVER (PARTITION BY ...) replaces a correlated subquery that would otherwise run once per group.

Recursive CTEs for hierarchies and graphs

A recursive CTE has an anchor (the seed rows) and a recursive member that references the CTE itself, joined back until it returns no new rows. This is the standard way to walk an org chart, a category tree, or a bill-of-materials.

WITH RECURSIVE reports AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees WHERE id = 42          -- anchor: the root
  UNION ALL
  SELECT e.id, e.manager_id, e.name, r.depth + 1
  FROM employees e JOIN reports r ON e.manager_id = r.id  -- recurse
)
SELECT * FROM reports;

For a graph with cycles, track the visited path (an array of ids) and exclude already-seen nodes, or set a depth cap, otherwise the recursion never terminates. The same shape does shortest-path-style traversals and transitive closure.

When a CTE is an optimization fence

Here is the trap. In some engines a CTE is materialized: the planner computes it once into a temp result and cannot push a later WHERE predicate down into it.

So a CTE that filters a billion-row table and is then filtered again downstream may scan the whole table if it is fenced.

The fix in old Postgres is to inline manually (use a subquery) or move the predicate up; in new Postgres, drop the MATERIALIZED hint. Materialization is occasionally what you want: a CTE referenced five times that is expensive to recompute should be a fence so it runs once.

Why interviewers probe this

They want to see you turn a nested subquery knot into a clean pipeline and know the performance footguns. The strong-answer move: refactor inside-out subqueries into named CTE stages, replace a per-row correlated subquery with a window function or a join to an aggregated CTE, and reach for WITH RECURSIVE the moment you hear "hierarchy" or "tree." The follow-up they hold back: "you have this CTE referenced once and the query is slow, why?" The expected answer is the optimization-fence question, does your engine materialize it, and is a predicate failing to push down.

Common misconceptions

• "CTEs are always faster than subqueries." Readability differs; performance is usually identical, except when a CTE is a materialization fence that blocks predicate pushdown.
• "A CTE is a temp table." It is scoped to one statement and (in inlining engines) often not materialized at all.
• "Correlated subqueries are just slow subqueries." They reference the outer row and run per-row; the fix is a window function or join, not a tweak.
• "Recursive CTEs are dangerous because of infinite loops." Only on cyclic graphs; track visited nodes or cap depth and they terminate.

Key takeaways

• Use CTEs to turn inside-out nested subqueries into a top-down, reviewable pipeline.
• Correlated subqueries reference the outer row and run per-row; prefer a window function or a join to a pre-aggregated CTE.
• WITH RECURSIVE (anchor + recursive member) walks hierarchies and graphs; guard cycles with a visited set or depth cap.
• A CTE can be an optimization fence (materialized, no predicate pushdown); know your engine's default before blaming the query.