Query hierarchical data with a recursive CTE

Created: 2023-08-17 14:02

A classic example is an “employees” table where each employee has a manager, and that manager might have their own manager, and so on, forming a hierarchical structure. Recursive CTEs shine in such scenarios.

The following employees table follows a hierarchy of representing the management chain.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    manager_id INT REFERENCES employees(id)
);

Some sample data:

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'Engineer1', 2),
(4, 'Engineer2', 2),
(5, 'CFO', 1),
(6, 'Accountant1', 5);

The hierarchy looks like this:

CEO
|-- CTO
|   |-- Engineer1
|   |-- Engineer2
|-- CFO
    |-- Accountant1

To retrieve the entire hierarchy beneath the CTO (i.e., the CTO and all their reports, and all their reports’ reports, etc.) use a recursive CTE:

WITH RECURSIVE hierarchy AS (
    -- Base case:
    -- select the root of the hierarchy (in this case, the CTO)
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'CTO'
 
    UNION ALL
 
    -- Recursive case:
    -- select the direct reports of anyone currently in the hierarchy
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

The result would be:

id | name      | manager_id
---|-----------|-----------
2  | CTO       | 1
3  | Engineer1 | 2
4  | Engineer2 | 2

The CTE starts with the CTO (the base case). And then, it repeatedly adds to the result set any employees who report to someone already in the result set (the recursive case). It keeps doing this until there are no more employees to add, which, for this data, gives us the CTO and all of their reports.