The WITH clause names a query so you can build on it. Watch a nested subquery unfold into a readable pipeline of steps — then run a recursive CTE one iteration at a time.
A CTE does not make queries more powerful. It makes them readable — and it unlocks recursion.
A Common Table Expression is a named temporary result set defined with WITH, available only to the statement that follows it. Think of it as a variable for a query: compute something once, give it a name, then use that name.
You can — the nested example in this lab returns exactly the same rows. But compare how they read. A nested query is evaluated inside out, so you must find the innermost parenthesis and work outward, holding each layer in your head. A chain of CTEs reads top to bottom, like a recipe.
Each CTE can also reference the ones declared before it, which is what turns a tangle into a pipeline. And a CTE referenced twice is written once, rather than copy-pasted into two places that can drift apart.
This is the thing subqueries genuinely cannot do. A recursive CTE has two halves joined by UNION ALL:
Press Step in the recursive example and watch one level of the org chart appear per iteration. This is how you query hierarchies of unknown depth: reporting lines, folder trees, bill-of-materials, graph traversal, or simply generating a series of numbers or dates.
Always ensure the recursion terminates. If the recursive member never stops producing new rows you have an infinite loop; most engines cap it, but a cycle in your data (A reports to B, B reports to A) will hit that cap rather than finish.Historically PostgreSQL treated CTEs as an optimisation fence — always materialised, never merged into the outer query, which sometimes made them slower than the equivalent subquery. Since version 12 it inlines them by default, with MATERIALIZED and NOT MATERIALIZED available to force the choice. Most other engines have always inlined them. Treat CTEs as free for readability, but check the plan if a query is unexpectedly slow.
A CTE gives a name to an intermediate result, so a complicated query can be written as a sequence of readable steps instead of one deeply nested expression.
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM orders
GROUP BY 1
),
with_growth AS (
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev
FROM monthly
)
SELECT month, revenue, ROUND(100.0 * (revenue - prev) / prev, 1) AS growth_pct
FROM with_growth
WHERE prev IS NOT NULL;Three steps, each named, each readable on its own, and the final query is four lines. The nested-subquery version of the same logic is a single expression three levels deep that nobody enjoys editing six months later.
Two mechanical points: separate multiple CTEs with commas (only the first gets WITH), and later CTEs may reference earlier ones but not the other way round.
WITH RECURSIVE lets a query refer to itself, which is how hierarchies and sequences are handled in SQL.
Every recursive CTE has exactly two halves joined by UNION ALL:
WITH RECURSIVE org AS (
-- anchor: where to start
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- recursive step: one level further, each time
SELECT e.id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY level;The anchor runs once and produces the starting rows. The recursive part then runs repeatedly, each time against the rows produced by the previous round, until a round produces nothing. Then all the results are unioned together.
This is how you walk an organisation chart, expand a bill of materials, trace a comment thread, find every descendant category, or generate a series of dates with no numbers table.
The failure mode to guard against is a cycle. If employee A reports to B and B reports to A, the recursion never ends. Two defences: track the path visited so far and exclude rows already in it, or add a WHERE level < 100 guard. PostgreSQL also offers a CYCLE clause that does the tracking for you.
They help most when a query has repeated logic, several stages, or needs to be understood by someone else. They are the difference between a query a colleague can review and one they have to reverse-engineer.
They do not, by themselves, make anything faster. On modern engines a CTE is inlined into the main query exactly as a derived table would be, so the plan is usually identical. Two exceptions worth knowing:
MATERIALIZED.If a CTE result is used by many queries rather than one, you are looking at a view or a materialised view instead. A CTE lives and dies with its statement.
A CTE gives a subquery a name and puts it before the query instead of inside it. That is mostly a readability change -- and the recursive form is not, because it can express things no ordinary SELECT can.
Use CTEs to turn one unreadable query into several named steps that a colleague can follow. Use WITH RECURSIVE when the data is a hierarchy and you do not know how deep it goes — that is a problem plain SQL simply cannot express.
Generating a date series, so that days with no data still appear in a report:
WITH RECURSIVE dates AS (
SELECT DATE '2026-01-01' AS d
UNION ALL
SELECT d + 1 FROM dates WHERE d < DATE '2026-01-31'
)
SELECT dates.d, COALESCE(SUM(o.total), 0) AS revenue
FROM dates LEFT JOIN orders o ON o.order_date = dates.d
GROUP BY dates.d ORDER BY dates.d;Splitting a data-cleaning pipeline into readable stages: one CTE to deduplicate, one to normalise values, one to join reference data, and a final SELECT that reads like a summary of the whole thing.
Reusing an expensive filter that would otherwise be written twice in a UNION or a self-join.
Writable CTEs, in PostgreSQL, where a CTE can contain INSERT, UPDATE or DELETE with RETURNING — the standard way to move rows between tables in a single statement:
WITH moved AS (
DELETE FROM active_orders WHERE completed_at IS NOT NULL RETURNING *
)
INSERT INTO archived_orders SELECT * FROM moved;Are CTEs slower than subqueries? Not on current engines, where they are inlined. On PostgreSQL 11 and earlier, they could be either faster or slower because they were always materialised.
Can I use a CTE in an INSERT or UPDATE? Yes — WITH ... UPDATE ... and WITH ... INSERT ... are both valid, and useful for computing the rows to change in a readable way.
How many CTEs can I chain? No practical limit. Readability is the constraint; past six or seven stages, consider a view or a materialised table.
Can a CTE reference a later CTE? No — references only go backwards. Recursive CTEs reference themselves, which is the one exception.
Do all databases support them? Yes in current versions: PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.8.3+. Recursion is supported by all of those too, with minor syntax differences (Oracle and SQL Server omit the RECURSIVE keyword).
When should I use a temporary table instead? When the intermediate result is large, used by several separate statements, or worth indexing. A CTE cannot be indexed and does not outlive its statement.
WITH name AS (...) names an intermediate result and makes multi-step queries readable.WITH RECURSIVE walks hierarchies and generates sequences — an anchor, UNION ALL, and a step that references the CTE itself.About to run: Open the recursive example and press Step. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Quick Context”?
A Common Table Expression is a named temporary result set defined with WITH , available only to the statement that follows it. Think of it as a variable for a query: compute something once, give it a name, then use that name.
What does this module say about “Why Not Just Nest Subqueries”?
You can — the nested example in this lab returns exactly the same rows. But compare how they read. A nested query is evaluated inside out , so you must find the innermost parenthesis and work outward, holding each layer in your head. A chain of CTEs reads top to bottom , like a recipe.
What does this module say about “Recursive CTEs: The Real Superpower”?
This is the thing subqueries genuinely cannot do. A recursive CTE has two halves joined by UNION ALL :
The WITH clause names a query so you can build on it. Watch a nested subquery unfold into a readable pipeline of steps — then run a recursive CTE one iteration at a time.