Search⌘ K
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Recursive CTEs with UNION vs. UNION ALL

Learn to differentiate between UNION and UNION ALL in recursive CTEs, managing duplicate rows effectively during SQL recursion. Understand their impact on graph traversal scenarios and counting visited nodes, enhancing your ability to write optimized recursive queries.

We'll cover the following...

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:

MySQL
-- Merging the values `1`, `2`, `2`, and `3` with duplicate elimination.
SELECT 1 AS `Number`
UNION
SELECT 2
UNION
SELECT 2
UNION
SELECT 3;
-- Insert empty lines in the output for better readability.
SELECT "";
-- Merging the values `1`, `2`, `2`, and `3` *without* duplicate elimination.
SELECT 1 AS `Number`
UNION ALL
SELECT 2
UNION ALL
SELECT 2
UNION ALL
SELECT 3;
-- Insert empty lines in the output for better readability.
SELECT "";
-- Merging the values `1`, `2`, `2`, and `3` with mixed duplicate elimination strategies.
SELECT 1 AS `Number`
UNION
SELECT 2
UNION ALL
SELECT 2
UNION
SELECT 3;

We can distinguish these examples into three categories:

  • This section shows how to merge values with automatic duplicate elimination. Each SELECT statement selects a ...