Recursive Queries

A recursive CTE refers to itself, repeating until no new rows are produced. It handles data of unknown depth — org charts, category trees, threaded comments — and can generate sequences.

Structure

WITH RECURSIVE name AS (
  -- 1. anchor: the starting rows
  SELECT ...
  UNION ALL
  -- 2. recursive part: new rows built from the previous step
  SELECT ... FROM name JOIN ...
)
SELECT * FROM name;

Generating a sequence

WITH RECURSIVE days(day) AS (
  SELECT DATE('2025-04-01')
  UNION ALL
  SELECT DATE(day, '+1 day') FROM days WHERE day < '2025-04-07'
)
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;

+------------+--------+
|    day     | orders |
+------------+--------+
| 2025-04-01 |      0 |
| 2025-04-02 |      1 |
| 2025-04-03 |      0 |
| 2025-04-04 |      0 |
| 2025-04-05 |      0 |
| 2025-04-06 |      0 |
| 2025-04-07 |      0 |
+------------+--------+

Generating every date and left-joining the data fills in days with zero orders — which a plain GROUP BY would skip. (PostgreSQL has generate_series() for this.)

Walking a hierarchy

Who is in each employee's management chain, and how deep?

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 0 AS depth, name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT depth, name, path
FROM chain
ORDER BY path;

+-------+-------+----------------------+
| depth | name  |         path         |
+-------+-------+----------------------+
|     0 | Sofia | Sofia                |
|     1 | Chen  | Sofia > Chen         |
|     2 | Lucia | Sofia > Chen > Lucia |
|     1 | Kofi  | Sofia > Kofi         |
|     1 | Omar  | Sofia > Omar         |
|     1 | Priya | Sofia > Priya        |
+-------+-------+----------------------+

1. The anchor selects the top of the tree (no manager).
2. Each recursive step finds the people who report to the rows found in the previous step, one level deeper.
3. It stops when a step finds nobody new.

Common mistakes

• Infinite recursion — if the data contains a cycle (A manages B, B manages A) or the stopping condition is missing, the query never ends. Add a depth limit (WHERE c.depth < 20) as a safety net.
• Using UNION instead of UNION ALL without meaning to — UNION removes duplicates at every step, which is slower (though it can also stop some cycles).