Mastering Advanced SQL Techniques with MySQL/

...

/

Recursive CTEs with UNION vs. UNION ALL

Recursive CTEs with UNION vs. UNION ALL

Explore the different variants of recursive common table expressions in MySQL.

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:

Press + to interact
-- 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 ...