Recursive CTEs in MySQL - Step-by-Step Execution

Concept Flow - Recursive CTEs

Start Recursive CTE

↓

Anchor Member: Base Query

↓

Recursive Member: Query referencing CTE

↓

Union All: Combine Anchor + Recursive

↓

Repeat Recursive Member with new rows

↓

Stop when Recursive Member returns no rows

↓

Final Result: All combined rows

A recursive CTE starts with a base query, then repeatedly runs a recursive query that references itself, combining results until no new rows appear.

Execution Sample

MySQL

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	Action	Anchor Result	Recursive Result	Combined Result
1	Run anchor member	[1]	[]	[1]
2	Run recursive member with n=1	[]	[2]	[1, 2]
3	Run recursive member with n=2	[]	[3]	[1, 2, 3]
4	Run recursive member with n=3	[]	[]	[1, 2, 3]

💡 Recursive member returns no rows at step 4, so recursion stops.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4
n	undefined	1	2	3	3

Key Moments - 3 Insights

Why does the recursion stop at step 4?

Because the recursive member returns no new rows (empty set) at step 4, so there is nothing more to add to the combined result, as shown in execution_table row 4.

What is the role of the anchor member?

The anchor member provides the starting rows for the recursion. In the example, it returns the initial value 1 at step 1, which seeds the recursive process.

How does the recursive member reference the CTE itself?

The recursive member queries the CTE 'nums' inside its own definition, allowing it to build on previous results, as seen in the recursive SELECT n + 1 FROM nums.

Visual Quiz - 3 Questions

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

A [1]

B [2]

C [1, 2]

D [1, 2, 3]

💡 Hint

Check the 'Combined Result' column in execution_table row 2.

At which step does the recursive member return no rows?

A Step 4

B Step 3

C Step 1

D Step 2

💡 Hint

Look at the 'Recursive Result' column in execution_table; empty set appears at step 4.

If the anchor member returned 2 instead of 1, what would be the first combined result?

A [1]

B [2]

C [3]

D []

💡 Hint

Anchor member result is the first combined result; see execution_table step 1.

Concept Snapshot

WITH RECURSIVE cte_name AS (
  anchor_query
  UNION ALL
  recursive_query_referencing_cte
)
SELECT * FROM cte_name;

- Anchor query runs once to start.
- Recursive query runs repeatedly, adding rows.
- Stops when recursive query returns no rows.
- Useful for hierarchical or iterative data generation.

Full Transcript

Recursive CTEs in MySQL start with an anchor query that provides initial rows. Then a recursive query runs repeatedly, referencing the CTE itself to add new rows. This repeats until the recursive query returns no rows, combining all results. For example, generating numbers 1 to 3 starts with 1, then adds 2, then 3, and stops when no more numbers are added. This process is shown step-by-step in the execution table and variable tracker. Understanding when recursion stops and how the anchor seeds the process helps beginners grasp recursive CTEs.