SQL recursive CTEs
A recursive CTE is a query that feeds its own output back into itself. It's the SQL tool for anything with an unknown number of steps: sequences, calendars, and trees like org charts or category hierarchies.
The two parts
A starting query produces the first rows. A recursive step, joined with UNION ALL, refers to the CTE itself and runs repeatedly on the newest rows until it returns nothing. The WHERE in the step is what eventually stops it.
WITH RECURSIVE nums(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10 ) SELECT n, n * n AS square FROM nums;
A calendar with no gaps
Reports often need every day, even days with no data. Generate the dates, then LEFT JOIN the facts. (PostgreSQL also has generate_series for this.)
WITH RECURSIVE days(day) AS ( SELECT '2026-03-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-03-10' ) SELECT d.day, COUNT(o.id) AS orders FROM days d LEFT JOIN orders o ON o.ordered_on = d.day GROUP BY d.day ORDER BY d.day;
Walking down a tree
Start from one person and repeatedly add everyone whose manager_id matches someone already found. A level column tracks depth.
WITH RECURSIVE org AS ( SELECT id, name, role, 0 AS level FROM staff WHERE manager_id IS NULL UNION ALL SELECT s.id, s.name, s.role, o.level + 1 FROM staff s JOIN org o ON s.manager_id = o.id ) SELECT level, name, role FROM org ORDER BY level, name;
Walking up a tree
Reverse the join to climb from one person to the top: find the row whose id is the current row's manager_id.
WITH RECURSIVE chain AS ( SELECT id, name, manager_id, 0 AS step FROM staff WHERE name = 'Zara Ali' UNION ALL SELECT s.id, s.name, s.manager_id, c.step + 1 FROM staff s JOIN chain c ON s.id = c.manager_id ) SELECT step, name FROM chain ORDER BY step;
Common mistakes
A step with no stopping condition, so the query never ends.
Cycles in the data (A manages B, B manages A). Add a depth limit, or PostgreSQL's CYCLE clause.
Using UNION instead of UNION ALL by accident. It works but de-duplicates every iteration, which is slower.
Questions
Is WITH RECURSIVE supported everywhere?+
PostgreSQL, SQLite, MySQL 8+ and SQL Server (which omits the RECURSIVE keyword) all support recursive CTEs.
Recursive CTE or generate_series?+
In PostgreSQL, generate_series is simpler for number and date ranges. Recursive CTEs work everywhere and also handle trees.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.