Q: Two halves joined by UNION ALL

A recursive CTE has two parts joined by UNION ALL. The anchor member runs once and seeds the initial result set. The recursive member references the CTE itself and runs repeatedly, adding rows each iteration. Recursion stops when the recursive member returns no new rows. Think of it like a loop with a base case - the anchor is the starting value, the recursive member is the loop body.

Q: What makes a CTE recursive at all

The defining characteristic is self-reference: the recursive member's FROM clause names the CTE itself. A regular CTE is just a named subquery - it runs once and produces a fixed result. The RECURSIVE keyword in WITH RECURSIVE is required in PostgreSQL to enable self-reference; without it, the database treats the CTE as a regular named subquery and raises an error if you try to reference it within itself.

Q: What actually stops the recursion

Recursion stops naturally when the recursive member finds no new rows to add. For a hierarchy traversal, that happens when there are no more children. For a sequence, that happens when the WHERE condition fails. There is no automatic limit - if you have a cycle in your data and no cycle detection, the query will run until it hits the database's max recursion depth (PostgreSQL defaults to 1000 iterations) and then error.

Q: When recursion beats a regular join

Recursive CTEs shine when the number of levels is not known in advance. A JOIN can handle one level, a self-join can handle two, but an arbitrarily deep hierarchy requires recursion. Other classic use cases include generating date series, computing transitive closures in graphs, and building bill-of-materials explosions. The other options are all better handled by simpler, non-recursive SQL.

Q: UNION vs UNION ALL in recursive CTEs

The SQL standard mandates UNION ALL between the anchor and recursive member. UNION would deduplicate the working table on every iteration, which is expensive and changes the semantics. If you need to prevent infinite loops caused by cycles in your data, add a visited array or a depth limit rather than relying on UNION deduplication.

Q: Tracking depth

The anchor selects Alice (the root, no manager) at depth 0. The recursive member finds Bob (manager_id = 1, Alice's id) and assigns depth 1. Next iteration finds Carol (manager_id = 2, Bob's id) at depth 2. Carrying a depth counter is the standard technique for knowing how many levels deep you are in the hierarchy. This is useful for indenting org charts or enforcing nesting limits.

Q: Walking an org hierarchy

The anchor selects the root employee (id = 1). The recursive member joins employees to the CTE - each iteration finds employees whose manager_id matches any id already in the result set. This walks down the tree level by level until no new reports are found. The final WHERE id != 1 excludes the root from the output. A simple WHERE manager_id = 1 would only return direct reports, missing everyone further down.

Q: Building a path string

The standard technique is to add a path column to the anchor (just the root's name) and concatenate in the recursive member. Each iteration appends the current node's name to the accumulated path. The result is a full ancestry string for every node in the tree without any post-processing. This same pattern works for building arrays of ancestors using array_append in PostgreSQL.

Q: Generating a date series

The anchor produces Jan 1. The recursive member adds one day each iteration while d < Jan 5. So iterations produce Jan 2, Jan 3, Jan 4, Jan 5. When d = Jan 5, the condition d < Jan 5 is false and recursion stops. The result set contains Jan 1 through Jan 5: five rows total. Generating date series this way is useful when you need a row for every date in a range, even dates with no activity in your data.

Question 1

Generating a number sequence

Accepted Answer

The anchor produces n=1. The recursive member adds n+1 on each iteration while n < 4. So the iterations produce n=2, n=3, n=4. When n=4, the condition n < 4 is false and the recursion stops. The final result is 1, 2, 3, 4. This pattern - generating sequences without a table - is one of the most common uses of recursive CTEs.

Question 2

Two halves joined by UNION ALL

Accepted Answer

A recursive CTE has two parts joined by UNION ALL. The anchor member runs once and seeds the initial result set. The recursive member references the CTE itself and runs repeatedly, adding rows each iteration. Recursion stops when the recursive member returns no new rows. Think of it like a loop with a base case - the anchor is the starting value, the recursive member is the loop body.

Question 3

What makes a CTE recursive at all

Accepted Answer

The defining characteristic is self-reference: the recursive member's FROM clause names the CTE itself. A regular CTE is just a named subquery - it runs once and produces a fixed result. The RECURSIVE keyword in WITH RECURSIVE is required in PostgreSQL to enable self-reference; without it, the database treats the CTE as a regular named subquery and raises an error if you try to reference it within itself.

Question 4

What actually stops the recursion

Accepted Answer

Recursion stops naturally when the recursive member finds no new rows to add. For a hierarchy traversal, that happens when there are no more children. For a sequence, that happens when the WHERE condition fails. There is no automatic limit - if you have a cycle in your data and no cycle detection, the query will run until it hits the database's max recursion depth (PostgreSQL defaults to 1000 iterations) and then error.

Question 5

When recursion beats a regular join

Accepted Answer

Recursive CTEs shine when the number of levels is not known in advance. A JOIN can handle one level, a self-join can handle two, but an arbitrarily deep hierarchy requires recursion. Other classic use cases include generating date series, computing transitive closures in graphs, and building bill-of-materials explosions. The other options are all better handled by simpler, non-recursive SQL.

Question 6

UNION vs UNION ALL in recursive CTEs

Accepted Answer

The SQL standard mandates UNION ALL between the anchor and recursive member. UNION would deduplicate the working table on every iteration, which is expensive and changes the semantics. If you need to prevent infinite loops caused by cycles in your data, add a visited array or a depth limit rather than relying on UNION deduplication.

Question 7

Tracking depth

Accepted Answer

The anchor selects Alice (the root, no manager) at depth 0. The recursive member finds Bob (manager_id = 1, Alice's id) and assigns depth 1. Next iteration finds Carol (manager_id = 2, Bob's id) at depth 2. Carrying a depth counter is the standard technique for knowing how many levels deep you are in the hierarchy. This is useful for indenting org charts or enforcing nesting limits.

Question 8

Walking an org hierarchy

Accepted Answer

The anchor selects the root employee (id = 1). The recursive member joins employees to the CTE - each iteration finds employees whose manager_id matches any id already in the result set. This walks down the tree level by level until no new reports are found. The final WHERE id != 1 excludes the root from the output. A simple WHERE manager_id = 1 would only return direct reports, missing everyone further down.

Question 9

Building a path string

Accepted Answer

The standard technique is to add a path column to the anchor (just the root's name) and concatenate in the recursive member. Each iteration appends the current node's name to the accumulated path. The result is a full ancestry string for every node in the tree without any post-processing. This same pattern works for building arrays of ancestors using array_append in PostgreSQL.

Question 10

Generating a date series

Accepted Answer

The anchor produces Jan 1. The recursive member adds one day each iteration while d < Jan 5. So iterations produce Jan 2, Jan 3, Jan 4, Jan 5. When d = Jan 5, the condition d < Jan 5 is false and recursion stops. The result set contains Jan 1 through Jan 5: five rows total. Generating date series this way is useful when you need a row for every date in a range, even dates with no activity in your data.

Question 11

Preventing infinite loops

Accepted Answer

The standard approach is to carry an array of visited node IDs. In PostgreSQL: anchor starts with ARRAY[id] as visited, recursive member does array_append(visited, e.id) and adds WHERE NOT (e.id = ANY(visited)). This prevents revisiting a node you have already seen. UNION instead of UNION ALL would not help - deduplication removes duplicate output rows but does not stop the recursion from following cycles.

Question 12

Fibonacci sequence

Accepted Answer

The anchor starts with (a=0, b=1). Each recursive step shifts: new a = old b, new b = old a + old b. Iterations produce (0,1), (1,1), (1,2), (2,3), (3,5), (5,8), (8,13). At (8,13), b=13 >= 10 so recursion stops. SELECT a gives 0, 1, 1, 2, 3, 5, 8. The first 5 are 0, 1, 1, 2, 3. Selecting b instead of a shifts the sequence by one position and gives 1, 1, 2, 3, 5. The values 1, 2, 3, 5, 8 would result from selecting a+b each row rather than a.