Part 2 — Querying › Chapter 12

CTEs and recursive queries

WITH for readability, WITH RECURSIVE for trees and hierarchies, and the optimisation fence that surprises people.

From the advert

“Ottima conoscenza del linguaggio SQL” — a 200-line nested query and a 200-line query built from named steps contain the same SQL. Only one of them can be reviewed.

A CTE is a name for a step

🧒 Simple

A recipe that says “put the thing you made in step 2 into the thing you made in step 4” is unreadable. A recipe that says “add the sauce to the base” is not. A CTE is giving a step a name so the next line can say what it means.

Two CTEs in a row: name the aggregation, then name the ranking, then say what you want.
WITH monthly AS (                                    -- step 1, given a name
  SELECT customer_id,                                -- who
         DATE_TRUNC('month', placed_at) AS month,    -- when, truncated to the month
         SUM(total_cents)               AS spend     -- how much, in cents
  FROM orders                                        -- the source table for this step
  WHERE status <> 'cancelled'                        -- row filter, before grouping
  GROUP BY customer_id, DATE_TRUNC('month', placed_at)  -- the expression, repeated: portable
),                                                   -- end of step 1; the comma chains the next
ranked AS (                                          -- step 2, reading step 1 by name
  SELECT *,                                          -- everything monthly produced…
         RANK() OVER (PARTITION BY month             -- …plus a position within each month
                      ORDER BY spend DESC) AS pos    -- biggest spender is 1; ties share a rank
  FROM monthly                                       -- reads the CTE above, by name
)                                                    -- end of step 2
SELECT month, customer_id, spend / 100.0 AS eur      -- cents → euros for display
FROM ranked                                          -- the final query reads the last CTE
WHERE pos <= 3                                       -- top three per month: legal HERE
ORDER BY month, pos;                                 -- chronological, best first within a month
What the names buy, and one thing to watch
  • Each CTE is a step with a name, so the query reads as a sequence of statements rather than nested parentheses. That is the whole point: it is the same query, made reviewable.
  • A CTE is not a temporary table. Most engines inline it, so referencing monthly twice may compute it twice. PostgreSQL before 12 materialised it always; PostgreSQL 12+ chooses, and MATERIALIZED forces the old behaviour.
  • RANK() and not ROW_NUMBER() is a decision: two customers tied for second both get 2, and there is no third. Use DENSE_RANK() if you want a third anyway.

Written as nested derived tables that is the same logic with the reader's eye starting in the middle. Written as a chain of CTEs it reads top to bottom, and each step can be run on its own while you are debugging it — which is the practical benefit nobody mentions.

The optimisation fence: the thing that surprises people

🎓 Is a CTE materialised?

It depends on the engine, and it matters:

  • SQL Server, MySQL 8+ — a CTE is an inline view. It is expanded into the outer query and optimised as a whole, and a CTE referenced twice is evaluated twice.
  • PostgreSQL before 12 — always materialised. This was a genuine optimisation fence, used deliberately to stop predicate push-down.
  • PostgreSQL 12+ — inlined when referenced once and side-effect free; materialised otherwise. You can force either with WITH x AS MATERIALIZED (…) or AS NOT MATERIALIZED.
  • Oracle — decides by cost; /*+ MATERIALIZE */ forces it.
⚠ Trap

“I put it in a CTE so it only runs once” is false in SQL Server and MySQL. If an expensive CTE is referenced three times it runs three times. When you genuinely need single evaluation, materialise it explicitly: a temporary table, or AS MATERIALIZED in PostgreSQL. A temp table also gets statistics, which an inline CTE does not — often the real reason the rewrite is faster.

Recursive CTEs: hierarchies, and how the machine runs them

🧒 Simple

Start with the boss. Ask “who reports to the people I already have?” Write them down. Ask again about the new ones. Keep going until an ask produces nobody new. Now you have the whole company.

A recursive CTE walking a management chain: an anchor, a step, and a guard.
WITH RECURSIVE org AS (                              -- RECURSIVE lets the CTE mention itself
  -- ANCHOR: the starting rows, evaluated once.
  SELECT id, name, manager_id,                       -- the row itself
         1 AS depth,                                 -- level counter starts at 1
         CAST(name AS VARCHAR(1000)) AS path         -- CAST fixes the column's declared width
  FROM employees                                     -- the whole table is available…
  WHERE manager_id IS NULL                           -- …but the anchor keeps only the top

  UNION ALL                                          -- ALL, not UNION: never deduplicate here

  -- RECURSIVE: joins the PREVIOUS ITERATION'S rows, not the whole result so far.
  SELECT e.id, e.name, e.manager_id,                 -- the employee found this round
         o.depth + 1,                                -- one level deeper than their manager
         o.path || ' > ' || e.name                   -- append to the breadcrumb string
  FROM employees e                                   -- everyone, every iteration…
  JOIN org o ON e.manager_id = o.id                  -- …restricted to "manager already found"
  WHERE o.depth < 20                                 -- the cycle guard. Put one in. Always.
)                                                    -- end of the recursive CTE
SELECT depth, path FROM org ORDER BY path;           -- read the finished set like a table
Types and the two traps
  • CAST(name AS VARCHAR(1000)) in the anchor is not decoration. The anchor decides each column's declared type, and the recursive branch must fit it. Without the cast, path is as wide as name and the query fails at depth two with a truncation error.
  • UNION ALL, never UNION: deduplicating each round is expensive and, in a graph with several routes to the same node, wrong.
  • The guard is the difference between a query and an outage. A cycle in the data — an employee who is transitively their own manager — is an infinite loop, and it is bad data, not bad SQL, that puts it there. SQL Server also caps it with OPTION (MAXRECURSION n).
