SQL
Traverse Hierarchical Data with Recursive SQL CTEs
Explore how to query and navigate hierarchical or tree-like data structures, such as organizational charts, threaded comments, or bill of materials, using SQL's powerful recursive Common Table Expressions.
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: Select the top-level employee(s) (e.g., those with no manager)
SELECT
id,
name,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive member: Join employees with their managers from the CTE
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1 AS level
FROM
employees e
JOIN
EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT
id,
name,
manager_id,
level
FROM
EmployeeHierarchy
ORDER BY
level, id;
How it works: This snippet uses a `RECURSIVE WITH` clause to build an `EmployeeHierarchy` CTE, ideal for querying tree-like structures. The "anchor member" selects the root elements (employees without a manager). The "recursive member" then repeatedly joins back to the CTE to find direct reports of the previously selected employees, incrementing the `level`. This continues until no new records are found, effectively traversing the entire hierarchy.