AdvancedDBMS & SQL · Lesson 17 of 17 · Updated ·
Recursive CTEs — Org Charts, Number Series, Missing Dates and Paths in SQL
A normal query cannot answer "list everyone who reports to Asha — directly or through other managers". A self join goes one level down; two self joins go two levels. But the company could be 3 levels deep or 12. A recursive CTE keeps joining until there is nothing left to find.
Recursive CTEs work in PostgreSQL, MySQL 8+, SQLite, Oracle and SQL Server (SQL Server writes just WITH, without the word RECURSIVE). They are asked in data and backend interviews, usually as "print the employee hierarchy" or "generate a date range".
1. The shape of a recursive CTE
WITH RECURSIVE name(columns) AS (
anchor query -- runs once: the starting rows
UNION ALL
recursive query -- refers to name: makes new rows from the previous ones
)
SELECT ... FROM name;
How the database runs it:
- Run the anchor. Its rows are the first result, and also the "working set".
- Run the recursive part using only the working set from the last step. The new rows are added to the result and become the next working set.
- Repeat step 2 until it returns no rows. That is how the loop stops.
The simplest example counts from 1 to 5:
WITH RECURSIVE nums(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;
n
1
2
3
4
5
The WHERE n < 5 is the stopping condition. Without it the query would never end (most databases stop it with an error after a limit, but do not rely on that).
2. Filling missing dates
A shop records sales only on days it sold something. A daily report must still show the empty days as 0, otherwise a chart skips them.
CREATE TABLE sales (day DATE, amount INT);
INSERT INTO sales VALUES ('2026-01-01', 500), ('2026-01-02', 300), ('2026-01-04', 700), ('2026-01-07', 200);
WITH RECURSIVE days(d) AS (
SELECT DATE('2026-01-01')
UNION ALL
SELECT DATE(d, '+1 day') FROM days WHERE d < '2026-01-07'
)
SELECT d AS day, COALESCE(SUM(s.amount), 0) AS total
FROM days LEFT JOIN sales s ON s.day = d
GROUP BY d ORDER BY d;
day | total
2026-01-01 | 500
2026-01-02 | 300
2026-01-03 | 0
2026-01-04 | 700
2026-01-05 | 0
2026-01-06 | 0
2026-01-07 | 200
The CTE builds a calendar of every day; the LEFT JOIN keeps days with no sales, and COALESCE turns their NULL into 0. Date functions differ between databases (DATE(d, '+1 day') in SQLite, d + INTERVAL 1 DAY in MySQL, d + 1 in PostgreSQL), but the pattern is the same.
3. The org chart: everyone under a manager
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), manager_id INT);
INSERT INTO employees VALUES (1, 'Asha', NULL), (2, 'Ravi', 1), (3, 'Neha', 1), (4, 'Kabir', 2), (5, 'Om', 2), (6, 'Meera', 3), (7, 'Arjun', 4);
Everyone with their level, starting from the top (the row with no manager):
WITH RECURSIVE chain(emp_id, name, level) AS (
SELECT emp_id, name, 1 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, c.level + 1
FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT name, level FROM chain ORDER BY level, name;
name | level
Asha | 1
Neha | 2
Ravi | 2
Kabir | 3
Meera | 3
Om | 3
Arjun | 4
Read the recursive part as: "find employees whose manager is someone I found in the last round". Round 1 finds Asha, round 2 her direct reports, round 3 theirs, and round 5 finds nobody, so it stops.
Only the people under Ravi — change the anchor:
WITH RECURSIVE team(emp_id, name) AS (
SELECT emp_id, name FROM employees WHERE name = 'Ravi'
UNION ALL
SELECT e.emp_id, e.name FROM employees e JOIN team t ON e.manager_id = t.emp_id
)
SELECT name FROM team WHERE name <> 'Ravi' ORDER BY name;
name
Arjun
Kabir
Om
Arjun is included even though he reports to Kabir, not to Ravi directly — that is exactly what a single self join cannot do.
4. Building a path
Carry a growing string through the recursion to show how each person is reached:
WITH RECURSIVE chain(emp_id, name, path) AS (
SELECT emp_id, name, name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, c.path || ' > ' || e.name
FROM employees e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT path FROM chain ORDER BY path;
path
Asha
Asha > Neha
Asha > Neha > Meera
Asha > Ravi
Asha > Ravi > Kabir
Asha > Ravi > Kabir > Arjun
Asha > Ravi > Om
Sorting by the path also prints the tree in a natural top-down order. (|| joins strings in SQLite, PostgreSQL and Oracle; MySQL uses CONCAT.)
Walking up instead of down — Arjun's chain of managers. The anchor is Arjun; each round finds the manager of the previous row:
WITH RECURSIVE up(emp_id, name, manager_id) AS (
SELECT emp_id, name, manager_id FROM employees WHERE name = 'Arjun'
UNION ALL
SELECT e.emp_id, e.name, e.manager_id FROM employees e JOIN up u ON e.emp_id = u.manager_id
)
SELECT name FROM up;
name
Arjun
Kabir
Ravi
Asha
5. Cycles and safety
Real data can contain a loop by mistake — A manages B and B manages A. The recursion would then go round forever. Common protections:
| Protection | How |
|---|---|
| Depth limit | carry a level column and add WHERE level < 20 in the recursive part |
| Visited check | carry the path and skip a row whose id already appears in it |
UNION instead of UNION ALL | drops rows already produced, which stops a loop over identical rows (it also makes the query slower) |
A depth-limited version of the chain query only needs one extra condition:
WITH RECURSIVE chain(emp_id, level) AS (
SELECT emp_id, 1 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, c.level + 1 FROM employees e JOIN chain c ON e.manager_id = c.emp_id
WHERE c.level < 2
)
SELECT COUNT(*) AS people_in_top_two_levels FROM chain;
people_in_top_two_levels
3
Asha plus her two direct reports = 3.
6. When to use a recursive CTE
- Hierarchies stored as "parent id" columns: employees, categories and sub-categories, folders, comments and replies.
- Graph-like data: course prerequisites, routes between stations (with a depth limit).
- Generating rows: number series, calendars, time slots.
For very deep or very large trees, some teams store extra columns (a full path, or nested-set numbers) so they can read a subtree without recursion. For most college and interview problems, a recursive CTE is the clean answer.
Common mistakes
- Forgetting the stopping condition in a series, so the query never ends.
- Putting the filter in the outer query instead of the recursive part. The outer filter runs after the recursion, so it does not stop it.
- Joining the wrong way: for "everyone under X" join
e.manager_id = c.emp_id; for "everyone above X" joine.emp_id = c.manager_id. - Mismatched columns: the anchor and the recursive part must return the same number of columns in the same order.
- Writing
WITHwithoutRECURSIVEin PostgreSQL, MySQL or SQLite.
Quick recap
WITH RECURSIVE name AS (anchor UNION ALL recursive-part).- The anchor runs once; the recursive part runs on the last round's rows until it returns nothing.
- Uses: number and date series, hierarchies with levels, paths, managers above someone.
- Protect against cycles with a depth limit or a visited check.