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.

Practice

1. What is the main purpose of a RECURSIVE CTE in SQL when working with hierarchical data?

easy

A. To speed up simple SELECT queries without joins

B. To repeatedly query connected rows to explore hierarchical relationships

C. To create temporary tables for storing unrelated data

D. To update multiple tables at once

Solution

1. Step 1: Understand the role of recursive CTEs

   Recursive CTEs allow querying hierarchical or connected data by repeatedly joining rows to find related entries.

2. Step 2: Compare options with this purpose

   Only To repeatedly query connected rows to explore hierarchical relationships describes this repeated querying of connected rows to explore hierarchies.

3. Final Answer:

   To repeatedly query connected rows to explore hierarchical relationships -> Option B

4. Quick Check:

   Recursive CTE = repeated connected row queries [OK]

Hint: Recursive CTEs repeatedly join related rows to explore hierarchies [OK]

Common Mistakes:

• Confusing recursive CTEs with simple temporary tables
• Thinking recursive CTEs speed up unrelated queries
• Assuming recursive CTEs update data

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

easy

A. DECLARE RECURSIVE OrgChart BEGIN ... END

B. CREATE RECURSIVE TABLE OrgChart AS ( ... )

C. SELECT RECURSIVE OrgChart FROM ...

D. WITH RECURSIVE OrgChart AS ( ... )

Solution

1. Step 1: Recall recursive CTE syntax

   The correct way to start a recursive CTE is using WITH RECURSIVE Name AS ( ... ).

2. Step 2: Check each option

   WITH RECURSIVE OrgChart AS ( ... ) matches the correct syntax. Options A, B, and C use invalid or unrelated SQL commands.

3. Final Answer:

   WITH RECURSIVE OrgChart AS ( ... ) -> Option D

4. Quick Check:

   Recursive CTE starts with WITH RECURSIVE [OK]

Hint: Recursive CTE always starts with WITH RECURSIVE [OK]

Common Mistakes:

• Using CREATE instead of WITH for CTEs
• Trying to SELECT RECURSIVE directly
• Using DECLARE which is for procedural code

3. Given the table Employees(id, name, manager_id) where manager_id references id, what will the following query return?

WITH RECURSIVE Subordinates AS (
  SELECT id, name, manager_id FROM Employees WHERE id = 2
  UNION ALL
  SELECT e.id, e.name, e.manager_id FROM Employees e
  JOIN Subordinates s ON e.manager_id = s.id
)
SELECT * FROM Subordinates;

medium

A. All employees who report directly or indirectly to employee with id 2

B. Only the employee with id 2

C. All employees who have no manager

D. All employees in the company

Solution

1. Step 1: Analyze the base query

   The base query selects the employee with id = 2.

2. Step 2: Analyze the recursive part

   The recursive part joins employees whose manager_id matches any id found so far, thus finding all subordinates under employee 2.

3. Final Answer:

   All employees who report directly or indirectly to employee with id 2 -> Option A

4. Quick Check:

   Recursive join finds all subordinates [OK]

Hint: Recursive CTE with join on manager_id finds all subordinates [OK]

Common Mistakes:

• Thinking it returns only the base employee
• Assuming it returns employees without managers
• Believing it returns all employees regardless

4. Identify the error in this recursive CTE that tries to list all descendants of a node in a tree:

WITH RECURSIVE Descendants AS (
  SELECT id, parent_id FROM Tree WHERE id = 1
  UNION ALL
  SELECT t.id, t.parent_id FROM Tree t
  JOIN Descendants d ON t.id = d.parent_id
)
SELECT * FROM Descendants;

medium

A. The JOIN condition should be ON t.parent_id = d.id, not t.id = d.parent_id

B. The base query should select from Descendants, not Tree

C. Recursive CTEs cannot use JOINs

D. The recursive part should use UNION, not UNION ALL

Solution

1. Step 1: Understand the tree relationship

   Each node's parent_id points to its parent. To find descendants, join where child's parent_id equals parent's id.

2. Step 2: Check the JOIN condition

   The query incorrectly joins on t.id = d.parent_id. It should be t.parent_id = d.id to find children of current nodes.

3. Final Answer:

   The JOIN condition should be ON t.parent_id = d.id, not t.id = d.parent_id -> Option A

4. Quick Check:

   Join child parent_id to parent id [OK]

