Recursive Hierarchy Traversal

Snippet

Recursive Hierarchy Traversal

Recursive Common Table Expressions (CTEs) allow for the traversal of self-referencing relationships, such as organizational charts or bill of materials, which are otherwise impossible to query with a fixed number of joins. The anchor member defines the starting point, and the recursive member references the CTE itself to build the result set iteratively.

snippet.sql
sql
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name
  FROM employees
  WHERE name = 'Alice'
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

Breakdown

WITH RECURSIVE subordinates AS (

Starts the recursive CTE definition by naming the temporary result set.

SELECT id, manager_id, name FROM employees WHERE name = 'Alice'

The anchor member: selects the initial row to start the recursion.

UNION ALL

Combines the anchor result with the results of subsequent recursive iterations.

SELECT e.id, e.manager_id, e.name FROM employees e

The recursive member: selects candidates for the next level of the hierarchy.

INNER JOIN subordinates s ON s.id = e.manager_id

The join condition that links the new rows to the already discovered subordinates.

Previous snippet Next snippet

From your library

Recursive Hierarchy Traversal

Related