Recursive CTE concept in SQL - Step-by-Step Execution

Concept Flow - Recursive CTE concept

Start with anchor query

↓

Anchor result set

↓

Recursive query uses previous result

↓

Combine anchor + recursive results

↓

Check if new rows added

↓

Repeat recursive

↓

Final combined result

A recursive CTE starts with an anchor query, then repeatedly applies a recursive query to build results until no new rows appear.

Execution Sample

SQL

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 3
)
SELECT * FROM nums;

This query generates numbers from 1 to 3 using a recursive CTE.

Execution Table

Step	Query Part	Current Result Set	New Rows Added	Action
1	Anchor query	[{n:1}]	1 row	Start with base row n=1
2	Recursive query	[{n:1}, {n:2}]	1 row	Add n=2 by n+1 where n<3
3	Recursive query	[{n:1}, {n:2}, {n:3}]	1 row	Add n=3 by n+1 where n<3
4	Recursive query	[{n:1}, {n:2}, {n:3}]	0 rows	No new rows, stop recursion
5	Final result	[{n:1}, {n:2}, {n:3}]	N/A	Return combined results

💡 Recursion stops when recursive query adds 0 new rows (step 4).

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Result Set	empty	[{n:1}]	[{n:1}, {n:2}]	[{n:1}, {n:2}, {n:3}]	[{n:1}, {n:2}, {n:3}]	[{n:1}, {n:2}, {n:3}]
New Rows	0	1	1	1	0	0

Key Moments - 3 Insights

Why does the recursion stop even though the recursive query looks like it could run forever?

Because the recursive query only adds rows where n < 3. Once n reaches 3, no new rows satisfy the condition, so no new rows are added (see execution_table step 4).

What is the role of the UNION ALL in the recursive CTE?

UNION ALL combines the anchor query result with the recursive query results each iteration, building up the full result set step by step (see execution_table rows 1-5).

How does the recursive query know which rows to use each time?

The recursive query references the CTE name (nums) itself, so it uses the rows generated so far to produce new rows (see execution_table steps 2 and 3).

Visual Quiz - 3 Questions

Look at the execution table, what is the result set after step 2?

A [{n:1}, {n:2}]

B [{n:2}]

C [{n:1}]

D [{n:1}, {n:2}, {n:3}]

💡 Hint

Check the 'Current Result Set' column for step 2 in the execution_table.

At which step does the recursion stop adding new rows?

A Step 3

B Step 4

C Step 5

D Step 2

💡 Hint

Look for the step where 'New Rows Added' is 0 in the execution_table.

If the condition in the recursive query changed to n < 5, how would the final result set change?

A It would be empty

B It would only include 1 to 3

C It would include numbers 1 to 5

D It would include numbers 1 to 4

💡 Hint

Refer to how the condition n < 3 limits the recursion in the execution_table and variable_tracker.

Concept Snapshot

Recursive CTE syntax:
WITH RECURSIVE cte_name AS (
  anchor_query
  UNION ALL
  recursive_query_referencing_cte_name
)
SELECT * FROM cte_name;

Behavior:
- Anchor query runs once
- Recursive query runs repeatedly using previous results
- Stops when no new rows added

Key rule: Recursive query must reduce or limit rows to avoid infinite loops.

Full Transcript

A recursive CTE in SQL starts with an anchor query that produces initial rows. Then a recursive query runs repeatedly, each time using the rows generated so far to produce new rows. These results are combined with UNION ALL. The recursion stops when the recursive query produces no new rows. For example, a query generating numbers from 1 to 3 starts with 1, then adds 2, then 3, and stops because the condition n < 3 is no longer true. This process builds a final result set step by step. The key is that the recursive query references the CTE itself and must have a condition to stop recursion to avoid infinite loops.