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.

Practice

1. What is the main purpose of a Recursive CTE in SQL?

easy

A. To create temporary tables for one-time use

B. To join two unrelated tables

C. To optimize query performance by indexing

D. To repeatedly execute a query to process hierarchical or linked data

Solution

1. Step 1: Understand Recursive CTE definition

   A Recursive CTE runs a query repeatedly, starting from an anchor and then recursively processing related rows.

2. Step 2: Identify its main use

   This is mainly used to explore hierarchical or linked data stepwise, like organizational charts or folder trees.

3. Final Answer:

   To repeatedly execute a query to process hierarchical or linked data -> Option D

4. Quick Check:

   Recursive CTE = repeated query for linked data [OK]

Hint: Recursive CTEs repeat queries to explore linked data [OK]

Common Mistakes:

• Confusing recursive CTE with simple temporary tables
• Thinking recursive CTEs optimize indexes
• Assuming recursive CTEs join unrelated tables

2. Which of the following is the correct basic syntax to start a recursive CTE in SQL?

easy

A. WITH cte_name AS (SELECT ... UNION ALL SELECT ... FROM cte_name ...)

B. CREATE RECURSIVE cte_name AS SELECT ...

C. SELECT * FROM cte_name RECURSIVE

D. WITH RECURSIVE cte_name SELECT ... UNION SELECT ...

Solution

1. Step 1: Recall recursive CTE syntax

   The standard syntax starts with WITH cte_name AS (anchor_query UNION ALL recursive_query).

2. Step 2: Check options for correct form

   WITH cte_name AS (SELECT ... UNION ALL SELECT ... FROM cte_name ...) correctly shows the anchor and recursive parts combined with UNION ALL and references the CTE name in the recursive part.

3. Final Answer:

   WITH cte_name AS (SELECT ... UNION ALL SELECT ... FROM cte_name ...) -> Option A

4. Quick Check:

   Recursive CTE syntax = WITH + UNION ALL + self-reference [OK]

Hint: Recursive CTE uses WITH + UNION ALL + self-reference [OK]

Common Mistakes:

• Using CREATE instead of WITH
• Omitting UNION ALL
• Not referencing the CTE name recursively

3. Given the table Employees(id, manager_id) and this recursive CTE:

WITH RECURSIVE EmpCTE AS (
  SELECT id, manager_id FROM Employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id FROM Employees e JOIN EmpCTE c ON e.manager_id = c.id
)
SELECT id FROM EmpCTE ORDER BY id;

What does this query return?

medium

A. All employees who have no manager

B. All employees who report directly or indirectly to employee with id 1

C. Only the employee with id 1

D. All employees in the table

Solution

1. Step 1: Analyze the anchor query

   The anchor selects employee with id = 1.

2. Step 2: Analyze the recursive part

   The recursive part joins Employees where manager_id matches the id from previous results, thus finding direct reports, then their reports, and so on.

3. Final Answer:

   All employees who report directly or indirectly to employee with id 1 -> Option B

4. Quick Check:

   Recursive CTE finds all subordinates under id 1 [OK]

Hint: Recursive join on manager_id finds all reports [OK]

Common Mistakes:

• Thinking it returns only the anchor row
• Assuming it returns employees without managers
• Believing it returns all employees regardless of manager

4. Identify the error in this recursive CTE:

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

medium

A. Missing anchor query

B. No termination condition in recursive part

C. Recursive CTE syntax is correct and will run successfully

D. Using UNION instead of UNION ALL causes infinite loop

Solution

1. Step 1: Check anchor query

   The anchor selects 1 AS num, which is correct.

2. Step 2: Check recursive part and termination

   The recursive part adds 1 to num and stops when num reaches 5, so it has a termination condition.

3. Final Answer:

   Recursive CTE syntax is correct and will run successfully -> Option C

4. Quick Check:

   Anchor + recursive with termination = valid recursive CTE [OK]

Hint: Check for termination condition and correct UNION ALL usage [OK]

Common Mistakes:

• Confusing UNION and UNION ALL effects
• Missing termination condition causing infinite recursion
• Assuming anchor query is missing

5. You want to calculate the factorial of 5 using a recursive CTE. Which query correctly computes this?

hard

A. WITH RECURSIVE Fact(n, fact) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, fact * (n + 1) FROM Fact WHERE n < 5
)
SELECT fact FROM Fact WHERE n = 5;

B. WITH Fact AS (
SELECT 1 AS n, 1 AS fact
UNION
SELECT n + 1, fact * n FROM Fact WHERE n <= 5
)
SELECT fact FROM Fact WHERE n = 5;

C. WITH RECURSIVE Fact(n, fact) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, fact * n FROM Fact WHERE n < 5
)
SELECT fact FROM Fact WHERE n = 5;

D. WITH RECURSIVE Fact(n, fact) AS (
SELECT 0, 1
UNION ALL
SELECT n + 1, fact * n FROM Fact WHERE n < 5
)
SELECT fact FROM Fact WHERE n = 5;

Solution

1. Step 1: Understand factorial calculation

   Factorial of n is product of all integers from 1 to n. We start with n=1, fact=1.

2. Step 2: Check recursive step correctness

   WITH RECURSIVE Fact(n, fact) AS ( SELECT 1, 1 UNION ALL SELECT n + 1, fact * (n + 1) FROM Fact WHERE n < 5 ) SELECT fact FROM Fact WHERE n = 5; correctly increments n by 1 and multiplies fact by (n+1), matching factorial definition up to 5.

3. Step 3: Verify termination and final selection

   It stops when n reaches 5 and selects fact at n=5, giving 120.

4. Final Answer:

   Option A correctly computes factorial of 5 using recursive CTE -> Option A

5. Quick Check:

   Factorial recursion: multiply by next number, stop at 5 [OK]

Hint: Multiply by n+1 each step, stop at target number [OK]

Common Mistakes:

• Starting from 0 causing multiplication by zero
• Using UNION instead of UNION ALL
• Incorrect multiplication order in recursive step