Recursive CTEs

Recursive CTE Example

WITH emp_hierarchy (employee_id, manager_id, level) AS (
  SELECT employee_id, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, eh.level + 1
  FROM employees e
  JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM emp_hierarchy;

Each recursion adds one level to the hierarchy.