How does a recursive CTE work?

The anchor query produces the starting rows. The recursive member references the CTE and adds rows based on the previous iteration. UNION ALL is common; UNION removes duplicates but adds cost. Execution stops when the recursive member returns no rows.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.id
  WHERE org.depth < 10
)
SELECT id, name, depth
FROM org
ORDER BY depth, name;

Risks and controls

• Cyclic data can loop forever without a depth limit or visited-path check
• Each iteration reads the previous working set, so wide fan-out grows quickly
• Index the join column, here manager_id, to keep each step efficient
• UNION can remove duplicates but requires comparison work on every iteration
• PostgreSQL supports CYCLE detection; other engines need an explicit path column

For a safe cycle check, carry an array or string path and reject rows whose next id is already present. A depth cap is simpler but can truncate valid deep hierarchies, so choose based on the domain.