Skip to content

Subqueries & CTEs · Hard

How would you list the full reporting chain of an organisation with a recursive CTE?

Short answer

A recursive CTE has an anchor member, such as the employee with no manager, unioned with a recursive member that joins the CTE back to the table to fetch the next level down. It repeats until no new rows appear. Track a depth counter and a path string to show each person's level and chain of command.

Detailed answer

WITH RECURSIVE org AS (
  -- Anchor: the top of the tree
  SELECT employee_id, full_name, job_title, manager_id,
         0 AS depth,
         full_name AS reporting_path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive step: direct reports of anyone already in org
  SELECT e.employee_id, e.full_name, e.job_title, e.manager_id,
         org.depth + 1,
         org.reporting_path || ' > ' || e.full_name
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, full_name, job_title, reporting_path
FROM org
ORDER BY reporting_path;

How it runs: the anchor returns the CEO; each iteration adds the next layer of reports; the loop stops when an iteration adds nothing.

Pitfalls to mention:

  • If the data contains a cycle (A manages B, B manages A), recursion never ends. Guard with a depth limit such as WHERE org.depth < 20 in the recursive member.
  • Use UNION ALL, not UNION, unless you deliberately need de-duplication — it is cheaper.

Other uses: category trees, bill-of-materials explosions, and generating a calendar of dates to LEFT JOIN facts onto.