UNION vs UNION ALL in recursive CTEs

Question

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.