~/learn/SQL · Subqueries & CTEs
Recursive CTEs
Generate sequences and walk hierarchies.
why this matters
Org charts, category trees and comment threads all have unknown depth. Recursion walks them without knowing how deep they go.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
A recursive CTE has an anchor query, then a query that refers back to the CTE itself, run repeatedly until it returns nothing. The simplest use is generating a series of numbers.
Try it: Stop at 5 and show the cube instead.
The most useful version is a date spine: a complete calendar you LEFT JOIN your data onto, so days with no activity still appear as zeros instead of vanishing.
Note
Always give the recursion a stopping condition. Without one it runs until the engine kills it.
Anchor + step
WITH RECURSIVE t AS ( SELECT … (anchor: where to start) UNION ALL SELECT … FROM t (step: how to go deeper) )
Common gotcha
Forget the stopping condition and the query loops forever. Always bound it with a WHERE on depth or a date range.
key takeaways
- The anchor row starts the sequence.
- The recursive part references the CTE itself.
- Always add a condition that eventually stops it.
check your understanding
What are the two parts of a recursive CTE joined by UNION ALL?
free practice
Build confidence with a question you can solve without Pro.