Recursive CTEs for Hierarchical Tree Traversal

Snippet

Recursive CTEs for Hierarchical Tree Traversal

Recursive Common Table Expressions (CTEs) allow SQL to query hierarchical data structures like organizational charts or file systems. The 'anchor member' defines the starting point (e.g., the CEO), while the 'recursive member' joins the CTE back to the base table to find children, incrementing the depth until no more relations exist.

snippet.sql
sql
WITH RECURSIVE organizational_chart 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, oc.depth + 1
  FROM employees e
  JOIN organizational_chart oc ON e.manager_id = oc.id
)
SELECT * FROM organizational_chart ORDER BY depth, name;

Breakdown

WITH RECURSIVE organizational_chart AS (

Initializes the recursive CTE structure.

SELECT id, manager_id, name, 1 AS depth FROM employees WHERE manager_id IS NULL

The anchor member: selects the root node where there is no manager.

UNION ALL

Combines the anchor results with subsequent recursive iterations.

JOIN organizational_chart oc ON e.manager_id = oc.id

The recursive step: joins the table with the previous result set to find the next level.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Recursive CTEs for Hierarchical Tree Traversal

Related