Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL CTEs: Common Table Expressions and Recursive WITH Queries Explained

When a CTE is inlined and when it becomes an optimisation fence, plus recursive hierarchies with depth and cycle guards and data-modifying CTEs.

~5 min readSQL
#SQL#Queries#Data Structures
3D illustration of recursive CTE pipelines chaining modular temporary query blocks into an organizational hierarchy

Quick Summary

A CTE (common table expression) is a named subquery declared with WITH and referenced in the statement that follows. It buys readability first: a five-step report becomes five named steps instead of five nested SELECTs. It also gives you recursion — the only standard way to walk a tree in pure SQL — and, on PostgreSQL, the only place a INSERT/UPDATE/DELETE can hand rows to a following query.

Two properties cause almost all of the confusion. A non-recursive CTE is not a temporary table: on PostgreSQL 12 and later it is inlined into the query when it is referenced once, so it can be optimised, but when referenced twice or declared MATERIALIZED it becomes an optimisation fence with its own cost. And a recursive CTE will happily loop forever if its join has no termination condition.

Basic syntax and query signatures

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

Multiple CTEs are comma-separated, and each can reference the ones declared before it — not after:

sql
WITH base AS ( SELECT * FROM orders WHERE status = 'paid' ), per_customer AS ( SELECT customer_id, SUM(total) AS revenue FROM base GROUP BY customer_id ), ranked AS ( SELECT *, RANK() OVER (ORDER BY revenue DESC) AS rnk FROM per_customer ) SELECT * FROM ranked WHERE rnk <= 10;

Recursive form — an anchor term, UNION [ALL], then a recursive term that references the CTE's own name:

sql
WITH RECURSIVE tree AS ( SELECT id, parent_id, name, 1 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree ORDER BY depth, id;

SQL Server and SQLite use WITH without the RECURSIVE keyword; PostgreSQL, MySQL 8, and Oracle require it. Recursion must be a single UNION/UNION ALL connecting exactly two terms in most engines, and the column list is fixed by the anchor.

Key comparison table

ApproachScopeCan recurseMaterialised?Cost model
CTE (WITH)One statementYes, with RECURSIVEInlined if used once (PG 12+)Usually zero extra cost
MATERIALIZED CTEOne statementYesAlwaysFull result built, then scanned
ViewPersistent, reusedNoNoInlined by the planner
Temporary tableSessionNoAlwaysWrite + stats + cleanup
Subquery in FROMOne statementNoNoInlined
LATERAL subqueryPer outer rowNoPer rowRuns once per driving row
Recursive detailUNION ALLUNION
Duplicate rowsKeptRemoved (dedupe cost per step)
CyclesLoop forever without a guardTerminates when the row repeats
Typical useTrees, graphs with known acyclicityEnsuring termination at the price of sorting

Practical edge cases and deep dives

1. The optimisation fence, and when you want one

On PostgreSQL 12+, a CTE referenced exactly once is merged into the outer query and normal planning applies. Referenced twice, it is materialised; force it with MATERIALIZED when the CTE is expensive and you want the result computed once:

sql
WITH expensive AS MATERIALIZED ( SELECT customer_id, percentile_cont(0.5) WITHIN GROUP (ORDER BY total) AS median FROM orders GROUP BY customer_id ) SELECT * FROM expensive WHERE median > 100;

Without MATERIALIZED, the planner may push median > 100 into the CTE and compute the percentile on a filtered set — which changes the answer, not just the speed, because the median is now over a subset. MATERIALIZED guarantees the aggregate is computed over all rows. The reverse hint, NOT MATERIALIZED, opts back into inlining.

That same fence exists by accident across engines: SQL Server and MySQL treat CTEs like derived tables and may materialise them. Reading EXPLAIN is the only honest answer to "is my CTE free?"

2. Recursive trees: depth limits and indexing

sql
WITH RECURSIVE tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, t.depth + 1, t.path || c.id FROM categories c JOIN tree t ON c.parent_id = t.id WHERE t.depth < 10 -- hard stop AND c.id <> ALL(t.path) -- cycle guard ) SELECT id, depth, path FROM tree;

