Recursive CTEs - SQL

1. Ignoring row order, which employee IDs will the CTE contain?
   Answer: 1, 2, 5; employees 3 and 4 are disconnected from every anchored root. When working with recursive CTEs, the key question is always: where does the traversal begin, and what can it actually reach? The anchor clause defines your starting roots, and each recursive step can only extend from rows already in the result set — it cannot "discover" disconnected nodes on its own. Here, the anchor selects employees where manager_id IS NULL, giving you employees 1 and 5. The recursive step then finds any employee whose manager_id matches someone already collected. Employee 2 has manager_id = 1, so it joins in next. No remaining employee has manager_id equal to 2 or 5, so recursion terminates. The final set is {1, 2, 5} — making A correct. The trap in B is assuming the CTE performs a global scan and includes everyone. It doesn't — recursion only propagates along valid edges. Employee 3 has manager_id = 99, but employee 99 doesn't exist anywhere in the table, so 3 is never reachable from any anchor root. Because 3 never enters the CTE, employee 4 (whose manager is 3) also has no path in — eliminating C, which incorrectly assumes 4 can be included while 3 is skipped. You can't traverse through a node that was never collected. D is wrong because multiple NULL-rooted anchors are perfectly valid; a recursive CTE unions all anchor rows together before recursing, so both 1 and 5 are included from the start. Your study tip: always mentally trace the path from anchor to each node. If any link in the chain is broken — missing employee, wrong manager reference — the entire subtree below that break is unreachable.
2. Ignoring row order, which values does the query return?
   Answer: 2, 5, 8, 11, because the condition is tested before generating the next value. When you see a WITH RECURSIVE query, the critical skill is tracing the execution step by step, paying close attention to when the stopping condition is evaluated relative to when values are generated. Here's how this query unfolds. The anchor term produces 2. The recursive term then takes each current value, checks WHERE n < 10, and if true, generates n + 3. Starting from 2: since 2 < 10, it generates 5. Since 5 < 10, it generates 8. Since 8 < 10, it generates 11. Now the engine checks whether 11 < 10 — it's not, so recursion stops. The final result set is 2, 5, 8, 11, making C correct. The key insight: the condition WHERE n < 10 filters which existing rows are used as input for the next iteration. It does not filter the output values themselves. So 8 passes the condition, 8 + 3 = 11 is generated, and then 11 fails the condition and produces nothing further — but 11 itself is already in the result. A is wrong because it assumes values greater than 10 are excluded from the output, confusing a filter on the recursive input with a filter on the final results. B is wrong for a similar reason — 10 never appears because we jump from 8 to 11 in steps of 3. D is wrong because it imagines an extra iteration occurring after the condition fails, which doesn't happen; the failed condition simply produces no new rows. As a study tip, always trace recursive CTEs manually row by row, and remember: the WHERE clause controls what feeds the next step, not what gets returned.
3. What is the most accurate description of the query's behavior?
   Answer: It continues recursing beyond depth 3, so it does not complete unless the system imposes a limit. When working with recursive CTEs, you need to understand where filtering happens versus where recursion stops. These are two completely different things — and confusing them is exactly what this question tests. The recursive CTE here alternates between nodes 1 and 2 indefinitely: depth 0 returns node 1, depth 1 returns node 2, depth 2 returns node 1, and so on forever. The critical insight is that the WHERE depth <= 3 clause belongs to the outer query, not to the recursive definition itself. The passage explicitly states that recursive CTEs are fully evaluated before the outer filter applies — and the database doesn't detect cycles. This means the recursion has no internal termination condition and will keep generating rows without bound. The outer WHERE clause never gets a chance to stop it. D is correct: the query recurses indefinitely until the system intervenes (typically via a recursion depth limit or timeout). A is the most tempting wrong answer — it assumes the outer WHERE acts as a recursion stopper, which it does not. The filter runs after recursion completes, but recursion never completes here. B is wrong because UNION ALL explicitly keeps all rows including duplicates; it does not deduplicate like UNION. C is wrong for the same reason as A — it assumes four depths are generated and returned cleanly, but again, the outer WHERE cannot halt an infinite recursive loop. The key study takeaway: to safely limit recursion, the termination condition must live inside the recursive part of the CTE (e.g., WHERE depth < 3 in the recursive branch), not in the outer query.
