CTEs (WITH clause) — recursive CTEs — Coding notes

WHAT is a CTE first?

Definition CTE (Common Table Expression)

A CTE is a named, temporary result set defined with the ==WITH== keyword that exists only for the duration of a single query. Think of it as a "local view" you can reference by name.

WITH dept_totals AS (
    SELECT dept_id, SUM(salary) AS total
    FROM employees
    GROUP BY dept_id
)
SELECT * FROM dept_totals WHERE total > 100000;

WHY use one? It makes nested queries readable (top-to-bottom instead of inside-out) and lets you reference the same subquery multiple times.

WHAT makes it recursive?

A normal CTE can't refer to itself. A recursive CTE can — that self-reference is what creates the loop.

Definition Recursive CTE structure

WITH RECURSIVE cte_name AS (
    -- (1) ANCHOR / base case: runs once
    SELECT ...
    UNION ALL                 -- (or UNION to dedupe)
    -- (2) RECURSIVE member: references cte_name itself
    SELECT ...
    FROM cte_name JOIN ...
    WHERE <stops eventually>
)
SELECT * FROM cte_name;

The anchor seeds the result; the recursive member references the CTE and runs again and again.

HOW the execution actually works (derivation from first principles)

Don't trust the word "recursive" — it's really iteration under the hood. Here is the exact algorithm the engine runs:

Run the anchor query. Put its rows into the result $R$ and into a working table $W_0$ .
Repeat for step $i = 1, 2, \dots$ $i = 1, 2, \dots$ :
- Run the recursive member, but every mention of cte_name reads only from $W_{i-1}$ (the rows produced last round).
- Call the new rows $W_i$ .
- Append $W_i$ to the final result $R$ .
Stop when an iteration produces zero new rows ( $W_i = \varnothing$ ).

So the total result is the union of every round: $R = W_0 \;\cup\; W_1 \;\cup\; W_2 \;\cup\; \dots \;\cup\; W_n \quad\text{where } W_{n+1}=\varnothing$

Figure — CTEs (WITH clause) — recursive CTEs

Worked example 1: count 1 → 5

WITH RECURSIVE nums AS (
    SELECT 1 AS n                 -- anchor
    UNION ALL
    SELECT n + 1 FROM nums        -- recursive member
    WHERE n < 5                   -- stop guard
)
SELECT n FROM nums;

Round	Working table $W_i$	New rows?
anchor $W_0$	`{1}`	yes
$W_1$	`{2}`	yes
$W_2$	`{3}`	yes
$W_3$	`{4}`	yes
$W_4$	`{5}`	yes
$W_5$	`{}` (6 fails `n<5`)	stop

Result: 1,2,3,4,5.

Why WHERE n < 5? It's the base-case-of-induction in reverse: without it, $W_i$ never empties → infinite loop.
Why UNION ALL not UNION? We know rows are unique here, and UNION ALL skips the dedupe sort → faster.

Worked example 2: walk an org chart (find all reports of CEO)

Table emp(id, name, manager_id) where manager_id points to the boss (NULL for CEO).

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl     -- anchor: the CEO
    FROM emp
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM emp e
    JOIN chain c ON e.manager_id = c.id        -- e reports to someone we've found
)
SELECT id, name, lvl FROM chain ORDER BY lvl;

Why join e.manager_id = c.id? Each round adds people whose manager was discovered in the previous round → we descend one level of the tree per iteration.
Why the lvl column? Dual-coding the depth: $lvl=1$ is CEO, $lvl=2$ direct reports, etc. Lets you filter/limit depth.

Worked example 3: detect a path in a graph (with cycle guard)

edges(src, dst). Find everything reachable from node 'A', avoiding infinite loops:

WITH RECURSIVE reach AS (
    SELECT 'A' AS node, ARRAY['A'] AS path
    UNION ALL
    SELECT e.dst, r.path || e.dst
    FROM edges e
    JOIN reach r ON e.src = r.node
    WHERE e.dst <> ALL(r.path)        -- cycle guard: don't revisit
)
SELECT DISTINCT node FROM reach;

