In WITH RECURSIVE, what changes if the members are combined with UNION instead of UNION ALL?

## The choice The anchor and recursive members are joined by a set operator, and the standard allows either `UNION ALL` or `UNION`. Most real recursive CTEs use `UNION ALL`; `UNION` is the one lever inside the construct itself that changes when iteration stops. ## What UNION removes `UNION` in a recursive CTE eliminates duplicates against the rows already produced: a row the recursive member emits that matches a row already in the result is discarded rather than appended. Because the working set for the next pass is exactly what was appended, a discarded row is also never re-expanded. `UNION ALL` skips that check entirely — every emitted row is appended and every appended row seeds another pass. ## Effect on termination Iteration ends when a pass contributes nothing. Under `UNION ALL` that means the recursive member must genuinely find no rows: a counter crossed its bound, or a join found no matching rows. Under `UNION` a pass also contributes nothing when everything it produced was already seen. So on data where the same row is reachable by several different routes, `UNION` converges on its own while `UNION ALL` keeps re-expanding the same rows — in the merely-multi-path case producing an explosion of duplicate work, and where the data loops back on itself, never stopping at all. ```sql -- self-limiting: a node already reached is not expanded again WITH RECURSIVE reachable(node) AS ( SELECT 'a' UNION SELECT e.dst FROM edges e JOIN reachable r ON e.src = r.node ) SELECT node FROM reachable; ``` ## The catch: the dedup key is the whole row Duplicate elimination compares every column of the row, not the column you consider the identity. Add a depth counter, a running path string, or a per-branch cost, and two visits to the same underlying entity differ in at least one column, so neither is a duplicate of the other and the loop is unbounded again: ```sql -- NOT self-limiting any more: depth makes every row distinct WITH RECURSIVE walk(node, depth) AS ( SELECT 'a', 0 UNION SELECT e.dst, w.depth + 1 FROM edges e JOIN walk w ON e.src = w.node ) SELECT node, depth FROM walk; ``` This is the single most common way candidates get burned: they add the depth column for reporting, keep the `UNION`, and are surprised that a query which used to finish now does not. If you need those extra columns, put an explicit depth bound in the recursive member and treat `UNION` as a bonus rather than the guard. ## Cost Duplicate elimination is not free: the engine has to compare each newly produced row against everything already produced, pass after pass. On a tree, where every row is reachable exactly once anyway, that work buys nothing, and `UNION ALL` is strictly the better choice. On a densely connected graph the saved re-expansion usually dwarfs the comparison cost. The rule of thumb: tree-shaped data with a clear stop condition takes `UNION ALL`; graph-shaped data where rows can be revisited takes `UNION`, a depth bound, or both. ## Portability The standard permits both operators between the two members, but implementations vary — some accept only `UNION ALL` in a recursive CTE and reject `UNION` outright, and duplicate-elimination semantics for rows containing NULLs follow each engine's ordinary set-operation rules. Verify against your engine's documentation before writing a query whose termination depends on `UNION`. ## Answering the question A complete answer names three things: `UNION` removes rows already produced so they never seed another pass; that changes the stop condition from "no rows" to "no new rows"; and the whole row is the comparison key, so extra columns silently disable the effect.