Recursive CTEs

Most analysts go years without writing one. Then a manager asks for “the full reporting chain under Alice”, or “fill in a zero for every day between these two dates”, and suddenly you need a loop in SQL.

That loop is WITH RECURSIVE — the only SQL feature that lets a query refer to itself. It has two halves joined by UNION ALL: an anchor that produces the starting rows once, and a recursive member that reads from the CTE itself and produces the next batch, over and over, until a pass returns no new rows.

WITH RECURSIVE name AS (
  SELECT ...                 -- ANCHOR: the seed rows, run once
  UNION ALL
  SELECT ... FROM name       -- RECURSIVE: reads the CTE, runs repeatedly
  WHERE  ...                 -- the stopping condition
)
SELECT * FROM name;

Watching it iterate

The clearest way in is the smallest example: counting. Each pass takes the previous batch, adds one, and stops when the condition fails. Counting up to 3:

WITH RECURSIVE nums(n) AS (
  SELECT 1                  -- anchor
  UNION ALL
  SELECT n + 1 FROM nums    -- recursive: previous n, plus one
  WHERE  n < 10             -- stop at 10
)
SELECT n FROM nums;

This produces the numbers 1 through 10. The anchor seeds {1}; each pass adds one to the previous batch; and when n reaches 10 the WHERE yields nothing, so it stops. Forget the stopping condition and it would loop forever — most engines cap recursion at about 1000 to save you.

The killer practical use: a date series

Your orders table has gaps — some days have orders, some don’t — and GROUP BY order_date makes the empty days simply vanish, so a chart line jumps over them. The fix is to generate every date in the range, then LEFT JOIN the orders to it:

WITH RECURSIVE days(d) AS (
  SELECT DATE('2024-01-01')
  UNION ALL
  SELECT DATE(d, '+1 day') FROM days WHERE d < DATE('2024-01-05')
)
SELECT days.d AS day, COUNT(o.id) AS orders
FROM   days
LEFT JOIN orders o ON o.order_date = days.d
GROUP BY days.d
ORDER BY days.d;

Every day appears — including the two with no orders, now honestly showing 0 instead of disappearing. This is exactly the SQL behind a “daily active users” line with no fake gaps.

Walking a hierarchy

The other big use is hierarchies. Suppose an employees table where each row has a manager_id pointing at another employee. To get everyone under Alice, you walk the tree — the anchor is Alice, and each pass finds the people whose manager is already in the result:

WITH RECURSIVE org AS (
  SELECT id, name, 0 AS depth FROM employees WHERE name = 'Alice'   -- anchor
  UNION ALL
  SELECT e.id, e.name, o.depth + 1                                  -- next layer down
  FROM   employees e
  JOIN   org o ON e.manager_id = o.id
)
SELECT * FROM org;

Each iteration finds the next layer, and the depth column gives you the indentation level for free. The stop is implicit: when a pass finds no more direct reports, it returns nothing. The same shape walks comment threads (parent_comment_id), category trees, and dependency graphs — anything with a parent pointer.

Practice