Recursive CTE in SQL: Hierarchies, Paths, Series and Cycles
A recursive CTE is a query that refers to itself, letting SQL walk trees and graphs of any depth. Learn the anchor and recursive parts, build an org chart with levels and paths, find all managers above an employee, count team sizes, generate date series, and stop infinite loops on cyclic data, with every query run.
- A recursive CTE has an anchor query (the starting rows) joined by
UNION ALLto a recursive query that references the CTE itself. - It runs level by level until the recursive part returns no new rows.
- Carry a level counter and a path string to get depth and breadcrumbs in one query.
- On cyclic data, add a guard such as a visited path, or the query never ends.
Org charts, category trees, folder structures, bills of materials, comment threads and flight routes all have one thing in common: rows point to other rows in the same table, to any depth. A normal join reaches one level; to go deeper you would need one join per level and you rarely know how many there are. A recursive CTE solves that in standard SQL with a single query.
All queries below ran on SQLite 3.50.4 and the output is copied from the run. The same syntax works in PostgreSQL, MySQL 8+ and Oracle; SQL Server omits the RECURSIVE keyword. If CTEs are new to you, read our SQL CTE guide first.
What is a recursive CTE?
A common table expression (CTE) is a named subquery introduced with WITH. A recursive CTE is one whose definition refers to its own name. The database evaluates it iteratively: it runs the anchor once, feeds those rows into the recursive part, feeds the new rows in again, and keeps going until an iteration produces nothing. The final result is the union of every iteration.
Anchor member and recursive member
WITH RECURSIVE cte_name (columns) AS (
-- anchor member: the starting rows
SELECT columns FROM some_table WHERE start_condition
UNION ALL
-- recursive member: refers to cte_name
SELECT columns FROM some_table JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;
The anchor must not reference the CTE. The recursive member must reference it exactly once, typically in a join, and must eventually stop producing rows. The column list and types of both parts must match.
The simplest example: counting to five
WITH RECURSIVE n(x) AS (
SELECT 1
UNION ALL
SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;
-- 1, 2, 3, 4, 5
The anchor produces 1. Each iteration adds 1 to the previous row, and the WHERE x < 5 condition makes the recursive part return nothing once 5 is reached, which ends the recursion. That stopping condition is the most important line in any recursive CTE.
The sample org chart
CREATE TABLE emp (id INT PRIMARY KEY, name TEXT, manager_id INT);
INSERT INTO emp VALUES
(1,'Asha',NULL), (2,'Ravi',1), (3,'Meera',1), (4,'Kabir',2),
(5,'Lina',2), (6,'Zoya',4), (7,'Dev',3);
Asha leads the company, Ravi and Meera report to Asha, Kabir and Lina to Ravi, Dev to Meera, and Zoya to Kabir: four levels in an adjacency list, where each row stores its parent. Our self join guide shows how far a single join gets you.
Walking down a tree: levels and paths
WITH RECURSIVE chain(id, name, lvl, path) AS (
SELECT id, name, 0, name FROM emp WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, c.lvl + 1, c.path || ' > ' || e.name
FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT lvl, name, path FROM chain ORDER BY path;
-- 0 | Asha | Asha
-- 1 | Meera | Asha > Meera
-- 2 | Dev | Asha > Meera > Dev
-- 1 | Ravi | Asha > Ravi
-- 2 | Kabir | Asha > Ravi > Kabir
-- 3 | Zoya | Asha > Ravi > Kabir > Zoya
-- 2 | Lina | Asha > Ravi > Lina
The anchor picks the root (no manager). Each iteration joins employees whose manager is in the previous level, adds one to the level and appends the name to the path. Sorting by the path string produces a natural depth-first outline, ready to indent in a UI. The || operator concatenates text in SQLite, PostgreSQL and Oracle; use CONCAT in MySQL and + or CONCAT in SQL Server.
Walking up a tree: all managers above someone
WITH RECURSIVE up(id, name, manager_id) AS (
SELECT id, name, manager_id FROM emp WHERE name = 'Zoya'
UNION ALL
SELECT e.id, e.name, e.manager_id FROM emp e JOIN up ON e.id = up.manager_id
)
SELECT name FROM up;
-- Zoya, Kabir, Ravi, Asha
Reverse the join direction and the CTE climbs from a leaf to the root. This is how you build breadcrumbs for a category page, check approval chains or find which department a team belongs to.
Counting everyone below each manager
WITH RECURSIVE sub(root, id) AS (
SELECT id, id FROM emp
UNION ALL
SELECT s.root, e.id FROM emp e JOIN sub s ON e.manager_id = s.id
)
SELECT e.name, COUNT(*) - 1 AS reports
FROM sub s JOIN emp e ON e.id = s.root
GROUP BY e.name HAVING COUNT(*) > 1 ORDER BY reports DESC;
-- Asha | 6
-- Ravi | 3
-- Meera | 1
-- Kabir | 1
Here the anchor starts a walk from every employee, and each walk carries its starting point as root. Counting rows per root, minus the root itself, gives the total number of direct and indirect reports. Ravi has three: Kabir, Lina and Zoya. The GROUP BY and HAVING step turns paths into totals.
Generating a date series
WITH RECURSIVE d(day) AS (
SELECT date('2026-11-27')
UNION ALL
SELECT date(day, '+1 day') FROM d WHERE day < '2026-12-01'
)
SELECT day FROM d;
-- 2026-11-27, 2026-11-28, 2026-11-29, 2026-11-30, 2026-12-01
Recursive CTEs are also the portable way to generate rows from nothing: a calendar for a report, so that days with zero sales still appear after a LEFT JOIN, or a list of numbers for testing. PostgreSQL has generate_series for this; elsewhere a recursive CTE does the job. Date arithmetic syntax differs by database, as our SQL date functions guide shows.
Tip: left join your facts to a generated calendar to fill gaps in time-series charts instead of handling missing days in application code.
Handling cycles in graph data
Trees have no loops, but general graphs often do. We created edges A to B, B to C, C to A and C to D:
-- UNION removes duplicate rows, so the loop dies out
WITH RECURSIVE r(node) AS (
SELECT 'A'
UNION
SELECT e.b FROM edges e JOIN r ON e.a = r.node
)
SELECT node FROM r;
-- A, B, C, D
-- UNION ALL plus a visited path as the guard
WITH RECURSIVE r(node, depth, path) AS (
SELECT 'A', 0, ',A,'
UNION ALL
SELECT e.b, depth + 1, path || e.b || ','
FROM edges e JOIN r ON e.a = r.node
WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, depth, path FROM r;
-- A 0 ,A, | B 1 ,A,B, | C 2 ,A,B,C, | D 3 ,A,B,C,D,
With UNION ALL and no guard, A to B to C to A would repeat forever. Two fixes: plain UNION discards rows already produced, which works when the row is just the node, as in the first query. When you carry extra columns such as depth, every row is new, so you need an explicit visited check: the second query keeps a comma-wrapped path and refuses to step onto a node already in it. PostgreSQL 14+ also offers a built-in CYCLE clause (not run here).
Limits and safety nets
WITH RECURSIVE r(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM r)
SELECT x FROM r LIMIT 1000000;
-- returned 1,000,000 rows in 0.56 s, then stopped
A recursive CTE without a stopping condition runs until something stops it. In SQLite, an outer LIMIT stops the recursion early, which is a handy safety net during development. SQL Server stops at 100 levels by default (OPTION (MAXRECURSION n) changes it), and MySQL has a cte_max_recursion_depth setting of 1000 by default; those two notes come from vendor documentation. In every database, also add a depth limit such as WHERE lvl < 20 in the recursive member to protect against bad data.
Recursive CTEs across databases
| Database | Syntax | Notes |
|---|---|---|
| SQLite 3.8.3+ | WITH RECURSIVE |
Outer LIMIT stops recursion |
| PostgreSQL | WITH RECURSIVE |
SEARCH and CYCLE clauses in 14+ |
| MySQL 8.0+, MariaDB 10.2+ | WITH RECURSIVE |
Depth limit setting |
| SQL Server | WITH (no RECURSIVE) |
MAXRECURSION, default 100 |
| Oracle 11gR2+ | WITH |
Also has CONNECT BY |
Performance tips
- Index the column used in the recursive join, such as
manager_id, or every level scans the table; see our SQL indexes guide. - Filter as early as possible, ideally in the anchor, so fewer rows recurse.
- Carry only the columns you need; long path strings grow with depth.
- For very large, read-heavy trees, consider storing a materialised path or a closure table, trading write complexity for fast reads.
- Prefer
UNION ALLwith explicit guards overUNIONon big data, sinceUNIONmust compare every row (see UNION vs UNION ALL).
Final thoughts
A recursive CTE gives SQL the ability to follow relationships to any depth: an anchor to start, a recursive member to take one step, and a condition that eventually stops. With levels and paths you can render trees, climb to the root, total whole subtrees and generate series, all in one query. Index the join column, guard against cycles and runaway depth, and hierarchical data becomes straightforward to query.
Frequently asked questions
What is a recursive CTE in SQL?
It is a common table expression that refers to itself. An anchor query produces the starting rows, and a recursive query joined with UNION ALL repeatedly adds new rows until it returns none.
How do I stop a recursive CTE from running forever?
Add a condition in the recursive member that eventually returns no rows, such as a level limit, and guard cyclic data with a visited path. Database limits like MAXRECURSION act as a final safety net.
Should I use UNION or UNION ALL in a recursive CTE?
UNION ALL is the usual choice and is faster. UNION removes duplicate rows, which can stop simple cycles, but it fails as a guard once you carry columns like depth that make every row unique.
Does SQL Server support recursive CTEs?
Yes, but it uses WITH without the RECURSIVE keyword and stops at 100 levels by default. Change the limit with the MAXRECURSION query option.
How do I get the level or depth of each row?
Start a counter at 0 in the anchor member and add 1 in the recursive member. The same technique builds a path column by concatenating names at each step.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.