Recursive CTEs: Walking an Org Chart of Unknown Depth

An org chart might be 3 levels deep or 12. You cannot pre-write 12 joins. But you can write one query.

The problem

The employees table has a manager_id pointing back at itself. You need everyone below a given person, at every level.

A self-join walks one level at a time. Three levels means three joins. And you do not know the depth in advance.

The fixed shape of a recursive CTE

WITH RECURSIVE subordinates AS (
  -- 1. Anchor: where to start
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 2. Recursive part: references itself
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level, name;

Three parts, always the same: anchor → UNION ALL → self-referencing member. The engine repeats part two until it produces no new rows.

Common mistakes

1. Using UNION instead of UNION ALL

UNION deduplicates on every iteration — far slower, and it can drop legitimate rows.

2. Infinite loops

If the data contains a cycle — A manages B, B manages A — the query never terminates. Cap the depth:

  WHERE s.level < 20

Or on PostgreSQL, track the visited path in an array and exclude rows already seen.

3. Forgetting the RECURSIVE keyword

PostgreSQL, MySQL 8 and SQLite require WITH RECURSIVE. SQL Server does not — plain WITH is enough.

The second use: generating continuous dates

WITH RECURSIVE days AS (
  SELECT DATE '2026-01-01' AS day
  UNION ALL
  SELECT day + 1 FROM days WHERE day < DATE '2026-01-31'
)
SELECT d.day, COALESCE(SUM(o.total), 0) AS revenue
FROM days d
LEFT JOIN orders o ON o.order_date::date = d.day
GROUP BY d.day ORDER BY d.day;

This is the only way to build a calendar on engines without generate_series. It permanently solves “empty months disappearing from the chart”.

The third use: finding missing numbers

LeetCode 1613 — generate a continuous id sequence, then LEFT JOIN to find ids that do not exist. Same technique, different column names.

Practice

• LeetCode 1613 — Find the Missing IDs
• LeetCode 1270 — All People Report to the Given Manager
• LeetCode 1729 — Find Followers Count
• HackerRank — Draw The Triangle 1 and 2
• HackerRank — Print Prime Numbers