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.
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.
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.