Recursive CTE for series generation in SQL - Step-by-Step Execution

Concept Flow - Recursive CTE for series generation

Start with anchor query

↓

Recursive query references CTE

↓

Add next row to series

↓

Check termination condition

↓

Stop

↓

Final result set

The recursive CTE starts with a base row, then repeatedly adds rows by calling itself until a stop condition is met, producing a series.

Execution Sample

SQL

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

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

Execution Table

Step	Query Part	Current n	Action	Resulting Rows
1	Anchor query	n=1	Start series with 1	[1]
2	Recursive query	n=1	Add n+1=2 since 1<5	[1, 2]
3	Recursive query	n=2	Add n+1=3 since 2<5	[1, 2, 3]
4	Recursive query	n=3	Add n+1=4 since 3<5	[1, 2, 3, 4]
5	Recursive query	n=4	Add n+1=5 since 4<5	[1, 2, 3, 4, 5]
6	Recursive query	n=5	Stop recursion since 5 < 5 is false	[1, 2, 3, 4, 5]

💡 Recursion stops when n reaches 5 because the condition n < 5 becomes false.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
n	1	2	3	4	5	5

Key Moments - 3 Insights

Why does the recursion stop when n equals 5?

Because the recursive query has a condition WHERE n < 5. When n is 5, this condition is false, so no new rows are added (see execution_table row 6).

What is the role of the anchor query in the recursive CTE?

The anchor query provides the starting row(s) for the recursion. Without it, the recursive part has no base to build on (see execution_table row 1).

How does the recursive query add new rows?

It selects n + 1 from the current rows where n < 5, adding one new row each recursion step (see execution_table rows 2-5).

Visual Quiz - 3 Questions

Look at the execution table, what is the value of n after step 3?

A 3

B 4

C 2

D 5

💡 Hint

Check the 'Resulting Rows' column in execution_table row 3.

At which step does the recursion stop adding new rows?

A Step 4

B Step 5

C Step 6

D Step 3

💡 Hint

Look at the 'Action' column in execution_table row 6 for the stopping condition.

If the condition changed to WHERE n < 3, how many rows would the final result have?

A 2 rows

B 3 rows

C 5 rows

D 4 rows

💡 Hint

Refer to variable_tracker and how n increments each step until the condition fails.

Concept Snapshot

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

- Anchor query starts the series.
- Recursive query adds rows until condition fails.
- Useful for generating sequences or hierarchical data.

Full Transcript

A recursive CTE starts with an anchor query that provides the first row(s). Then, the recursive query repeatedly references the CTE itself to add new rows. This continues until a termination condition is met, stopping recursion. For example, generating numbers 1 to 5 starts with 1, then adds 2, 3, 4, and 5 one by one. When the condition n < 5 is false at n=5, recursion stops. This method is useful for creating series or traversing hierarchical data in SQL.