Traversing Hierarchical Data with Recursive CTEs

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: Start with top-level employees (no manager)
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL

    UNION ALL

    -- Recursive member: Find employees whose manager is in the previous result set
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM
        employees e
    INNER JOIN
        employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    level
FROM
    employee_hierarchy
ORDER BY
    level, employee_name;

How it works: This snippet demonstrates how to use a Recursive Common Table Expression (CTE) to traverse and query hierarchical data, such as an organizational chart where employees have managers. The `employee_hierarchy` CTE consists of two parts: an "anchor member" that selects the starting rows (top-level employees with no manager) and a "recursive member" that repeatedly joins back to the CTE itself to find subordinates at deeper levels. The `UNION ALL` combines these results, and the `level` column tracks the depth in the hierarchy. This is incredibly useful for reporting on nested structures and building features like threaded comments or file system representations.