SQL
Querying Hierarchical Data with Recursive SQL CTEs
Master recursive Common Table Expressions (CTEs) in SQL to efficiently query and traverse hierarchical data structures like organizational charts or threaded comments.
WITH RECURSIVE
EmployeeHierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
1 as level
FROM
employees
WHERE
manager_id IS NULL -- Anchor member: Top-level employees (e.g., CEO)
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1
FROM
employees e
JOIN
EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- Recursive member
)
SELECT
employee_id,
employee_name,
level,
manager_id
FROM
EmployeeHierarchy
ORDER BY
level, employee_id;
How it works: Recursive CTEs are powerful for navigating hierarchical data, like an organizational chart where employees have managers. This snippet defines `EmployeeHierarchy` with two parts: an 'anchor member' that selects the top-level employees (those without a manager) and a 'recursive member' that iteratively joins `employees` with the `EmployeeHierarchy` itself to find direct reports and increment their level. The UNION ALL combines these results until no more new employees are found, effectively building out the entire hierarchy.