SQL
Query Hierarchical Data with SQL Recursive CTEs
Explore how to effectively query and traverse hierarchical or tree-like data structures in SQL using Common Table Expressions (CTEs), ideal for organizational charts or threaded comments.
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: Select top-level employees (no manager)
SELECT
id,
name,
manager_id,
0 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive member: Join to find subordinates
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 query uses a 'RECURSIVE' Common Table Expression (CTE) to traverse hierarchical data, such as an employee reporting structure. The anchor member selects the top-level items, and the recursive member repeatedly joins to itself to find children, effectively building the hierarchy level by level, making it perfect for tree-like data.