The two halves
WITH RECURSIVE chain(...) AS (
<anchor> -- runs once
UNION ALL
<recursive step> -- runs repeatedly, reads `chain`
)
SELECT ... FROM chain;
The anchor is an plain query. It runs once and makes the starting rows. In the first variant it selects the employee whose manager_id is NULL — the root.
The recursive step is a query that refers to the CTE by name. It runs against the rows the previous round made, not against all built up so far. That gap is what makes the walk proceed one level at a time.
Run goes: anchor makes Ada. Step runs against {Ada} and makes Grace and Alan. Step runs against {Grace, Alan} and makes Edsger, Barbara, Donald. Step runs against those and makes Tony. Then Niklaus. Then the step finds no one reporting to Niklaus, returns no rows, and the recursion halts.
The empty result is the only stopping rule. There's no iteration limit and no depth check unless you write one.
Carrying state through
The level column in the starter query isn't special syntax. It's an plain column: the anchor sets it to 1. The step sets it to c.level + 1. any of it found the same way rides along with the recursion.
The third variant builds a text path this way, concatenating each name onto the one before to give Ada > Grace > Edsger > Tony. Sorting by that string gives a right nested listing. This Is a small trick worth knowing: sorting by path sorts a tree into doc order.
Direction is just the join condition
Run the second variant. It starts at Niklaus and walks *up*, and the only thing that changed is which side of the join rule is which:
down: JOIN chain c ON e.manager_id = c.id
up: JOIN up u ON u.manager_id = m.id
Same make, opposite direction. Ancestors and descendants are the same problem with the arrow reversed.
Generating rows from nothing
The fourth variant has no table in it. The anchor is SELECT 1 and the step adds one until it reaches ten.
This is how you give a sequence without a helper table: a row per day across a date range so a report shows zeros for days with no sales, a row per bucket for a histogram, a numbers table for splitting strings. It's one of the most handy uses and the one people find last.
Cycles
All above assumes the data is a tree. If someone turns their own manager's manager, the step never returns empty and the query runs until the server stops it.
Real data acquires cycles. The defences, in growing order of strength:
- Carry a depth column and add
WHERE level < 50 to the step. - Carry the path and add
WHERE instr(path, e.name) = 0 to refuse revisiting. - Use
UNION instead of UNION ALL. This Deduplicates — this stops a cycle but costs a check against the whole built up set each round. - In PostgreSQL, use the
CYCLE clause. This Does this properly.
A depth cap costs almost nothing and turns a hung query into a wrong-but-finite answer, which is far easier to notice.
Filling gaps: the row-per-day spine
The sequence trick earns its own worked case. It solves a problem each reporting query hits: a GROUP BY day over a table with no sales on some days simply has no row for those days. The chart skips them and a week with a zero-sales Tuesday looks like a six-day week.
The fix is to make each day in the range and LEFT JOIN the data onto it:
WITH RECURSIVE days(d) AS (
SELECT date('2026-01-01')
UNION ALL
SELECT date(d, '+1 day') FROM days WHERE d < '2026-01-31'
)
SELECT days.d, COALESCE(SUM(s.amount), 0) AS sales
FROM days LEFT JOIN sales s ON s.day = days.d
GROUP BY days.d;
The anchor is one date, the step adds a day until it reaches the end, and 31 rows come out of no table at all. The LEFT JOIN then keeps each day, and COALESCE turns the missing matches into zeros. A gap in the data turns a visible zero rather than an invisible skip. The difference between a chart that lies by omission and one that doesn't.
What a cycle actually does
"Runs forever" understates it. With UNION ALL and a cycle, each round feeds the next, and if the cycle doubles the frontier the row count explodes geometrically before the server kills it:
| round | rows produced |
|---|
| 1 | 1 |
| 5 | 16 |
| 10 | 512 |
| 20 | 524,288 |
That isn't a slow query. It's a query trying to build a billion-row mid from a handful of cyclic rows. A WHERE level < 50 guard turns it into a wrong-but-finite answer that returns at once and is obvious in the output, which is far easier to notice than a link that simply never comes back. UNION instead of UNION ALL stops it too, by deduplicating — but that checks each new row against all found so far, each round, so on acyclic data it makes a correct query needlessly quadratic. The depth cap costs one int check per row. Reach for it first. A path-based guard. Refusing to revisit a name already on the built up path — is stricter and costs a substring check per row, and it's the right choice when the data is a graph rather than a tree and genuine repeats are expected.
Questions people ask about Recursive CTEs
What are the two parts of a recursive CTE? An anchor query that supplies the starting rows, and a recursive query that refers to the CTE itself. They are joined with UNION or UNION ALL.
How does a recursive CTE know when to stop? It stops when the recursive part returns no new rows. If it never does, you need your own limit.
What causes infinite recursion? A cycle in the data, such as A pointing to B and B pointing to A. Track visited ids or add a depth limit.
Should I use UNION or UNION ALL in a recursive CTE? UNION ALL is faster. UNION removes duplicates each round, which can break cycles but costs more.
Can a recursive CTE generate rows from nothing? Yes. Start with a single row and add one each step, for example to build a row for every date in a range.
What is the difference between a recursive CTE and a self join? A self join walks a fixed number of levels. A recursive CTE repeats until there is nothing left, so it handles any depth.
Where it goes wrong with Recursive CTEs
No termination guard. Cyclic data plus UNION ALL runs forever.
An anchor that matches too much. If the anchor selects each row rather than the roots, each row is a starting point and the result is huge.
Expecting the step to see all found so far. It sees the previous round only. Aggregating across all levels has to happen in the outer query.
Recursing over a large table without an index on the join column. Each round is a join. Without an index each round is a scan.