4. What happens when the query is executed?
   Answer: It terminates with nodes 1, 2, and 3, because revisiting node 1 adds no new row. When you see a recursive CTE with UNION (not UNION ALL), the critical question is: how does SQL prevent infinite loops? The answer lies in duplicate elimination. UNION removes rows that already exist in the accumulated result set, so if a node is re-encountered, it produces no new row — and recursion stops when no new rows are produced. Tracing through the query: the base case seeds reach with node 1. The first recursive step finds 1 → 2, adding node 2. The second finds 2 → 3, adding node 3. The third step finds 3 → 1, but 1 already exists in reach — UNION suppresses it as a duplicate. No new row is inserted, the working table is empty, and recursion halts. The final result is {1, 2, 3}, making A correct. B describes what would happen with UNION ALL, which keeps duplicates. With UNION ALL, the cycle 1 → 2 → 3 → 1 → ... would indeed recurse forever (or until a depth limit is hit). The question explicitly states UNION removes duplicates, so B is based on a false premise. C is wrong because UNION doesn't discard rows mid-traversal based on cycles — it deduplicates after each iteration. Node 3 is a genuinely new value and gets added normally. D is a distractor suggesting UNION can't suppress later iterations. This is incorrect — UNION compares each candidate row against the entire accumulated result, regardless of which iteration produced it. Study tip: Always distinguish UNION from UNION ALL in recursive CTEs — this distinction alone determines whether a cycle causes infinite recursion or graceful termination.
5. What count does the query return?
   Answer: 2, because D is generated once through B and once through C. When working with recursive CTEs, you need to trace the execution step by step rather than assuming SQL automatically deduplicates results. A recursive CTE generates rows in successive iterations — the anchor produces the starting set, and each recursive step appends new rows based on joins against the previous iteration's output. Here's how this query executes: The anchor selects 'A', giving reach = {A}. Iteration 1 joins edges where parent = 'A', producing B and C. Iteration 2 joins edges where parent IN ('B', 'C') — both B→D and C→D match, so **two rows of 'D'** are added. Iteration 3 finds no new children of D, so recursion stops. The final reach table contains A, B, C, D, D — five rows total, with D appearing twice. The WHERE node = 'D' filter returns a count of 2, making C correct. A is wrong because recursive CTEs absolutely do traverse multiple hops — that's their entire purpose. The anchor is just the starting point, not the limit of traversal. B reflects what would happen if you used UNION instead of UNION ALL. Using UNION would deduplicate rows and yield a count of 1, but UNION ALL preserves every generated row, including duplicates. D is a miscount — the intermediate nodes A, B, and C are not counted because the filter isolates only D, and D appears exactly twice. A key pattern to remember: UNION ALL in a recursive CTE means no deduplication. If you want distinct reachable nodes, you must either use UNION or wrap the result in SELECT DISTINCT.
6. Ignoring row order, which employee IDs are returned?
   Answer: 2, 4, 5, representing the selected employee and that employee's descendants. Recursive CTEs follow a two-part pattern: an anchor (the starting row) and a recursive member (the repeated join that expands results). The key to tracing them is understanding the direction of traversal — are you walking up the hierarchy (toward managers) or down (toward subordinates)? In this query, the anchor selects employee_id = 2. The recursive member then joins employees to org on e.manager_id = o.employee_id — meaning it finds employees whose manager is already in the result set. This travels downward through the hierarchy. Starting with {2}, the next iteration finds employees whose manager is 2: that's 4 and 5. The iteration after that looks for employees whose manager is 4 or 5 — none exist. The recursion stops, returning 2, 4, 5, confirming B is correct. A is wrong because the join condition e.manager_id = o.employee_id fetches children, not parents. To retrieve manager 1, you'd need to reverse the join direction. C incorrectly includes employee 3, who reports to manager 1 — not to employee 2. Employee 3 is a sibling of 2, not a descendant. D combines the mistakes of A and C, adding both the parent (1) and sibling-branch descendant (3) while correctly including 4 and 5 — a mix of upward and sideways traversal that the query simply doesn't perform. When you encounter a recursive CTE question, immediately identify the join condition and ask: which column is the "seed" side and which is the "expansion" side? That tells you the direction of traversal and prevents confusing parent-walking with child-walking.
7. Which levels can appear in the result?
   Answer: The root, its children, and its grandchildren, but not its great-grandchildren. When working with recursive CTEs, the key is understanding exactly when the recursion stops. The WHERE clause in the recursive member controls which rows are allowed to generate new children — not which rows appear in the result. Here's how the query unfolds step by step. The anchor selects the root node at depth = 0. The recursive member then joins items whose parent is in the current set, incrementing depth by 1, but only when t.depth < 2. So rows at depth = 0 (root) can recurse and produce depth = 1 children. Rows at depth = 1 can recurse and produce depth = 2 grandchildren. Rows at depth = 2 cannot recurse further because 2 < 2 is false — but the grandchildren themselves are still added to the result. This means the final result contains depths 0, 1, and 2: the root, children, and grandchildren. That confirms B is correct. A is wrong because it misreads the condition. t.depth < 2 allows depth-1 rows to recurse, so grandchildren (depth 2) do appear. The recursion stops after producing depth 2, not before. C is wrong because the anchor row (the root at depth 0) is always included — it's the base case of the recursion and is never filtered out. D is wrong because depth-2 rows fail the t.depth < 2 check, so they cannot generate great-grandchildren (depth 3). A useful trick: mentally ask, "which existing rows are allowed to recurse?" The filter applies to the parent generating children, not to the child being added. Depth 2 rows exist in the result but are blocked from spawning further.
