SQL Recursive CTEs
A recursive CTE (recursive common table expression) is a WITH RECURSIVE query that refers to its own result set to build up rows step by step, one batch at a time, until no new rows are produced. It is the standard SQL tool for querying data that has an unknown or variable number of levels — organizational charts, category trees, bill-of-materials explosions, file-system paths, or graphs of any kind — situations a fixed number of JOINs cannot handle because you don’t know in advance how deep the hierarchy goes. Recursive CTEs are part of the SQL standard and are supported by SQLite, PostgreSQL, SQL Server, and MySQL (8.0+), all using the WITH RECURSIVE syntax (SQL Server uses plain WITH).
Overview: How Recursive CTEs Work
A recursive CTE has two parts joined by UNION or UNION ALL: an anchor member (the non-recursive starting query, run exactly once) and a recursive member (a query that references the CTE’s own name, run repeatedly). Conceptually the database engine does the following:
- Run the anchor member. Its rows become the first batch of the CTE’s result and are placed in a “working table.”
- Run the recursive member, but only against the rows added in the previous iteration (not the entire accumulated result). This is important: a recursive CTE is not true row-by-row recursion, it is an iterative loop.
- Append the new rows produced to the final result and to the working table, then repeat step 2 using only the newest batch.
- Stop when an iteration produces zero new rows — this happens when the recursive member’s
WHEREclause (or join condition) excludes every candidate row.
Because it is iterative rather than a database-level function call, there is no stack overflow risk, but there is a real risk of an infinite loop if the recursive member never runs out of new rows to produce (see Common Mistakes below). SQLite implements this using a queue: each newly produced row is enqueued, dequeued one at a time, fed through the recursive member, and any results are enqueued again. This queue-based approach is why recursive CTEs typically return rows in a roughly breadth-first order (level by level) unless you add an explicit ORDER BY to the outer query.
Syntax
WITH RECURSIVE cte_name (column1, column2, ...) AS (
-- anchor member (runs once)
SELECT ...
UNION ALL
-- recursive member (runs repeatedly, references cte_name)
SELECT ...
FROM cte_name
WHERE <termination condition>
)
SELECT * FROM cte_name;
| Part | Purpose |
|---|---|
WITH RECURSIVE |
Introduces the CTE; the RECURSIVE keyword is required in SQLite/PostgreSQL/MySQL whenever any CTE in the statement is recursive (SQL Server infers it automatically from plain WITH). |
cte_name (columns) |
The CTE’s name and, optionally, explicit column names — useful for clarity and required if the anchor and recursive members use expressions rather than plain columns. |
| Anchor member | A plain, non-recursive SELECT that produces the starting rows. It must not reference cte_name. |
UNION / UNION ALL |
Combines the anchor and recursive members. UNION ALL keeps duplicates and is faster; UNION removes duplicate rows on every iteration, which can also be used to stop cycles in graph data. |
| Recursive member | A SELECT that references cte_name (exactly once in SQLite) and includes a condition that eventually excludes all rows, ending the loop. |
Outer SELECT |
Queries the finished CTE like a normal table or view, and may add WHERE, ORDER BY, or LIMIT. |
Examples
Example 1: Generating a number sequence
The simplest possible recursive CTE needs no tables at all — it can generate a sequence of numbers, which is useful for building calendars, pagination helpers, or test data.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;
Result:
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
The anchor member SELECT 1 seeds the CTE with a single row, n = 1. Each iteration of the recursive member takes the most recent row and produces n + 1, as long as n < 10. Once n reaches 10, the condition n < 10 is false for that row, the recursive member returns no new rows, and the loop stops — leaving exactly ten rows in the final result.
Example 2: Walking an organizational chart
Recursive CTEs are most useful for self-referencing, parent/child data, such as an employee table where each row stores its manager’s id.
CREATE TABLE staff (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER
);
INSERT INTO staff (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);
WITH RECURSIVE org_chart AS (
SELECT id, name, manager_id, 0 AS level
FROM staff
WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name, s.manager_id, oc.level + 1
FROM staff s
JOIN org_chart oc ON s.manager_id = oc.id
)
SELECT id, name, manager_id, level
FROM org_chart
ORDER BY level, id;
Result:
| id | name | manager_id | level |
|---|---|---|---|
| 1 | Alice | NULL | 0 |
| 2 | Bob | 1 | 1 |
| 3 | Carol | 1 | 1 |
| 4 | Dave | 2 | 2 |
| 5 | Eve | 2 | 2 |
| 6 | Frank | 3 | 2 |
The anchor member finds the row(s) with no manager (manager_id IS NULL) — Alice, the CEO — and assigns her level 0. Each recursive iteration joins staff back to the rows produced in the previous step, finding direct reports and incrementing level by one. The loop naturally terminates once an iteration finds no employees reporting to the previous batch (nobody reports to Dave, Eve, or Frank).
Example 3: Building a readable path through a category tree
Recursive CTEs can also accumulate a value across levels — for example, concatenating names into a breadcrumb-style path.
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
parent_id INTEGER
);
INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Accessories', 1);
WITH RECURSIVE category_path AS (
SELECT id, name, parent_id, name AS path, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, cp.path || ' > ' || c.name, cp.depth + 1
FROM categories c
JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, depth, path
FROM category_path
ORDER BY path;
Result:
| id | name | depth | path |
|---|---|---|---|
| 1 | Electronics | 0 | Electronics |
| 5 | Accessories | 1 | Electronics > Accessories |
| 2 | Computers | 1 | Electronics > Computers |
| 4 | Desktops | 2 | Electronics > Computers > Desktops |
| 3 | Laptops | 2 | Electronics > Computers > Laptops |
The path column is carried forward and extended on every iteration using the || string-concatenation operator (SQLite/PostgreSQL syntax; use CONCAT() in MySQL or SQL Server). Each row’s path is built from its parent’s already-computed path plus its own name, which is exactly why the recursive member must reference the CTE — it needs the parent’s path from the previous iteration.
How It Works Step by Step (Under the Hood)
For the org chart example, the engine effectively runs this sequence:
- Iteration 0 (anchor):
SELECT ... FROM staff WHERE manager_id IS NULL→ produces Alice, level 0. This row goes into the working table and the final result. - Iteration 1: the recursive member runs with the working table limited to Alice’s row → joins to find Bob and Carol (their
manager_idis 1) → produces two rows at level 1. These become the new working table. - Iteration 2: the recursive member runs against Bob and Carol’s rows → finds Dave and Eve (report to Bob) and Frank (reports to Carol) → three rows at level 2.
- Iteration 3: the recursive member runs against Dave, Eve, and Frank’s rows → no one reports to them → zero rows produced → the loop stops.
- Finally, the outer
SELECT ... ORDER BY level, idsorts the complete accumulated result for display. Sorting always happens after the recursion is finished, never in the middle of it.
This mirrors the standard logical query-processing order for the outer query — the CTE is fully materialized first, then treated like any other table for WHERE/GROUP BY/HAVING/ORDER BY/LIMIT in the final SELECT.
Common Mistakes
1. Anchor and recursive members returning a different number of columns
Every branch of the UNION/UNION ALL must return the same number of columns with compatible types, exactly like any other UNION.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1, 'extra' FROM counter WHERE n < 5
)
SELECT n FROM counter;
This fails immediately: the anchor returns 1 column but the recursive member returns 2, so SQLite raises an error rather than running the query. The fix is to make both branches return the same column list:
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;
2. Using an aggregate function on the CTE inside its own recursive member
WITH RECURSIVE bad_totals(n) AS (
SELECT 1
UNION ALL
SELECT MAX(n) + 1 FROM bad_totals WHERE n < 5
)
SELECT n FROM bad_totals;
It’s tempting to reach for MAX(), SUM(), or similar when you want “the running total so far,” but SQLite (and the SQL standard) does not allow an aggregate function applied to the recursive table inside its own recursive member — each iteration only ever sees a single new row from the previous step, so an aggregate over the CTE has nothing meaningful to aggregate and the engine rejects the query outright. If you need a running total, carry it forward as a plain column instead, the same way path and level were carried forward in Examples 2 and 3.
3. Forgetting a termination condition (infinite recursion)
If the recursive member’s WHERE clause never becomes false — for example, omitting WHERE n < 10 entirely from the counter example — the loop never runs out of new rows to produce. Depending on the engine and query, this either runs until it exhausts memory/time or (for cyclic graph data, such as an employee accidentally listed as their own manager’s manager) loops forever revisiting the same nodes. Always give the recursive member an explicit stopping condition, and treat an outer LIMIT as a safety net, not a substitute:
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT n FROM seq
LIMIT 5;
Result: just 5 rows (n = 1 through 5) — SQLite is smart enough to stop evaluating the recursive member as soon as the outer LIMIT is satisfied, even though the WHERE n < 1000000 condition alone would have allowed a million iterations.
Best Practices
- Always include a condition in the recursive member (a
WHEREclause or a join that shrinks the candidate set) that is guaranteed to eventually exclude every row. - Add an outer
LIMITas a safety net while developing a recursive query, especially against real hierarchical data that might contain an unexpected cycle. - Prefer
UNION ALLfor performance when you know the data cannot produce duplicate rows (e.g. a tree with a single root); useUNIONwhen traversing a graph that might contain cycles, since it discards rows already visited. - Name the CTE’s columns explicitly (
cte_name(col1, col2, ...)) when the anchor and recursive members use expressions — it documents the shape of each iteration and catches column-count mistakes early. - Index the foreign key used in the recursive join condition (e.g.
manager_id,parent_id) — the join runs once per level, and an unindexed self-join over a large table gets slow fast. - Carry forward any value you need at the end (running totals, depth counters, path strings) as a plain column in both the anchor and recursive members — don’t try to compute it after the fact with an aggregate over the CTE.
- Test recursive CTEs on a small, known dataset first, and check the row count and order match your expectations before running them against production-scale hierarchies.
Practice Exercises
- Exercise 1: Using the
stafftable from Example 2, write a recursive CTE that starts at Frank (id 6) and walks upward through his chain of managers, ending at the top of the company. Hint: the anchor should select the row whereid = 6, and the recursive member should join each row to its manager instead of its reports. Expected result: Frank, Carol, Alice, in that order. - Exercise 2: Modify the number-sequence query from Example 1 so it generates only the even numbers from 2 to 20 (2, 4, 6, …, 20). Hint: you can either add 2 on each iteration, or add 1 each time and filter the outer query with
WHERE n % 2 = 0. - Exercise 3: Using the
categoriestable from Example 3, write a query that counts how many descendant categories (at any depth) each top-level category (parent_id IS NULL) has. Expected result: Electronics has 4 descendants (Computers, Laptops, Desktops, Accessories).
Summary
- A recursive CTE uses
WITH RECURSIVE cte_name AS (anchor UNION [ALL] recursive)to build up rows iteratively until the recursive member produces nothing new. - It’s the standard way to query hierarchical or graph-shaped data — org charts, category trees, bill-of-materials, ancestor/descendant lookups, or generated sequences.
- Execution is a loop, not true recursion: each pass only processes the rows added in the previous pass, then the results accumulate into the final table.
- The recursive member must have a stopping condition, or the query can loop indefinitely; an outer
LIMITis a useful safety net but not a substitute for a real termination condition. - Anchor and recursive members must return the same number and type of columns, and aggregate functions cannot be applied directly to the CTE inside its own recursive member.
- Use
UNION ALLfor performance on acyclic data (trees); useUNIONto automatically drop already-visited rows when the data might contain cycles.
