SQL / SUBQUERIES AND CTES
Recursive CTEs for hierarchies and sequences
Use WITH RECURSIVE to walk parent-child trees downward or upward, generate numeric sequences, carry depth and path, and stop the recursion safely.
What you will learn
- Build a hierarchy query from an anchor query plus a UNION ALL recursive term
- Carry depth and a path breadcrumb by incrementing them in the recursive term
- Walk down to descendants or up to ancestors by flipping the join key
- Stop runaway recursion with a depth cap, UNION, or a visited-path column
Understanding Recursive CTEs for hierarchies and sequences
A recursive CTE is two queries glued together by UNION ALL: an anchor that runs once and produces the starting rows, and a recursive term that is allowed to name the CTE itself. The engine runs the anchor, then runs the recursive term again and again, appending whatever it returns, and stops as soon as an iteration returns zero rows. The RECURSIVE keyword is what permits that self-reference; leave it out in PostgreSQL, SQLite or MySQL 8 and you get a plain "relation does not exist" error on the CTE name inside itself.
The detail that decides whether your query is right is what the self-reference holds. Each iteration sees only the rows the previous iteration produced, never the whole accumulated result, so the recursive term really says "given the level I just built, build the next level". That is why r.depth + 1 counts tree levels instead of drifting, and why r.path || ' > ' || e.name extends a breadcrumb exactly one step: every output row descends from one parent row written one round earlier.
Termination comes from one of two places. Either the join in the recursive term eventually matches nothing (you reach people with no reports going down, or a NULL manager_id going up), or you write an explicit predicate such as WHERE n < 6. Cyclic data has neither ending, so edges a to b to c back to a will spin until the server runs out of memory unless you switch UNION ALL to UNION so repeated rows are discarded, or carry a visited-path column and refuse to extend a row that closed a loop.
-- setup
CREATE TABLE employee (
id integer PRIMARY KEY,
name text NOT NULL,
manager_id integer
);
INSERT INTO employee (id, name, manager_id) VALUES
(1, 'Ada', NULL),
(2, 'Brij', 1),
(3, 'Cleo', 1),
(4, 'Dmitri', 2),
(5, 'Elena', 4),
(6, 'Farid', 3);
WITH RECURSIVE reports AS (
-- anchor: everyone with no manager
SELECT id, name, 1 AS depth, name AS path
FROM employee
WHERE manager_id IS NULL
UNION ALL
-- recursive term: direct reports of the rows added last round
SELECT e.id, e.name, r.depth + 1, r.path || ' > ' || e.name
FROM reports r
JOIN employee e ON e.manager_id = r.id
)
SELECT depth, path
FROM reports
ORDER BY path;In a recursive CTE the self-reference means only the rows the previous iteration produced, and the loop ends when an iteration produces none.
Worked examples
A sequence with no table at all
Generates six rows from a single seed row, carrying a running product forward.
WITH RECURSIVE fact(n, product) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, product * (n + 1)
FROM fact
WHERE n < 6
)
SELECT n, product FROM fact;Example explained
Line 1The anchor SELECT 1, 1 is the whole starting set, so a recursive CTE needs no base table to produce rows.
Line 2product * (n + 1) reads the previous row's product, which is the only copy of it the recursive term can see.
Line 3WHERE n < 6 filters the input row, not the output, so the last row produced is n = 6, built from n = 5.
Line 4The next iteration reads (6, 720), fails the predicate, returns nothing, and the CTE stops there.
Walking upward to the ancestors
Uses the employee table from above but starts at a leaf and climbs to the root by reversing the join key.
WITH RECURSIVE ancestors AS (
SELECT id, name, manager_id, 0 AS steps_up
FROM employee
WHERE name = 'Elena'
UNION ALL
SELECT e.id, e.name, e.manager_id, a.steps_up + 1
FROM ancestors a
JOIN employee e ON e.id = a.manager_id
)
SELECT steps_up, name
FROM ancestors
ORDER BY steps_up;Example explained
Line 1The anchor selects one row, so the seed is a single leaf instead of the tree's roots.
Line 2e.id = a.manager_id flips the direction: each round fetches the parent of the row just added, one row per iteration.
Line 3Ada's manager_id is NULL, so the join matches nothing and the recursion ends with no depth predicate needed.
Line 4steps_up counts hops from Elena, which is only meaningful because each row derives from exactly one predecessor.
Surviving a cycle
Traverses a graph that loops back on itself and stops by remembering the nodes already visited.
WITH RECURSIVE edge(src, dst) AS (
VALUES ('a','b'), ('b','c'), ('c','a')
),
walk(node, path, looped) AS (
SELECT CAST('a' AS TEXT), CAST('a' AS TEXT), 0
UNION ALL
SELECT e.dst,
w.path || '>' || e.dst,
CASE WHEN w.path LIKE '%' || e.dst || '%' THEN 1 ELSE 0 END
FROM walk w
JOIN edge e ON e.src = w.node
WHERE w.looped = 0
)
SELECT node, path, looped FROM walk;Example explained
Line 1edge is an ordinary non-recursive CTE sitting in the same WITH RECURSIVE list; only walk refers to itself.
Line 2CAST('a' AS TEXT) pins the anchor's column type to match the text the recursive term builds, since the engine compares the two branches column by column.
Line 3The LIKE test marks a row whose destination already appears in path, so the loop is recorded the moment it closes.
Line 4WHERE w.looped = 0 keeps that marked row from being extended, so the query finishes with the cycle visible instead of hanging.
Important notes
PostgreSQL, SQLite and MySQL 8 require the RECURSIVE keyword and impose no default depth limit, so a bad join condition hangs the query; SQL Server omits the keyword but caps at 100 levels unless you add OPTION (MAXRECURSION n).
Rows come out in the order the iterations produced them, which looks level by level but is not guaranteed; sort by a path column in the outer SELECT if you want children printed under their parent.
Common mistakes
Putting the limit only in the outer query, as in SELECT * FROM reports WHERE depth <= 3: the CTE is fully built first, so cyclic or deep data still runs until it exhausts memory and the filter only trims what you see.
Seeding the anchor with the whole table instead of the roots (dropping WHERE manager_id IS NULL): you get one copy of every subtree from every starting point, and depth counts from a random node rather than the top.
Assuming the self-reference contains everything produced so far and writing MAX(depth) or a window function in the recursive term: PostgreSQL and SQLite reject aggregates and window functions there, and the round-by-round logic would give the wrong answer anyway.
Try it yourself
Change, predict, then run
Using the employee table from the lesson, write a recursive CTE whose anchor is WHERE name = 'Brij' and return everyone in Brij's subtree with depth measured from Brij. Then change the anchor to 'Cleo' and confirm you get only Cleo and Farid.
Open the SQL workspaceCheck your understanding
During the third iteration of a recursive CTE, what does the reference to the CTE inside the recursive term contain?
- Just the anchor rows, every time
- Only the rows produced by the previous iteration
- Every row produced so far, anchor rows included
- The base table the anchor selected from
Show answer
Each round replaces the working set with the rows the last round emitted, so the third iteration joins against only what the second one produced; that is exactly why depth + 1 advances one level per round. "Every row produced so far" describes the CTE's final result, which is what the outer SELECT reads, but if the recursive term saw that set it would re-expand parents it had already expanded and duplicate whole subtrees.