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.

employee_hierarchy.sql 
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 ()), 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 ()), 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 ()), 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;
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.