Hierarchical Traversal with Recursive CTEs

Snippet

Hierarchical Traversal with Recursive CTEs

Recursive Common Table Expressions (CTEs) allow you to query hierarchical data structures, such as organizational charts or bill-of-materials, by iteratively joining a table with itself until a termination condition is met.

snippet.sql

sql

WITH RECURSIVE employee_path AS (
  SELECT id, manager_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name, ep.depth + 1
  FROM employees e
  INNER JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT * FROM employee_path ORDER BY depth, name;

Breakdown

WITH RECURSIVE employee_path AS (

Defines the start of the recursive query block.

SELECT ... WHERE manager_id IS NULL

The anchor member: selects the root nodes of the hierarchy.

UNION ALL

Combines the anchor member with the recursive member.

INNER JOIN employee_path ep ON e.manager_id = ep.id

The recursive member: joins the table back to the previous result set to find children.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Hierarchical Traversal with Recursive CTEs

Related