CTEs (WITH clause) — recursive CTEs — Hinglish notes

4.4.10 · Coding › Databases

WHAT hai pehle ek CTE?

Definition CTE (Common Table Expression)

Ek CTE ek named, temporary result set hota hai jo ==WITH== keyword se define hota hai aur sirf ek single query ki duration ke liye exist karta hai. Ise ek "local view" samjho jise tum naam se reference kar sakte ho.

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 karo? Yeh nested queries ko readable banata hai (top-to-bottom instead of inside-out) aur tumhe ek hi subquery ko kai baar reference karne deta hai.

WHAT ise recursive banata hai?

Ek normal CTE khud ko refer nahi kar sakta. Ek recursive CTE kar sakta hai — wahi self-reference loop create karta hai.

Definition Recursive CTE structure

WITH RECURSIVE cte_name AS (
    -- (1) ANCHOR / base case: ek baar chalta hai
    SELECT ...
    UNION ALL                 -- (ya UNION dedupe ke liye)
    -- (2) RECURSIVE member: cte_name ko khud reference karta hai
    SELECT ...
    FROM cte_name JOIN ...
    WHERE <kabhi na kabhi rukta hai>
)
SELECT * FROM cte_name;

Anchor result ko seed karta hai; recursive member CTE ko reference karta hai aur baar-baar chalta rehta hai.

HOW execution actually kaam karta hai (derivation from first principles)

"Recursive" word par bharosa mat karo — hood ke andar yeh actually iteration hai. Engine yeh exact algorithm run karta hai:

Anchor query run karo. Uske rows result $R$ mein aur ek working table $W_{0}$ mein daalo.
Step $i = 1, 2, \dots$ ke liye repeat karo:
- Recursive member run karo, lekin cte_name ka har mention sirf $W_{i - 1}$ se read kare (woh rows jo pichli baar produce hue the).
- Naye rows ko $W_{i}$ kaho.
- $W_{i}$ ko final result $R$ mein append karo.
Jab ek iteration zero naye rows produce kare tab ruk jao ( $W_{i} = \emptyset$ ).

Toh total result har round ka union hai: $R = W_{0} \cup W_{1} \cup W_{2} \cup \dots \cup W_{n} where W_{n + 1} = \emptyset$

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}$	Naye rows?
anchor $W_{0}$	`{1}`	haan
$W_{1}$	`{2}`	haan
$W_{2}$	`{3}`	haan
$W_{3}$	`{4}`	haan
$W_{4}$	`{5}`	haan
$W_{5}$	`{}` (6 fails `n<5`)	ruko

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

WHERE n < 5 kyun? Yeh induction ka base case hai reverse mein: iske bina, $W_{i}$ kabhi khaali nahi hota → infinite loop.
UNION ALL kyun na ki UNION? Hum jaante hain rows unique hain yahan, aur UNION ALL dedupe sort skip karta hai → faster.

Worked example 2: ek org chart walk karo (CEO ke saare reports dhundo)

Table emp(id, name, manager_id) jahan manager_id boss ki taraf point karta hai (CEO ke liye NULL).

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl     -- anchor: 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 kisi aise ko report karta hai jise hum dhundh chuke hain
)
SELECT id, name, lvl FROM chain ORDER BY lvl;

e.manager_id = c.id join kyun? Har round mein un logon ko add karte hain jinka manager pichle round mein discover hua tha → hum tree ke ek level neeche utarte hain per iteration.
lvl column kyun? Depth ka dual-coding: $l v l = 1$ CEO hai, $l v l = 2$ direct reports hain, etc. Isse depth filter/limit kar sakte ho.

Worked example 3: graph mein ek path detect karo (cycle guard ke saath)

edges(src, dst). Node 'A' se reachable sab kuch dhundo, infinite loops se bachte hue:

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: revisit mat karo
)
SELECT DISTINCT node FROM reach;

path array + <> ALL kyun? Ek graph mein cycles ho sakti hain → $W_{i}$ kabhi khaali na ho. Hum visited set carry karte hain taaki hum kisi node mein dobara enter karne se mana kar sakein, termination force karte hue.

Common mistakes (steel-manned)

Recall Feynman: 12-saal ke bachche ko explain karo

Ek treasure hunt imagine karo. Pehla clue (anchor) tumhe diya jaata hai. Har clue batata hai agle clues kahan hain. Tum hamesha sirf abhi mila clue dekhte ho agle ke liye — apne purane saare clues nahi. Tum tab tak chalte rehte ho jab tak ek clue dead end par le jaaye (koi naya clue nahi) aur phir ruk jaate ho. Ek recursive CTE database hai jo yeh treasure hunt automatically kar raha hai: "yahan se shuru karo, yahan bataya hai agle step kaise jaao, tab tak chalte raho jab tak kuch naya na ho."

Active-recall flashcards

CTE introduce karne wala keyword kaunsa hai?

WITH (RECURSIVE add karo self-referencing ones ke liye)

Recursive CTE ke do members kaunse hain?

Anchor (base case, ek baar chalta hai) aur recursive member (CTE ko reference karta hai, repeat karta hai).

Iteration ke dauran, recursive member kaunse rows dekh sakta hai?

Sirf pichle round ke naye rows (working table

W_{i - 1}

), poora accumulated result nahi.

Exact termination condition kya hai?

Loop tab rukta hai jab ek iteration zero naye rows produce kare (

W_{n + 1} = \emptyset

Recursive CTE mein UNION aur UNION ALL ka difference?

UNION har round mein dedupe karta hai (cycle guard ki tarah kaam kar sakta hai, slower); UNION ALL saare rows rakhta hai (faster, cycles par forever loop karta hai).

Cyclic graph par infinite loops kaise rokein?

Ek visited-path array carry karo (ya UNION use karo) aur already-visited nodes exclude karo, ya depth limit impose karo.

Ek plain (non-recursive) CTE khud ko reference kyun nahi kar sakta?

Yeh ek one-shot named subquery hai; sirf WITH RECURSIVE woh self-reference enable karta hai jo iteration create karta hai.

Recursive CTE ke liye ORDER BY / LIMIT kahan jaana chahiye?

Outer final SELECT mein, recursive member ke andar nahi.

Connections

Common Table Expressions (WITH clause) — non-recursive parent concept
SQL Joins — recursive member almost hamesha CTE ko base table se join karta hai
UNION vs UNION ALL — set semantics termination drive karte hain
Tree and Graph Data Structures — recursive CTEs kya traverse karte hain
Window Functions — doosra "advanced read" SQL tool
Mathematical Induction — anchor = base case, recursive member = inductive step