SQL
Query Hierarchical Data with Recursive CTE
Explore how to use Recursive Common Table Expressions (CTE) to traverse and query tree-like or hierarchical data structures like organizational charts in SQL databases.
WITH RECURSIVE EmployeeHierarchy AS (
-- Anchor member: Start with top-level employees (no manager)
SELECT
employee_id,
first_name,
last_name,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive member: Find employees who report to the previous level
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.manager_id,
eh.level + 1 AS level
FROM
employees e
JOIN
EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
REPEAT(' ', level - 1) || first_name || ' ' || last_name AS employee_tree,
employee_id,
manager_id,
level
FROM
EmployeeHierarchy
ORDER BY
level, employee_id;
How it works: This SQL snippet uses a `RECURSIVE Common Table Expression (CTE)` to query hierarchical data. It starts with an "anchor member" (employees with no manager) and then recursively joins itself to find direct reports in the "recursive member," incrementing the `level` at each step. This allows for displaying organizational structures, nested categories, or other tree-like data. The `REPEAT` function (PostgreSQL example) visually indents the output based on the hierarchy level.