What's the Difference Between SQL's UNION and UNION ALL?

The difference between UNION and UNION ALL in SQL

Both the UNION and UNION ALL operators in SQL are used to combine the results of multiple queries. However, the key difference between the two is the way they handle duplicate rows.

UNION: eliminate duplicate rows

UNION is used to merge multiple query results and eliminate any duplicate rows. It compares rows based on column values ​​and only returns different rows. This means that if a row has the same value in all columns, it will only appear once in the combined result.

UNION ALL: Keep all rows

In contrast, UNION ALL does not remove duplicate rows. It simply concatenates the results of the query, including any duplicates. This is useful when you want to retain all rows from various queries regardless of whether their column values ​​match.

Performance impact

Using UNION instead of UNION ALL incurs a performance overhead. This is because the database must perform additional work to identify and eliminate duplicate rows. Therefore, UNION ALL is usually faster than UNION, but it may not always be ideal when duplicates need to be removed.

Example: Identify duplicate rows

To determine if rows are duplicates, not only must they be of the same type, but they must also be compatible. Different SQL systems may handle this compatibility differently. For example, some systems may truncate long text columns for comparison, while other systems may reject comparisons between binary columns.

UNION Demo:

The following example combines two queries, each returning the same value 'foo' for the 'bar' column:

SELECT 'foo' AS bar
UNION
SELECT 'foo' AS bar

Result:

<code>+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)</code>

As expected, UNION removes the duplicate rows, resulting in only one row containing 'foo'.

UNION ALL Demo:

In contrast, UNION ALL does not remove duplicates:

SELECT 'foo' AS bar
UNION ALL
SELECT 'foo' AS bar

Result:

<code>+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)</code>

There are two rows in the combined result, including duplicates.

The above is the detailed content of What's the Difference Between SQL's UNION and UNION ALL?. For more information, please follow other related articles on the PHP Chinese website!