8. Which conclusion is valid when the query is moved to another conforming database system?
   Answer: No traversal order is guaranteed; an explicit final ORDER BY is needed for stable presentation. Whenever you see a question about recursive CTEs and result ordering, anchor yourself to one foundational SQL rule: **the SQL standard makes no guarantee about the physical order of rows returned unless an explicit ORDER BY clause is present.** This applies to regular queries, CTEs, and recursive CTEs alike. The observed breadth-first pattern in the first database is purely incidental — it reflects that system's internal implementation details (perhaps how it queues iteration results), not any requirement of the SQL standard. When you move the same query to a different conforming database, that engine is free to return rows in whatever order its optimizer chooses. Adding a depth column tracks which level a row came from, but it does nothing to enforce how those rows are sorted in the final result set. C is correct: without an explicit ORDER BY depth (or whatever column you care about), output order is undefined and unstable across systems. A is wrong because recursive iterations do not define final result ordering — SQL engines are free to materialize and reorder intermediate results however they like. The iteration logic is deterministic, but the output sequence is not guaranteed. B is wrong on two fronts: recursive CTEs don't universally use stack-based traversal, and even if one implementation did, the SQL standard doesn't mandate depth-first ordering. You're mixing implementation details with language guarantees. D is a tempting half-truth. Even anchor rows have no guaranteed position in the final result without an ORDER BY; the standard doesn't promise they appear first. Study tip: Any time a SQL question mentions ordering without an explicit ORDER BY, the answer almost always hinges on "no ordering is guaranteed." Treat apparent ordering as coincidental, never contractual.
9. If the query sums the cumulative quantities for all rows representing component D, how many units of D are required for one A?
   Answer: Eleven units, because each path's quantities are multiplied before the path totals are added. When working with recursive CTEs on hierarchical data like bills of materials, the key insight is that each path through the hierarchy is independent, and quantities multiply as you traverse edges, then the path totals sum at the end. Here's how the traversal works for component D. Starting from A (cumulative quantity = 1), the CTE explores two distinct paths to D: - Path 1: A → B → D. The cumulative quantity becomes $1 \times 2 \times 4 = 8$ units. - Path 2: A → C → D. The cumulative quantity becomes $1 \times 3 \times 1 = 3$ units. Summing both rows where the component is D gives $8 + 3 = 11$ units. That confirms B is correct. A is wrong because it adds only the direct component quantities on the edges leading into D ($4 + 1 = 5$), completely ignoring the multiplicative effect of the parent-level quantities (how many Bs and Cs are needed per A). C is wrong because 14 would result from naively adding all quantities encountered across both paths ($2 + 4 + 3 + 1 = 10$... actually no clean path to 14), reflecting a misunderstanding of how cumulative multiplication interacts with path independence. D is wrong because 24 treats both paths as a single combined multiplication ($2 \times 4 \times 3 \times 1 = 24$), conflating two separate recursive branches into one operation. A useful tip: always trace each root-to-leaf path separately in a recursive CTE, multiply quantities along each path, then aggregate across paths at the end.
10. Which path is selected from the final CTE row?
    Answer: CEO > Manager > Engineer, because each newly found manager is prepended to the existing path. When working with recursive CTEs, the key is to trace exactly how the path string gets built in each iteration — specifically, which side of the || concatenation gets the new value versus the accumulated value. The anchor row starts at employee 30 (Engineer), so path = 'Engineer' and manager_id = 20. In the first recursive step, the query finds employee 20 (Manager) because m.id = c.manager_id. It then builds: m.name || ' > ' || c.path, which is 'Manager' || ' > ' || 'Engineer' = 'Manager > Engineer'. In the second recursive step, employee 10 (CEO) is found, producing 'CEO' || ' > ' || 'Manager > Engineer' = 'CEO > Manager > Engineer'. This row has manager_id IS NULL, so it's selected — confirming D is correct. A is wrong because it describes appending (putting the new name after the existing path), which would produce Engineer > Manager > CEO. That would require c.path || ' > ' || m.name, but the query does the opposite. B is wrong because recursive CTEs don't replace anchor rows — each iteration adds new rows to the result set while referencing previous ones. The anchor row is never removed. C is wrong on two counts: it skips the middle employee entirely and reverses the order, neither of which matches the concatenation logic. As a study tip: whenever you see recursive CTE path-building, sketch out each iteration row by row and pay close attention to which operand is on the left of || — that determines whether names are prepended or appended.

Start this quiz