Recursive CTEs

Why recursion?

Some questions have no fixed depth. "Everyone who reports to Meera" means her direct reports, their reports, their reports' reports, and so on. With self joins you would need one join per level and would have to know the number of levels in advance. A recursive CTE repeats one step until there is nothing left to find.

The two parts

1. The anchor member: an ordinary SELECT that gives the starting rows, for example the number 1 or the employee at the top.
2. The recursive member: a SELECT that reads the CTE itself and produces the next rows from the rows found in the previous round.
3. They are joined with UNION ALL. The database runs the anchor once, then runs the recursive member again and again on the newest rows, until it returns no rows.

WITH RECURSIVE n(x) AS (
  SELECT 1                 -- anchor: start at 1
  UNION ALL
  SELECT x + 1 FROM n      -- recursive member: next number
  WHERE x < 10             -- stop condition
)
SELECT x FROM n;
-- 1, 2, 3, ... 10

PostgreSQL, MySQL 8 and SQLite write WITH RECURSIVE. SQL Server and Oracle use the same idea but write just WITH.

A date series

WITH RECURSIVE d(day) AS (
  SELECT '2025-03-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2025-03-05'
)
SELECT day FROM d;
-- 2025-03-01 ... 2025-03-05 (5 rows)

This solves the "missing days" problem from module 5: generate every date (or month), then LEFT JOIN the sales onto it, so days with no sales show 0 instead of disappearing from a chart.

Walking the org chart

Start with the employee at the top (manager_id IS NULL) at level 1. In each round, find the employees whose manager is someone found in the previous round, at that level + 1.

WITH RECURSIVE chain(id, name, level) AS (
  SELECT id, name, 1
  FROM employees
  WHERE manager_id IS NULL                  -- anchor: Meera
  UNION ALL
  SELECT e.id, e.name, c.level + 1
  FROM employees AS e
  JOIN chain AS c ON e.manager_id = c.id    -- people managed by the previous round
)
SELECT name, level FROM chain ORDER BY level, name;
-- Meera Iyer     | 1
-- Deepak Yadav   | 2  ... Rahul Sharma | 2   (5 people)
-- Anita Desai    | 3  ... Vikram Singh | 3   (5 people)

To get everyone under one person, change the anchor: start from that person's direct reports. You can also walk upwards, from an employee to the top, by joining the other way: ON e.id = c.manager_id.

-- The chain of command above Neha Joshi
WITH RECURSIVE up(id, name, manager_id, step) AS (
  SELECT id, name, manager_id, 0 FROM employees WHERE name = 'Neha Joshi'
  UNION ALL
  SELECT e.id, e.name, e.manager_id, u.step + 1
  FROM employees AS e JOIN up AS u ON e.id = u.manager_id
)
SELECT step, name FROM up ORDER BY step;
-- 0 Neha Joshi, 1 Farhan Ali, 2 Meera Iyer

Recursion limits and cycles

If the data has a cycle (A manages B and B manages A, by mistake) or the stop condition is wrong, the recursion never ends. Engines protect you differently: SQL Server stops at 100 levels by default (change it with OPTION (MAXRECURSION n)); MySQL stops at cte_max_recursion_depth, 1000 by default; PostgreSQL and SQLite have no such default limit, so the query runs until you cancel it. PostgreSQL 14+ has a CYCLE clause to detect loops. Using UNION instead of UNION ALL removes repeated rows, which can stop some loops, but costs time.

Common mistakes

• No stop condition, so the query runs forever. Always put a WHERE in the recursive member (x < 10, day < end date), or rely on the join running out of rows.
• Forgetting RECURSIVE in PostgreSQL or MySQL, which gives an error that the CTE does not exist.
• A different number of columns in the anchor and the recursive member. Both SELECTs must return the same columns, in the same order.
• Joining the wrong way round in a hierarchy (e.id = c.manager_id walks up, e.manager_id = c.id walks down). Decide the direction first.