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.

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
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:
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:
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
| Approach | Scope | Can recurse | Materialised? | Cost model |
|---|---|---|---|---|
CTE (WITH) | One statement | Yes, with RECURSIVE | Inlined if used once (PG 12+) | Usually zero extra cost |
MATERIALIZED CTE | One statement | Yes | Always | Full result built, then scanned |
| View | Persistent, reused | No | No | Inlined by the planner |
| Temporary table | Session | No | Always | Write + stats + cleanup |
Subquery in FROM | One statement | No | No | Inlined |
LATERAL subquery | Per outer row | No | Per row | Runs once per driving row |
| Recursive detail | UNION ALL | UNION |
|---|---|---|
| Duplicate rows | Kept | Removed (dedupe cost per step) |
| Cycles | Loop forever without a guard | Terminates when the row repeats |
| Typical use | Trees, graphs with known acyclicity | Ensuring 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:
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
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:
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:
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:
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)
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:
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 symptom | Cause | Fix |
|---|---|---|
recursive query does not have the form non-recursive-term UNION [ALL] recursive-term | More than one UNION, or the CTE referenced in the anchor | Split into two CTEs, or restructure to one UNION ALL |
| Query hangs, memory climbs | Recursive term without a termination guard, or a cycle in the data | Add a depth limit, dedupe with UNION, or add a cycle guard |
column "depth" has type integer but expression has type bigint | Anchor and recursive term disagree on type | Cast in the anchor to the wider type |
relation "x" does not exist | CTE referenced before it is declared | Reorder the WITH list; each CTE only sees earlier ones |
WITH clause containing a data-modifying statement must be at the top level | Modifying CTE nested inside a subquery | Move it to the outermost WITH |
syntax error at or near "WITH" on MySQL 5.7 | CTEs need MySQL 8 | Upgrade, or use derived tables |
| Query slower after moving a subquery into a CTE | Materialisation fence (pre-12, or referenced twice) | Inline it, use NOT MATERIALIZED, or restructure |
column reference "id" is ambiguous inside a recursive term | Anchor and recursive join both expose id | Qualify every column in the recursive term |
| CTE result differs from the equivalent subquery | Aggregate pushed into a materialised CTE | Add 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.