Skills101

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:

  1. Run the anchor. Its rows are the first result, and also the "working set".
  2. 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.
  3. 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

Recursive CTE iterations: the anchor gives 1, then each round adds n plus 1 from the previous round's row, until n less than 5 fails and the round returns no rows

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);

Org chart tree: Asha at level 1 manages Ravi and Neha; Ravi manages Kabir and Om; Neha manages Meera; Kabir manages Arjun at level 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:

ProtectionHow
Depth limitcarry a level column and add WHERE level < 20 in the recursive part
Visited checkcarry the path and skip a row whose id already appears in it
UNION instead of UNION ALLdrops 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" join e.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 WITH without RECURSIVE in 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.

Practice what you learned

📝 Quiz: Recursive CTEs5 questions · instant answers & explanations · +10 XP · counts towards your free certificateTake the quiz →