Recursive CTE

A recursive CTE repeatedly executes until no new rows are produced.

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.

Recursive CTE — source table 1

Recursive CTE — source table 2

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

Recursive CTE — result 1

SQL · Pattern 9 — Common Table Expressions (CTEs)