CTEs and recursive queries

WITH names a result so the query can be read

A common table expression is a subquery given a name, written before the query that uses it:

WITH paid_orders AS (
  SELECT * FROM orders WHERE status = 'paid'
),
totals AS (
  SELECT customer_id, SUM(total) AS spent
  FROM paid_orders
  GROUP BY customer_id
)
SELECT c.name, t.spent
FROM totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spent > 1000;

Same result as three nested subqueries, read top to bottom instead of inside out. A later CTE may use an earlier one, which is what makes a chain of transformations legible.

Is a CTE materialised?

The honest answer is: it depends on the engine, and it has changed.

This matters when a CTE is referenced twice: inlined, its work may be done twice; materialised, once — but then no filter from the outer query can be pushed into it. If a CTE is expensive and used more than once, say what you want explicitly rather than hoping:

WITH expensive AS MATERIALIZED (
  SELECT ... -- computed once, whatever the planner would have preferred
)

Treating a CTE as a performance tool is where people get surprised. Treat it as a readability tool, and check the plan when it is on a hot path.

Recursive: a query that refers to itself

WITH RECURSIVE has exactly two halves joined by UNION ALL — a starting point, and a rule for the next step:

WITH RECURSIVE subordinates AS (
  -- anchor: where the walk starts
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- recursive step: rows reachable from what we already have
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

The recursive half joins the table against the rows the previous step produced, not against the whole result. It runs again and again until a step produces nothing, and the union of every step is the answer.

Walking upward is the same shape with the join reversed — that is how you build an ancestry path from a node to its root.

UNION ALL, not UNION

UNION deduplicates, which costs a sort or a hash on every step and, worse, can hide the repetition that tells you the walk is not terminating. Use UNION ALL and make termination the query's own business.

Cycles will run forever

A tree cannot loop. Real data can — one bad row where a manager reports to their own subordinate, and the query never ends. Two defences:

-- 1. A depth limit, portable everywhere
WHERE s.depth < 10

-- 2. A visited path, PostgreSQL syntax
) CYCLE id SET is_cycle USING path

The depth limit is not elegant and it works in every engine. On a hierarchy whose depth you cannot bound, carry the path and refuse to extend into a node already in it.

What it replaces

Before recursive CTEs, walking a tree meant a loop in application code: select the children, then select theirs, one round trip per level. The recursive query does it in one, on the machine that already holds the rows — and it is the answer to "find every descendant", "compute the depth of each node", and "give me the path from here to the root".