Recursive CTEs

Explanation

A recursive CTE references itself, which lets it walk hierarchies and sequences — org charts, category trees, bill-of-materials, or even just counting numbers. It has two parts joined by UNION ALL:

1. Anchor member — the starting rows (the base case).
2. Recursive member — a query that joins the CTE to itself to produce the next level, repeated until it returns no new rows.

In the employees dataset, each person has a manager_id pointing at another employee. A recursive CTE can expand the full reporting chain under a manager.

Syntax

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.level + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

Interactive Example

Walk the management hierarchy starting from the top. Then from a specific manager, list everyone beneath them with their depth.

Common Mistakes

• Using UNION instead of UNION ALL. UNION deduplicates and can hide rows or hurt performance; recursive CTEs almost always want UNION ALL.
• No terminating condition. If the recursive step can always find a new row you get an infinite loop; ensure the join eventually stops (no cycles).
• Forgetting RECURSIVE. Plain WITH won't allow the CTE to reference itself.

Best Practices

• Always include an anchor that returns a finite starting set.
• Track level (or a visited-path list) to understand and bound the recursion.
• Watch for cycles in real data; add a path column to detect them.

Practice Question

Write a recursive CTE that starts from employee id = 13 (Mia White) and lists everyone in her reporting chain, including their level.

Summary

A recursive CTE (WITH RECURSIVE) combines an anchor and a self-referencing step with UNION ALL to traverse hierarchies and sequences. Include a base case and a level counter, and guard against cycles to keep it from running forever.

FAQ