A recursive CTE is a CTE that refers to itself. It is built from two halves: an anchor query that produces the first row or rows, and a recursive query that produces the next one, referencing the previous result as a table. The engine repeats the recursive half until it produces nothing new. That is enough to walk a hierarchy, generate a sequence, and traverse a graph.
What do recursive CTEs traverse?
Two shapes cover almost everything: trees and sequences. A tree has rows that point at other rows in the same table — an organisational hierarchy, a category structure, a station network with hubs. A sequence is a generated series — dates, numbers, identifiers — that does not exist in any table. Both are expressed with the same two-half structure, and both are what the RECURSIVE keyword unlocks.
How are the two halves written?
WITH RECURSIVE tree_name (station_id, name, hub_station_id, depth) AS (
-- anchor: the starting rows, usually the roots
SELECT station_id, name, hub_station_id, 0
FROM stations
WHERE hub_station_id IS NULL
UNION ALL
-- recursive: the next rows, using the previous result
SELECT s.station_id, s.name, s.hub_station_id, t.depth + 1
FROM stations s
JOIN tree_name t ON s.hub_station_id = t.station_id
)
SELECT * FROM tree_name;
Two details matter. The column list in brackets after the CTE name declares the shape of the result, and the recursive half must produce exactly those columns in the same order. And the operator between the halves is normally UNION ALL rather than UNION: UNION ALL keeps duplicates and is much faster, while UNION would silently deduplicate rows and could hide an infinite loop by terminating it early. Each pass of the recursive half adds one row per qualifying row from the previous pass, and the loop stops when the recursive half returns no rows at all.
How do you walk a hierarchy?
The stations in this network have hubs. Two stations are independent roots, and the rest report to Harbour Road, which is itself a station. The hub_station_id column points at the parent row in the same table — the same self-join shape as lesson 25, but now walked level by level:
WITH RECURSIVE chain(station_id, name, hub_station_id, depth) AS (
SELECT station_id, name, hub_station_id, 0
FROM stations
WHERE hub_station_id IS NULL
UNION ALL
SELECT s.station_id, s.name, s.hub_station_id, c.depth + 1
FROM stations s
JOIN chain c ON s.hub_station_id = c.station_id
)
SELECT depth, station_id, name FROM chain ORDER BY depth, station_id;
depth station_id name
----- ---------- ---------------
0 1 Harbour Road
0 6 Kelvingrove
1 2 Clifton Hill
1 3 Cathays Terrace
1 4 Roath Park
1 5 Clyde Walkway
1 7 Weir Industrial
Pass one takes the anchor: the two stations with no hub. Pass two finds every station whose hub is one of those, and adds one to the depth. Pass three finds nothing new and the loop ends. The depth column is not decoration — it is the level of the tree, and it is what lets you ask questions about distance from the root:
WITH RECURSIVE chain(station_id, name, hub_station_id, depth) AS (
SELECT station_id, name, hub_station_id, 0 FROM stations WHERE hub_station_id IS NULL
UNION ALL
SELECT s.station_id, s.name, s.hub_station_id, c.depth + 1
FROM stations s JOIN chain c ON s.hub_station_id = c.station_id
)
SELECT MAX(depth) AS deepest_level, COUNT(*) AS stations_in_chain FROM chain;
deepest_level stations_in_chain
------------- ------------------
1 7
Seven stations, and the network is two levels deep. Without a recursive CTE, that question needs a fixed number of self-joins — one per level — which works only as long as you know the maximum depth in advance. The recursive version has no such limit, and it is the standard answer to "walk this tree, whatever shape it turns out to be".
How do you generate a sequence that is not in any table?
The same machinery, with the recursive half producing the next element of a series instead of the next row of a tree. SQLite's date function does the arithmetic:
WITH RECURSIVE cal(d) AS (
SELECT '2025-03-01'
UNION ALL
SELECT date(d, '+1 day') FROM cal WHERE d < '2025-03-31'
)
SELECT COUNT(*) AS days_generated FROM cal;
days_generated
--------------
31
WITH RECURSIVE cal(d) AS (
SELECT '2025-03-01'
UNION ALL
SELECT date(d, '+1 day') FROM cal WHERE d < '2025-03-31'
)
SELECT d FROM cal ORDER BY d LIMIT 6;
d
----------
2025-03-01
2025-03-02
2025-03-03
2025-03-04
2025-03-05
2025-03-06
Thirty-one days, one per iteration, starting from the anchor date and stopping before the end date. The termination condition lives inside the recursive half's WHERE clause: when the current row is the last one, the next row is not produced and the loop ends. Write the condition carelessly — with an off-by-one — and you either lose a day or run forever, so the boundary is worth checking by eye.
What is a generated calendar actually for?
Its main use is finding what is missing. Join the generated calendar to the readings and every day with no reading stands out:
WITH RECURSIVE cal(d) AS (
SELECT '2025-03-01'
UNION ALL
SELECT date(d, '+1 day') FROM cal WHERE d < '2025-03-31'
)
SELECT cal.d AS missing_day
FROM cal
LEFT JOIN readings r ON r.recorded_on = cal.d
GROUP BY cal.d
HAVING COUNT(r.reading_id) = 0
ORDER BY cal.d
LIMIT 6;
missing_day
-----------
2025-03-04
2025-03-06
2025-03-07
2025-03-11
2025-03-13
2025-03-14
The station network does not report every day, and the gap is invisible in the raw readings table — a missing row and a day that never existed look identical. The generated calendar provides the frame, and the LEFT JOIN plus the HAVING count of zero identifies the absences. This is the standard technique behind "show me every day even when nothing happened", and behind a monthly report that must list all thirty days rather than only the busy ones.
Quick reference
- A recursive CTE has an anchor query and a recursive query, joined by UNION ALL.
- The column list in the CTE name fixes the shape that the recursive half must reproduce.
- UNION ALL is required in practice: UNION would deduplicate and can mask a runaway loop.
- Depth counters turn a tree walk into a hierarchy report.
- The recursive half's WHERE clause is the termination condition — get the boundary right.
Recursion drill: write a recursive CTE that generates every date in April 2025, then join it to the readings to list the days with no data at all — and say in one sentence why the calendar has to be generated rather than read from the readings table.