CTEs (WITH clause) — recursive CTEs: Question bank

Before we start, one word we lean on constantly needs pinning down so nothing here is used before it's defined.

True or false — justify

TF1. A recursive CTE is genuinely recursive, like a function that calls itself.

False — it is iteration dressed up. The engine runs the recursive member in a loop, feeding last round's rows (

W_{i - 1}

) back in; there is no call stack.

TF2. The anchor query runs once per iteration.

False — the anchor runs exactly once at the very start to seed

W_{0}

; only the recursive member repeats.

TF3. Inside the recursive member, cte_name refers to every row gathered so far.

False — it refers only to

W_{i - 1}

, the previous round's new rows. This is the single most common mental-model error.

TF4. UNION ALL and UNION are interchangeable in a recursive CTE.

False — UNION dedupes each round (and can silently kill a needed loop or act as a free cycle guard); UNION ALL keeps everything and will loop forever on a cycle. See UNION vs UNION ALL.

TF5. If the source data is finite, a recursive CTE is guaranteed to terminate.

False — SELECT n+1 FROM nums with no bound generates infinitely many rows from a finite table. Termination needs a rule that eventually yields zero new rows, not just finite input.

TF6. The loop stops as soon as the result stops growing usefully.

False — it stops on the exact first round where the working table is empty (

W_{n + 1} = \emptyset

). "Useful" is not a concept the engine has.

TF7. You can put ORDER BY inside the recursive member to control traversal order.

False — most engines forbid ORDER BY/LIMIT in the recursive member; ordering belongs in the outer final SELECT.

TF8. A recursive CTE with a self-join is really just a plain join the engine does once.

False — it re-runs the join every round against the shrinking working table, descending one level of the hierarchy per iteration.

TF9. The anchor is the base case and the recursive member is the inductive step, matching Mathematical Induction.

True — the anchor establishes level 1; the recursive member assumes level

i

exists (in

W_{i - 1}

) and builds level

i + 1

, exactly like proving

P (k) \Rightarrow P (k + 1)

TF10. Adding a lvl (depth) column changes what rows the CTE produces.

False — a lvl = c.lvl + 1 column only labels rows; it changes nothing about traversal unless you also add a WHERE lvl < k bound.

Spot the error

ERR1. WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM nums) SELECT * FROM nums;

No stop guard — n+1 never fails a condition, so

W_{i}

never empties and it loops forever. Add WHERE n < 5.

ERR2. Recursive member reads the whole result: ... UNION ALL SELECT MAX(n)+1 FROM nums.

Aggregates like MAX over the self-reference are illegal — the recursive member may only reference

W_{i - 1}

row-by-row, and aggregation/subqueries over the CTE are forbidden.

ERR3. Graph reachability with UNION ALL and no visited-set on cyclic edges.

A cycle keeps producing "new" rows forever. Fix by carrying a path array and excluding visited nodes, or switch to UNION for set semantics — see Tree and Graph Data Structures.

ERR4. ... e JOIN chain c ON e.id = c.id in an org-chart descent.

Joins a node to itself, so no new children are found and it stops after the anchor. It should be e.manager_id = c.id — link a person to the boss discovered last round.

ERR5. WITH cte AS (SELECT 1 UNION ALL SELECT n+1 FROM cte ...) — missing RECURSIVE.

A plain CTE cannot reference itself; the parser rejects the self-reference. You must write WITH RECURSIVE.

ERR6. SELECT n FROM nums LIMIT 5 placed inside the recursive member to cap it.

LIMIT in the recursive member is disallowed by most engines and, even where allowed, doesn't reliably bound the loop. Bound with a WHERE, and LIMIT in the outer query.

ERR7. Two separate CTEs both named x, one recursive, referenced in the recursive part expecting the other.

Name collision — the self-reference always binds to the CTE being defined (

W_{i - 1}

), so it silently reads the wrong rows. Rename one.

Why questions

WHY1. Why does the recursive member see only

W_{i - 1}

and not all of

R

So each row is expanded exactly once. Re-scanning the whole growing result would re-expand old rows endlessly, breaking termination.

WHY2. Why does feeding only new rows guarantee termination on acyclic data?

Each round strictly descends the hierarchy; with no cycles the depth is finite, so eventually no unvisited children remain and

W_{i}

empties.

WHY3. Why is UNION (not UNION ALL) sometimes a "free" cycle guard?

It removes duplicate rows every round; a revisited node produces a duplicate that gets dropped, so the loop can't be fed forever by the same rows.

WHY4. Why carry a path array instead of relying on UNION for graphs?

UNION only dedupes on the whole row; two different paths to the same node are distinct rows and survive. A path array lets you dedupe on node identity and reconstruct the route.

WHY5. Why is a recursive CTE preferred over looping in application code?

It keeps traversal inside the database, avoiding round-trips per level and letting the planner optimise the joins — one query instead of N network calls.

WHY6. Why does the anchor need its own WHERE manager_id IS NULL in the org example?

It selects the single seed row (the CEO). Without a precise anchor you'd seed every employee and the "levels" would be meaningless.

Edge cases

EDGE1. What does a recursive CTE return if the anchor selects zero rows?

W_{0} = \emptyset

, so round 1 already produces nothing and the loop stops immediately — the result is empty.

EDGE2. What if the recursive member matches nothing on the very first round?

You get just the anchor rows;

W_{1} = \emptyset

triggers the stop after one iteration.

EDGE3. Self-loop edge ('A','A') with a path/visited guard — what happens?

The guard e.dst <> ALL(r.path) rejects 'A' since it's already in the path, so the self-loop is skipped and traversal terminates.

EDGE4. Two paths of equal length reach the same node under UNION ALL with a path column.

Both survive as distinct rows (different paths); an outer SELECT DISTINCT node collapses them if you only want the node set.

EDGE5. Depth-limited traversal with WHERE c.lvl < 3 but a chain 5 deep — what's missing?

Levels 4 and 5 are never produced; the bound empties

W_{i}

early. The result is a truncated subtree, which may be intended or a bug.

EDGE6. UNION (dedupe) on a legitimately repeated value like counting duplicates.

UNION silently collapses the repeats, so a genuinely recurring row is lost — here UNION ALL is required and the cycle must be bounded another way.

EDGE7. A hierarchy where a node is its own ancestor (data corruption cycle) under UNION ALL, no guard.

Infinite loop — the engine spins until it errors or exhausts memory. A visited-set guard or UNION is the only defence.

Connections

CTEs (WITH clause) — recursive CTEs — the parent topic these traps drill
Common Table Expressions (WITH clause) — why a plain CTE can't self-reference
UNION vs UNION ALL — the choice that decides termination
SQL Joins — the self-join at the heart of the recursive member
Tree and Graph Data Structures — cycles, depth, visited sets
Mathematical Induction — anchor = base case, recursive member = inductive step
Window Functions — the sibling "advanced read" tool