Recursive CTEs & Tree Queries

Tutorial 6 of 11

Many real-world datasets are hierarchical: org charts, category trees, bill-of-materials. A WITH RECURSIVE CTE lets you walk these trees in pure SQL—no application-level loops, no stored procedures. This tutorial builds an employee org chart and queries it 7 different ways.

1. Schema and seed data

One table with a self-referencing foreign key: each employee points to their manager. The CEO has manager_id = NULL.

CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    title       TEXT NOT NULL,
    manager_id  INT REFERENCES employees(id),
    salary      INT NOT NULL
);

INSERT INTO employees (name, title, manager_id, salary) VALUES
    ('Alice',   'CEO',              NULL, 200000),
    ('Bob',     'VP Engineering',   1,    160000),
    ('Charlie', 'VP Sales',         1,    155000),
    ('Diana',   'Tech Lead',        2,    130000),
    ('Eve',     'Senior Engineer',  2,    120000),
    ('Frank',   'Sales Manager',    3,    110000),
    ('Grace',   'Engineer',         4,    100000),
    ('Hank',    'Engineer',         4,     95000),
    ('Ivy',     'Sales Rep',        6,     80000),
    ('Jack',    'Sales Rep',        6,     75000);

10 employees across 4 levels. Alice manages Bob and Charlie directly; Bob manages Diana and Eve; Diana manages Grace and Hank; and so on.

2. Walk the full org chart

A recursive CTE has two parts: the anchor (the starting rows) and the recursive term (the join that walks one level deeper). Here we start from the CEO and walk down.

WITH RECURSIVE org AS (
    SELECT id, name, title,
           manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.title,
           e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT name, title, depth
FROM org
ORDER BY depth, name;

The depth column starts at 0 for the CEO and increments by 1 at each level. The recursion terminates naturally when no more children are found.

3. Subtree under a specific manager

Change the anchor to start from Bob instead of the CEO. This gives you only the engineering subtree—4 employees instead of 10.

WITH RECURSIVE team AS (
    SELECT id, name, title, 0 AS depth
    FROM employees
    WHERE name = 'Bob'

    UNION ALL

    SELECT e.id, e.name, e.title,
           t.depth + 1
    FROM employees e
    JOIN team t ON e.manager_id = t.id
)
SELECT name, title, depth
FROM team
ORDER BY depth, name;

4. Walk upward to the root

Reverse the direction: start from a leaf node and walk up to the CEO. This answers “who is Grace’s management chain?”

WITH RECURSIVE chain AS (
    SELECT id, name, title,
           manager_id, 0 AS depth
    FROM employees
    WHERE name = 'Grace'

    UNION ALL

    SELECT e.id, e.name, e.title,
           e.manager_id, c.depth + 1
    FROM employees e
    JOIN chain c
      ON e.id = c.manager_id
)
SELECT name, title, depth
FROM chain
ORDER BY depth;

The join is flipped: e.id = c.manager_id walks from child to parent instead of parent to child.

5. Aggregate over a subtree

Combine the recursive CTE with an aggregate to compute the total salary cost of each manager’s entire subtree (including themselves).

WITH RECURSIVE team AS (
    SELECT id, name, id AS root_id
    FROM employees
    WHERE id IN (1, 2, 3)

    UNION ALL

    SELECT e.id, e.name, t.root_id
    FROM employees e
    JOIN team t ON e.manager_id = t.id
)
SELECT m.name AS manager,
       COUNT(*) AS team_size,
       SUM(e.salary) AS total_cost
FROM team t
JOIN employees e ON t.id = e.id
JOIN employees m ON t.root_id = m.id
GROUP BY m.name
ORDER BY total_cost DESC;

Alice’s subtree is the entire company: 10 people, $1,125,000 total. Bob’s engineering org is 5 people at $605,000. Charlie’s sales team is 4 people at $420,000.

6. Limit recursion depth

Add a WHERE clause on the recursive term to stop after 2 levels. This is useful for large trees where you only need nearby nodes.

WITH RECURSIVE org AS (
    SELECT id, name, title, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.title,
           o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
    WHERE o.depth < 2
)
SELECT name, title, depth
FROM org
ORDER BY depth, name;

Only 6 rows instead of 10—the level-3 employees (Grace, Hank, Ivy, Jack) are excluded by the WHERE o.depth < 2 guard.

7. Non-recursive CTEs for readability

Not every CTE needs RECURSIVE. A plain CTE is just a named subquery—useful for breaking complex queries into readable steps.

WITH dept_salary AS (
    SELECT manager_id,
           COUNT(*) AS reports,
           AVG(salary) AS avg_salary
    FROM employees
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
)
SELECT e.name, e.title,
       d.reports,
       d.avg_salary
FROM employees e
JOIN dept_salary d
  ON e.id = d.manager_id
ORDER BY d.reports DESC;

What you learned

In this tutorial you modeled a 10-person org chart and queried it with recursive and non-recursive CTEs:

• WITH RECURSIVE to walk a self-referencing tree top-down
• Anchor + recursive term pattern with UNION ALL
• Depth tracking via an incrementing counter column
• Subtree queries by changing the anchor’s WHERE clause
• Upward traversal by flipping the join direction
• Aggregation over subtrees by carrying a root_id through the recursion
• Depth limiting with a WHERE guard on the recursive term
• Plain CTEs for readable multi-step queries without recursion