Recursive CTE

A recursive CTE (WITH RECURSIVE) is a Common Table Expression that references itself, letting you walk hierarchical or graph-shaped data — org charts, category trees, bill-of-materials — that a plain query can't traverse.

It has two parts unioned together: an anchor member (the starting rows, e.g. top-level managers with no boss) and a recursive member that joins back to the CTE itself, adding one more level each iteration until no new rows are produced. Oracle's older, Oracle-specific alternative for tree traversal is CONNECT BY.

Recursive CTEs are standard SQL, so the same pattern works (with minor syntax differences) across PostgreSQL, SQL Server, MySQL 8+, and Oracle.

WITH RECURSIVE org_chart AS (
  SELECT employee_id, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, oc.lvl + 1
  FROM employees e JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;