Hint: Join child's parent_id to parent's id to find descendants [OK]

Common Mistakes:

• Reversing join condition columns
• Using base query from recursive CTE itself
• Thinking recursive CTEs can't use JOIN
• Confusing UNION and UNION ALL usage

5. You have a parts list table Parts(id, name, parent_part_id) representing assemblies and subassemblies. Write a recursive CTE query to find the full hierarchy of parts under part with id = 10, including the root part itself, and show the level (depth) of each part in the hierarchy starting at 0 for the root. Which query is correct?

hard

A. WITH RECURSIVE PartHierarchy AS (
SELECT id, name, parent_part_id FROM Parts WHERE parent_part_id IS NULL
UNION ALL
SELECT p.id, p.name, p.parent_part_id FROM Parts p
JOIN PartHierarchy ph ON p.id = ph.parent_part_id
)
SELECT * FROM PartHierarchy WHERE id = 10;

B. WITH RECURSIVE PartHierarchy AS (
SELECT id, name, parent_part_id, 1 AS level FROM Parts WHERE id = 10
UNION ALL
SELECT p.id, p.name, p.parent_part_id, ph.level - 1 FROM Parts p
JOIN PartHierarchy ph ON p.id = ph.parent_part_id
)
SELECT * FROM PartHierarchy;

C. WITH RECURSIVE PartHierarchy AS (
SELECT id, name, parent_part_id, 0 AS level FROM Parts WHERE id = 10
UNION ALL
SELECT p.id, p.name, p.parent_part_id, ph.level + 1 FROM Parts p
JOIN PartHierarchy ph ON p.parent_part_id = ph.id
)
SELECT * FROM PartHierarchy;

D. WITH RECURSIVE PartHierarchy AS (
SELECT id, name, parent_part_id, 0 AS level FROM Parts WHERE id = 10
UNION ALL
SELECT p.id, p.name, p.parent_part_id, ph.level + 1 FROM Parts p
JOIN PartHierarchy ph ON p.id = ph.parent_part_id
)
SELECT * FROM PartHierarchy;

Solution

1. Step 1: Identify the base case and level

   The base query selects the root part with id=10 and sets level to 0.

2. Step 2: Check the recursive join and level increment

   The recursive part joins parts where parent_part_id matches current part's id, and increments level by 1 to track depth.

3. Step 3: Verify options

   WITH RECURSIVE PartHierarchy AS ( SELECT id, name, parent_part_id, 0 AS level FROM Parts WHERE id = 10 UNION ALL SELECT p.id, p.name, p.parent_part_id, ph.level + 1 FROM Parts p JOIN PartHierarchy ph ON p.parent_part_id = ph.id ) SELECT * FROM PartHierarchy; correctly implements this logic. WITH RECURSIVE PartHierarchy AS ( SELECT id, name, parent_part_id, 1 AS level FROM Parts WHERE id = 10 UNION ALL SELECT p.id, p.name, p.parent_part_id, ph.level - 1 FROM Parts p JOIN PartHierarchy ph ON p.id = ph.parent_part_id ) SELECT * FROM PartHierarchy; has wrong join direction and decrements level. WITH RECURSIVE PartHierarchy AS ( SELECT id, name, parent_part_id FROM Parts WHERE parent_part_id IS NULL UNION ALL SELECT p.id, p.name, p.parent_part_id FROM Parts p JOIN PartHierarchy ph ON p.id = ph.parent_part_id ) SELECT * FROM PartHierarchy WHERE id = 10; starts from root parts but filters later, missing the correct root. WITH RECURSIVE PartHierarchy AS ( SELECT id, name, parent_part_id, 0 AS level FROM Parts WHERE id = 10 UNION ALL SELECT p.id, p.name, p.parent_part_id, ph.level + 1 FROM Parts p JOIN PartHierarchy ph ON p.id = ph.parent_part_id ) SELECT * FROM PartHierarchy; has wrong join direction.

4. Final Answer:

   The query with base WHERE id=10, level 0, JOIN p.parent_part_id=ph.id, level+1 -> Option C

5. Quick Check:

   Join on child.parent_part_id = parent.id, level + 1 [OK]

Hint: Join child.parent_part_id = parent.id and increment level [OK]

Common Mistakes:

• Reversing join condition (parent to child)
• Starting level at 1 or decrementing level
• Filtering after recursion instead of base case
• Using wrong root selection