# Recursive CTEs — Org Charts, Number Series, Missing Dates and Paths in SQL

Source: https://skills101.in/learn/dbms-sql/sql-recursive-cte — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-18.

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:

```sql
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;
```

```text
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](https://skills101.in/learn-img/dbms-sql/recursive-steps.svg)

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.

```sql
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;
```

```text
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

```sql
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](https://skills101.in/learn-img/dbms-sql/org-tree.svg)

**Everyone with their level**, starting from the top (the row with no manager):

```sql
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;
```

```text
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:

```sql
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;
```

```text
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:

```sql
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;
```

```text
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:

```sql
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;
```

```text
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:

```sql
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;
```

```text
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 quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-recursive-cte