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

Concept Flow - Recursive CTE for hierarchical data

Start with anchor query

↓

Anchor rows selected

↓

Recursive query references CTE

↓

Join with base table to find children

↓

Add new rows to CTE result

↓

Check if new rows found

↓

Repeat

↓

Final hierarchical result

The recursive CTE starts with base rows, then repeatedly finds child rows by joining with the base table until no more children are found, building the hierarchy step-by-step.

Execution Sample

PostgreSQL

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

This query finds all employees under manager with id=1, showing each level in the hierarchy.

Execution Table

Step	Action	Rows Selected	New Rows Added	Level Values	Notes
1	Anchor query: select employee with id=1	[{id:1, name:'Alice', manager_id:null, level:1}]	1	[1]	Start with manager Alice
2	Recursive query: find employees with manager_id=1	[{id:2, name:'Bob', manager_id:1, level:2}]	1	[1,2]	Bob reports to Alice
3	Recursive query: find employees with manager_id=2	[{id:4, name:'Diana', manager_id:2, level:3}, {id:5, name:'Evan', manager_id:2, level:3}]	2	[1,2,3]	Diana and Evan report to Bob
4	Recursive query: find employees with manager_id=4	[{id:6, name:'Fiona', manager_id:4, level:4}]	1	[1,2,3,4]	Fiona reports to Diana
5	Recursive query: find employees with manager_id=5	[]	0	[1,2,3,4]	No employees report to Evan
6	Recursive query: find employees with manager_id=6	[]	0	[1,2,3,4]	No employees report to Fiona
7	No new rows found, recursion ends	[]	0	[1,2,3,4]	Final hierarchical data collected

💡 Recursion stops when no new child rows are found in step 5 and 6.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
subordinates_rows	[]	[{id:1, level:1}]	[{id:1, level:1}, {id:2, level:2}]	[{id:1, level:1}, {id:2, level:2}, {id:4, level:3}, {id:5, level:3}]	[{id:1, level:1}, {id:2, level:2}, {id:4, level:3}, {id:5, level:3}, {id:6, level:4}]	[same as previous]	[same as previous]
new_rows	[]	[{id:1}]	[{id:2}]	[{id:4}, {id:5}]	[{id:6}]	[]	[]

Key Moments - 3 Insights

Why does the recursive query join the CTE with the base table?

Because the recursive part uses the current CTE rows to find the next level of children by joining on manager_id = id, as shown in steps 2-4 of the execution_table.

What stops the recursion from running forever?

When no new rows are added in a recursive step (steps 5 and 6), the recursion ends automatically, preventing infinite loops.

Why do we add a 'level' column in the query?

The 'level' helps track how deep each employee is in the hierarchy, increasing by 1 each recursion, as seen in the 'Level Values' column.

Visual Quiz - 3 Questions

Look at the execution_table, what is the 'level' of employee 'Diana' when she is added?

A 2

B 4

C 3

D 1

💡 Hint

Check step 3 in the execution_table where Diana is added with level 3.

At which step does the recursion stop adding new rows?

A Step 5

B Step 4

C Step 2

D Step 1

💡 Hint

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

If the anchor query selected two employees instead of one, how would the variable 'subordinates_rows' change after step 1?

A It would remain empty

B It would have two rows instead of one

C It would have one row with combined data

D It would cause an error

💡 Hint

Refer to variable_tracker 'subordinates_rows' after step 1 showing rows collected.

Concept Snapshot

WITH RECURSIVE cte_name AS (
  -- Anchor query: base rows
  SELECT ...
  UNION ALL
  -- Recursive query: join cte with base table
  SELECT ... FROM base_table JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Use recursive CTEs to build hierarchical data by repeatedly joining child rows until no more are found.

Full Transcript

A recursive CTE starts by selecting base rows (anchor query). Then it repeatedly joins the CTE with the base table to find child rows, adding them to the result. This repeats until no new rows are found, building a hierarchy step-by-step. The example query finds all employees under a manager by joining on manager_id. The 'level' column tracks depth. Recursion stops automatically when no new children exist. This method is useful for hierarchical data like organizational charts or folder trees.