Recursive CTEs with UNION vs. UNION ALL

So far, our understanding of recursion in SQL is largely based on practical examples. However, a nuanced theoretical difference exists in the syntax of SQL’s recursion. To merge the base case result with the result of the recursion, these previous examples used UNION, equivalent to UNION DISTINCT. That is, duplicate rows are eliminated during the said merge. However, there are problems, e.g., counting traversed nodes in a graph, that we want to solve using a recursive CTE while retaining duplicates in the output. In that case, we want to eliminate duplicate elimination using UNION ALL instead of UNION [DISTINCT].

Recap of UNION variants

Before looking at variants of UNION in the context of recursion, let us look at such variants in isolation. Consider the following examples of merging literal values in SQL: