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.

Practice

1. What is the main purpose of using a WITH RECURSIVE clause in PostgreSQL when working with hierarchical data?

easy

A. To repeatedly query related rows and build a hierarchy step-by-step

B. To create temporary tables for faster queries

C. To join two unrelated tables efficiently

D. To update multiple rows in a single statement

Solution

1. Step 1: Understand the purpose of recursive CTEs

   Recursive CTEs allow querying hierarchical data by starting with a base case and repeatedly adding related rows.

2. Step 2: Compare options with this purpose

   Only To repeatedly query related rows and build a hierarchy step-by-step describes this step-by-step building of hierarchy, while others describe unrelated SQL features.

3. Final Answer:

   To repeatedly query related rows and build a hierarchy step-by-step -> Option A

4. Quick Check:

   Recursive CTE = step-by-step hierarchy building [OK]

Hint: Recursive CTEs build hierarchies by repeating queries [OK]

Common Mistakes:

• Confusing recursive CTE with temporary tables
• Thinking recursive CTEs update data
• Assuming recursive CTEs join unrelated tables

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

easy

A. RECURSIVE WITH tree AS (SELECT ... UNION ALL SELECT ...)

B. WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...)

C. WITH tree RECURSIVE AS (SELECT ... UNION ALL SELECT ...)

D. WITH tree AS RECURSIVE (SELECT ... UNION ALL SELECT ...)

Solution

1. Step 1: Recall correct recursive CTE syntax

   The correct syntax starts with WITH RECURSIVE cte_name AS ( ... ).

2. Step 2: Match options to syntax

   Only WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...) matches this exact syntax; others misplace the keyword RECURSIVE.

3. Final Answer:

   WITH RECURSIVE tree AS (SELECT ... UNION ALL SELECT ...) -> Option B

4. Quick Check:

   WITH RECURSIVE before CTE name [OK]

Hint: Start with 'WITH RECURSIVE cte_name AS' [OK]

Common Mistakes:

• Placing RECURSIVE after CTE name
• Using RECURSIVE inside parentheses
• Omitting RECURSIVE keyword

3. Given the table employees(id, name, manager_id) with data:
1, 'Alice', NULL
2, 'Bob', 1
3, 'Carol', 2
What will the following query return?

WITH RECURSIVE emp_tree AS (SELECT id, name, manager_id FROM employees WHERE id = 1 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN emp_tree et ON e.manager_id = et.id) SELECT name FROM emp_tree ORDER BY id;

medium

A. Alice only

B. Carol only

C. Bob, Carol only

D. Alice, Bob, Carol

Solution

1. Step 1: Understand base case and recursion

   The base case selects employee with id=1 (Alice). The recursive part adds employees whose manager_id matches ids in emp_tree.

2. Step 2: Trace recursion

   First iteration adds Bob (manager_id=1), second adds Carol (manager_id=2). So all three are included.

3. Final Answer:

   Alice, Bob, Carol -> Option D

4. Quick Check:

   Recursive CTE collects all subordinates [OK]

Hint: Recursive CTE collects all descendants starting from base [OK]

Common Mistakes:

• Ignoring base case rows
• Confusing manager_id direction
• Assuming only direct reports are included

4. Consider this recursive CTE:

WITH RECURSIVE cte AS (SELECT id, parent_id FROM items WHERE parent_id IS NULL UNION ALL SELECT i.id, i.parent_id FROM items i JOIN cte ON i.parent_id = cte.id) SELECT * FROM cte;

It returns no rows. What is the most likely cause?

medium

A. The base case SELECT returns no rows because no parent_id is NULL

B. The JOIN condition is reversed and should be cte.parent_id = i.id

C. The recursive part uses UNION instead of UNION ALL

D. The CTE is missing an ORDER BY clause

Solution

1. Step 1: Check base case condition

   The base case selects rows where parent_id IS NULL. If none exist, base returns zero rows.

2. Step 2: Understand recursion depends on base

   Without base rows, recursion never starts, so no rows returned.

3. Final Answer:

   The base case SELECT returns no rows because no parent_id is NULL -> Option A

4. Quick Check:

   Base case must return rows to start recursion [OK]

Hint: Check base case returns rows before recursion [OK]

Common Mistakes:

• Assuming JOIN condition is wrong without checking data
• Thinking UNION vs UNION ALL causes no rows
• Believing ORDER BY affects recursion output

5. You have a table folders(id, name, parent_id) representing nested folders. Write a recursive CTE query to list all folders with their full path from the root, separated by '/'. Which query correctly achieves this?

hard

A. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NOT NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

B. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, '/' || name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, '/' || fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

C. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

D. WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path + '/' + f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path;

Solution

1. Step 1: Identify correct base case and path initialization

   The base case must select root folders (parent_id IS NULL) and set path to just the folder name without leading '/'. The recursive part builds path as parent_path || '/' || name.

2. Step 2: Compare options with correct logic

   The correct query uses parent_id IS NULL for base, name AS path, and fp.path || '/' || f.name for recursion (PostgreSQL string concat). Distractors fail by using invalid + concat, adding extra '/' (causing '//'), or starting from non-roots (parent_id IS NOT NULL).

3. Final Answer:

   WITH RECURSIVE folder_paths AS (SELECT id, name, parent_id, name AS path FROM folders WHERE parent_id IS NULL UNION ALL SELECT f.id, f.name, f.parent_id, fp.path || '/' || f.name FROM folders f JOIN folder_paths fp ON f.parent_id = fp.id) SELECT id, path FROM folder_paths ORDER BY path; -> Option C

4. Quick Check:

   Use base root rows + '||' concat for paths [OK]

Hint: Use '||' for string concat and start from root folders [OK]

Common Mistakes:

• Using '+' instead of '||' for string concatenation
• Starting recursion from non-root folders
• Adding extra '/' at path start