SQL

Querying Hierarchical Data with Recursive CTE

Learn to traverse and query hierarchical data structures like organizational charts or category trees in SQL using a powerful recursive Common Table Expression (CTE).

WITH RECURSIVE EmployeeHierarchy AS (
  SELECT
    id,
    name,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL -- Top-level employees (CEOs, etc.)

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id,
    eh.level + 1
  FROM employees e
  INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level, id;

How it works: This snippet demonstrates a recursive Common Table Expression (CTE) named `EmployeeHierarchy`. It starts by selecting top-level employees (those without a manager) as the anchor member. Then, in the recursive member, it joins `employees` with the `EmployeeHierarchy` CTE to find employees whose `manager_id` matches an `id` from the previous level, incrementing the `level` counter. This process continues until no more related rows are found, effectively traversing the entire hierarchy.

Querying Hierarchical Data with Recursive CTE

Need help integrating this into your project?