SQL Snippet: Recursive CTE for Hierarchical Data

If you have a database table that references itself (like an employees table where every row has a manager_id), querying the full hierarchy tree is impossible with standard JOINs. You need a Recursive CTE.

The Schema

Imagine a table called employees with columns id, name, and manager_id.

The Snippet

WITH RECURSIVE OrgChart AS (
    -- Base Case: Select the CEO (employee with no manager)
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive Step: Select employees whose manager is already in the OrgChart
    SELECT 
        e.id, 
        e.name, 
        e.manager_id, 
        oc.level + 1 AS level
    FROM employees e
    INNER JOIN OrgChart oc ON e.manager_id = oc.id
)

-- Final Query
SELECT 
    id, 
    REPEAT('  ', level - 1) || name AS indented_name, 
    level 
FROM OrgChart
ORDER BY level;

Why this matters

This single query replaces having to load the entire dataset into application memory (like Python or PHP) to build the tree manually. The database handles the traversal infinitely deep in a fraction of a millisecond.