SQL

Query Hierarchical Data with Recursive CTEs

Discover how to navigate and retrieve all descendants or ancestors in hierarchical datasets using SQL's powerful recursive Common Table Expressions (CTEs), essential for organizational charts or threaded comments.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: Select the top-level employee(s) (e.g., CEO)
    SELECT
        employee_id,
        manager_id,
        employee_name,
        1 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL -- Or a specific top-level ID

    UNION ALL

    -- Recursive member: Join to find direct reports
    SELECT
        e.employee_id,
        e.manager_id,
        e.employee_name,
        eh.level + 1
    FROM
        employees e
    JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    level
FROM
    EmployeeHierarchy
ORDER BY
    level, employee_id;

How it works: This snippet demonstrates how to use a recursive Common Table Expression (CTE) to query hierarchical data, such as an employee reporting structure. The 'anchor member' defines the starting point (e.g., employees with no manager), and the 'recursive member' repeatedly joins back to the CTE to find direct reports, incrementing the `level` until all descendants are found. This is invaluable for navigating tree-like data structures.

Query Hierarchical Data with Recursive CTEs

Need help integrating this into your project?