Three guards matter in practice. depth < 10 bounds the work even when the data is wrong. c.id <> ALL(t.path) stops a cycle, which happens with a single bad UPDATE that makes a node its own ancestor; the array grows by one element per level, so keep depth limits tight. And categories (parent_id) needs an index — without it, every level is a sequential scan of the whole table.

PostgreSQL 14 added a built-in cycle clause that does the same thing without the array bookkeeping:

sql
WITH RECURSIVE tree AS ( SELECT id, parent_id, 1 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) CYCLE id SET is_cycle USING path SELECT * FROM tree WHERE NOT is_cycle;

3. Recursive date series — and why it is the wrong tool at scale

Generating a gap-free calendar is the most common non-hierarchical recursion:

sql
WITH RECURSIVE days AS ( SELECT DATE '2026-01-01' AS d UNION ALL SELECT d + 1 FROM days WHERE d < DATE '2026-12-31' ) SELECT d FROM days;

It works, it is readable, and it generates one row per iteration — fine for a year, painful for a decade. generate_series (PostgreSQL) or a calendar table is faster. The join to it is the important part anyway:

sql
SELECT d.d::date, COALESCE(SUM(o.total), 0) AS revenue FROM days d LEFT JOIN orders o ON o.placed_at >= d.d AND o.placed_at < d.d + 1 GROUP BY d.d ORDER BY d.d;

The >= start AND < start + 1 predicate keeps the index on placed_at usable; date_trunc('day', placed_at) = d.d does not, and the query degrades to one scan per day.

4. Data-modifying CTEs (PostgreSQL)

sql
WITH moved AS ( DELETE FROM jobs WHERE state = 'done' AND finished_at < now() - interval '30 days' RETURNING * ), archived AS ( INSERT INTO jobs_archive SELECT * FROM moved RETURNING id ) SELECT count(*) FROM archived;

All statements in one WITH see the same snapshot, so the INSERT cannot see rows that another part of the same statement changed; the rows flow through RETURNING instead. This makes the pattern atomic and avoids a round trip, but it also means the CTE is not a cursor — you cannot page through moved. Data-modifying CTEs must be at the top level of the statement, and a SELECT that ignores archived still executes both modifications; the SELECT count(*) here exists only to return something.

5. CTEs for the "greatest n per group" case

Before window functions were portable, this was the CTE idiom, and it is still the clearest way to express a two-stage filter:

sql
WITH ranked AS ( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY views DESC, id) AS rn FROM posts p WHERE p.published_at IS NOT NULL ) SELECT id, category_id, title, views FROM ranked WHERE rn <= 3;

Every engine that supports window functions also supports CTEs now, so the combination is the portable form. Doing the WHERE inside the CTE rather than outside is what keeps the ranking honest — filter after ranking and a category can end up with fewer than three rows.

Common mistakes and runtime errors

Error or symptomCauseFix
recursive query does not have the form non-recursive-term UNION [ALL] recursive-termMore than one UNION, or the CTE referenced in the anchorSplit into two CTEs, or restructure to one UNION ALL
Query hangs, memory climbsRecursive term without a termination guard, or a cycle in the dataAdd a depth limit, dedupe with UNION, or add a cycle guard
column "depth" has type integer but expression has type bigintAnchor and recursive term disagree on typeCast in the anchor to the wider type
relation "x" does not existCTE referenced before it is declaredReorder the WITH list; each CTE only sees earlier ones
WITH clause containing a data-modifying statement must be at the top levelModifying CTE nested inside a subqueryMove it to the outermost WITH
syntax error at or near "WITH" on MySQL 5.7CTEs need MySQL 8Upgrade, or use derived tables
Query slower after moving a subquery into a CTEMaterialisation fence (pre-12, or referenced twice)Inline it, use NOT MATERIALIZED, or restructure
column reference "id" is ambiguous inside a recursive termAnchor and recursive join both expose idQualify every column in the recursive term
CTE result differs from the equivalent subqueryAggregate pushed into a materialised CTEAdd MATERIALIZED, or filter outside

The habit worth forming: write the CTE, run EXPLAIN, and confirm the plan matches your intent. A WITH that makes the SQL readable but doubles the runtime is a step backwards, and the plan is the only place that difference is visible before production.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.