Skip to main content

cte_recursive

🔄 Simplify Hierarchical Queries with Recursive CTEs​

Recursive CTEs let you traverse trees or graphs elegantly without multiple self-joins. They work by defining an anchor member and a recursive member. Use them for org charts, bill-of-materials explosions, or hierarchical permissions.

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