sqlstreak

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.

Practise it now