How a recursive CTE iterates The anchor query produces the first working set. Each iteration joins the previous working set to the base table to produce the next, accumulating into the result, until an iteration returns no rows. ANCHOR runs once working set depth 1 working set depth 2 empty → stop UNION ALL accumulates into the result
Each step sees only the previous iteration's rows. That is why a self-join to org terminates.
🎓 The three rules of a recursive CTE
  1. The recursive member may reference the CTE once, and not inside an outer join, aggregate or subquery.
  2. UNION ALL keeps everything and is what you normally want. UNION deduplicates each iteration, which also breaks cycles — at the cost of a sort per step.
  3. Always bound it. A depth column with a WHERE depth < n, or SQL Server's OPTION (MAXRECURSION 100). Real hierarchy data contains cycles — an employee who is their own grandparent — and without a guard the query runs until the server gives up.
Generating a date spine, so a report can show the days on which nothing happened.
-- Every day in 2026, whether or not anything happened on it.
WITH RECURSIVE days(d) AS (                       -- the column list names what the CTE produces
  SELECT DATE '2026-01-01'                        -- anchor: the first day
  UNION ALL                                       -- ALL: never deduplicate a recursive step
  SELECT d + INTERVAL '1 day' FROM days           -- step: yesterday's row plus one day
  WHERE d < DATE '2026-12-31'                     -- the guard, and here also the end condition
)                                                 -- end of the spine
SELECT d, COALESCE(o.n, 0) AS orders              -- a day with no orders reads as 0, not blank
FROM days                                         -- drive from the CALENDAR, not from the data
LEFT JOIN (SELECT CAST(placed_at AS DATE) d,      -- truncate the timestamp to a date…
                  COUNT(*) n                      -- …and count the orders on each
           FROM orders GROUP BY 1) o              -- GROUP BY 1 = by the first select column
       ON o.d = days.d;                           -- one row per day either way
Why you drive from the calendar
  • Grouping orders by day can only ever produce days that have orders. The zero days are missing, and a chart with missing days is a chart that lies. Generating the days and left-joining the data is the general fix.
  • d + INTERVAL '1 day' is PostgreSQL. SQL Server writes DATEADD(day, 1, d); SQLite writes date(d, '+1 day'). The shape is portable, the arithmetic is not.
  • GROUP BY 1 means “by the first column of the select list”. Compact, standard, and worth knowing when you read other people's SQL — though naming the expression is kinder to the next reader.
  • In production, a permanent dim_date table beats generating one every time: it can carry holidays and working-day flags, and it can be indexed. The reference database has one.
Recursive CTE or a hierarchy column?

If you read a tree far more often than you change it, storing a materialised path (/1/14/97/), a nested-set pair, or SQL Server's hierarchyid turns the recursion into a single indexed LIKE '/1/14/%'. It costs more on write and needs maintenance when a node moves. Naming that trade-off is the senior answer; reaching for it before measuring is not.

  1. A CTE names a step. Its main value is readability and being able to run each step alone while debugging.
  2. A CTE is not automatically materialised. In SQL Server and MySQL it is inlined and re-evaluated per reference.
  3. When you need single evaluation, say so: AS MATERIALIZED, or a temp table — which also gets statistics.
  4. A recursive CTE has an anchor and a recursive member joined by UNION ALL.
  5. The recursive member sees only the previous iteration, which is why it terminates.
  6. Always add a depth guard. Production hierarchy data contains cycles.
  7. UNION instead of UNION ALL breaks cycles by deduplication, and pays for a sort each iteration.
  8. Read-heavy trees can be stored as materialised paths and queried without recursion at all.

Interview questions this chapter answers

CTE or subquery — what is the difference?

Mostly readability and reuse: a CTE is named, can be referenced more than once, and can be recursive. Performance-wise they are usually identical because the CTE is inlined — the exception is PostgreSQL, where MATERIALIZED makes it an explicit optimisation fence.

How do you query a hierarchy of unknown depth?

A recursive CTE: an anchor selecting the roots, then a recursive member joining the table back to the CTE, combined with UNION ALL. I would carry a depth column and bound it, because real data contains cycles and an unbounded recursion runs until the server stops it.

Does putting an expensive query in a CTE make it run once?

Not in SQL Server or MySQL — it is inlined, so three references mean three evaluations. To guarantee one evaluation, materialise it: AS MATERIALIZED in PostgreSQL, or a temporary table, which has the side benefit of getting statistics.