Why the path array + <> ALL? A graph can have cycles → $W_i$ might never empty. We carry the visited set so we refuse to re-enter a node, forcing termination.

Common mistakes (steel-manned)

Recall Feynman: explain to a 12-year-old

Imagine a treasure hunt. The first clue (the anchor) is handed to you. Each clue tells you where the next clues are. You only ever look at the clue you just found to get the next one — not all your old clues. You keep going until a clue leads to a dead end (no new clue) and then you stop. A recursive CTE is the database doing this treasure hunt automatically: "here's the start, here's how to get the next step, keep going until there's nothing new."

Active-recall flashcards

What keyword introduces a CTE?

WITH (add RECURSIVE for self-referencing ones)

What are the two members of a recursive CTE?

The anchor (base case, runs once) and the recursive member (references the CTE, repeats).

During iteration, which rows can the recursive member see?

Only the previous round's new rows (the working table

W_{i-1}

), not the full accumulated result.

What is the exact termination condition?

The loop stops when an iteration produces zero new rows (

W_{n+1}=\varnothing

Difference between UNION and UNION ALL in a recursive CTE?

UNION dedupes each round (can act as a cycle guard, slower); UNION ALL keeps all rows (faster, loops forever on cycles).

How do you prevent infinite loops on a cyclic graph?

Carry a visited-path array (or use UNION) and exclude already-visited nodes, or impose a depth limit.

Why can't a plain (non-recursive) CTE reference itself?

It's a one-shot named subquery; only WITH RECURSIVE enables the self-reference that creates the iteration.

Where should ORDER BY / LIMIT go for a recursive CTE?

In the outer final SELECT, not inside the recursive member.

Connections

Common Table Expressions (WITH clause) — the non-recursive parent concept
SQL Joins — the recursive member almost always joins the CTE to a base table
UNION vs UNION ALL — set semantics drive termination
Tree and Graph Data Structures — what recursive CTEs traverse
Window Functions — the other "advanced read" SQL tool
Mathematical Induction — anchor = base case, recursive member = inductive step

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Bhai, recursive CTE ka funda simple hai: SQL me normally loop nahi hota, but WITH RECURSIVE use karke hum loop bana lete hain. Do hisse hote hain — anchor (starting point, ek baar chalta hai) aur recursive member (jo khud CTE ko reference karke baar-baar chalta hai). Jaise treasure hunt — pehli clue di gayi (anchor), uske baad har clue agli clue batati hai, aur jab koi nayi clue nahi milti to ruk jaate ho.

Sabse important baat jo log galat samajhte hain: recursive part ke andar jab tum CTE ka naam likhte ho, wo poora result nahi dikhata — sirf pichle round me jo nayi rows bani thi wahi dikhata hai (working table). Isi wajah se loop terminate hota hai. Agar har baar pura result re-scan hota to kabhi khatam hi nahi hota. Loop tab rukta hai jab kisi round me zero nayi rows banti hain.

Practical use? Org chart (kaun kiska boss), folder tree, ya graph me path nikalna — yahan recursion zaroori hai kyunki depth pehle se pata nahi hoti. Graph me cycle ho to infinite loop ka danger hota hai, isliye ek path array rakhte ho ya UNION use karte ho taaki visited node dobara na ghuse. Aur dhyan rakho: UNION ALL fast hai par cycle pe phasega; UNION duplicate hata deta hai (free cycle-guard) par thoda slow. Stop condition (WHERE n < 5 jaisa) bhulna sabse common mistake hai — uske bina query hang!

CTEs (WITH clause) — recursive CTEs

WHAT is a CTE first?

WHAT makes it recursive?

HOW the execution actually works (derivation from first principles)

Worked example 1: count 1 → 5

Worked example 2: walk an org chart (find all reports of CEO)

Worked example 3: detect a path in a graph (with cycle guard)

Common mistakes (steel-manned)

Active-recall flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections