Recursive CTE for hierarchical data in SQL - Step-by-Step Execution

Concept Flow - Recursive CTE for hierarchical data

Start with base query

↓

Anchor member: select root rows

↓

Recursive member: join CTE to table

↓

Combine results with UNION ALL

↓

Repeat recursive member until no new rows

↓

Final result: full hierarchy

The recursive CTE starts with root rows, then repeatedly joins to find child rows, combining all levels until no more children are found.

Execution Sample

SQL

WITH RECURSIVE OrgChart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN OrgChart oc ON e.manager_id = oc.id
)
SELECT * FROM OrgChart;

This query builds an organizational chart showing employees and their levels starting from top managers.

Execution Table

Step	Action	Rows Returned	New Rows Added	Explanation
1	Anchor member: select employees with no manager	2 rows	2 rows	Selects top-level managers (id 1 and 2) with level 1
2	Recursive member: join employees to OrgChart on manager_id = id	3 rows	3 rows	Finds employees reporting to managers from step 1, level 2
3	Recursive member: join employees to OrgChart on manager_id = id	2 rows	2 rows	Finds employees reporting to managers from step 2, level 3
4	Recursive member: join employees to OrgChart on manager_id = id	0 rows	0 rows	No more employees found, recursion ends
5	Final result: union of all rows	7 rows	N/A	Complete hierarchy with levels 1 to 3

💡 Recursion stops when no new rows are added in step 4

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
OrgChart rows	empty	2 rows (level 1)	5 rows (levels 1-2)	7 rows (levels 1-3)	7 rows (no change)	7 rows (final)
New rows added	N/A	2	3	2	0	N/A

Key Moments - 3 Insights

Why does the recursion stop after step 4?

Because in step 4, the recursive member returns 0 new rows, meaning no more child rows exist to add, so recursion ends as shown in the execution_table.

Why do we use UNION ALL instead of UNION?

UNION ALL includes all rows without removing duplicates, which is important here to keep all hierarchical levels and avoid extra overhead, as seen in the combined results in step 5.

What does the 'level' column represent and how is it calculated?

The 'level' shows the depth in the hierarchy, starting at 1 for root rows and increasing by 1 each recursion, as tracked in the variable_tracker and execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows are returned after the second recursive step (Step 3)?

A 3 rows

B 2 rows

C 5 rows

D 7 rows

💡 Hint

Check the 'Rows Returned' column for Step 3 in the execution_table.

At which step does the recursion stop adding new rows?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

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

If the anchor member returned 3 rows instead of 2, how would the final number of rows change?

A Final rows would stay the same

B Final rows would decrease

C Final rows would increase by 1 or more

D Final rows would be exactly 3

💡 Hint

Refer to variable_tracker showing how initial rows affect total rows.

Concept Snapshot

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

Use for hierarchical data by starting with root rows and recursively joining children.
Recursion stops when no new rows are found.
Add a level column to track depth in hierarchy.

Full Transcript

A recursive CTE starts with an anchor query selecting root rows, such as employees with no manager. Then, the recursive query joins the CTE back to the table to find child rows. This repeats, adding rows at each level until no new rows are found, ending recursion. The example query builds an organizational chart with levels. The execution table shows each step's rows returned and new rows added. The variable tracker shows how the CTE grows. Key points include why recursion stops when no new rows appear, why UNION ALL is used to keep duplicates, and how the level column tracks depth. The visual quiz tests understanding of rows at each step and effects of changing the anchor query.