Recursive CTEs

Hierarchy Walk

Follow Manager Links

A recursive CTE can traverse parent-child rows, carrying a depth value as it walks the tree.

Program

Play the script to choose a manager and list everyone reachable below that manager.

root_id
employee_hierarchy.sql
Replay: real traced execution (multi-file project)
CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
WITH RECURSIVE params(root_id) AS (VALUES (1)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;

CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
WITH RECURSIVE params(root_id) AS (VALUES (2)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;

CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
WITH RECURSIVE params(root_id) AS (VALUES (3)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;

  1. tables ← 1 row

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
    values this step1 rowtables

  2. employees ← 6 rows

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (1)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step6 rowsemployees

  3. result ← 6 rows

    2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (1)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step6 rowsresult

  1. tables ← 1 row

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
    values this step1 rowtables

  2. employees ← 6 rows

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (2)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step6 rowsemployees

  3. result ← 3 rows

    2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (2)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);
    values this step1 rowtables

  2. employees ← 6 rows

    1CREATE TABLE employees (id INTEGER, name TEXT, manager_id INTEGER);2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (3)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step6 rowsemployees

  3. result ← 2 rows

    2INSERT INTO employees VALUES (1, 'Ada', NULL), (2, 'Lin', 1), (3, 'Grace', 1), (4, 'Ken', 2), (5, 'Mira', 2), (6, 'Nia', 3);3WITH RECURSIVE params(root_id) AS (VALUES (3)), org(id, name, depth) AS (SELECT id, name, 0 FROM employees WHERE id = (SELECT root_id FROM params) UNION ALL SELECT employees.id, employees.name, org.depth + 1 FROM employees JOIN org ON employees.manager_id = org.id) SELECT name, depth FROM org ORDER BY depth, name;
    values this step2 rowsresult
parent-child rows `manager_id` points from each employee to a parent row.
recursive join `JOIN org ON employees.manager_id = org.id` finds the next level.
depth `org.depth + 1` records how far each row is from the selected root.