Recursive CTE

Source data — Pattern 9: Common Table Expressions (CTEs)

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces Common Table Expressions (CTEs). Topic 54 demonstrates a recursive CTE using the employee-manager hierarchy from the employees table.

---

What it does

A recursive CTE repeatedly executes until no new rows are produced. It is commonly used for hierarchical data.

Problem

Display employee-manager hierarchy.

WITH RECURSIVE emp_tree AS (
  SELECT employee_id,first_name,manager_id FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id,e.first_name,e.manager_id FROM employees e JOIN emp_tree t ON
e.manager_id=t.employee_id
)
SELECT * FROM emp_tree;

Result