SQL UNION vs UNION ALL Differences

SQL UNION vs UNION ALL Differences

UNION and UNION ALL combine the result sets of two or more SELECT statements into a single result set. The key difference lies in how they handle duplicate rows.

UNION (Removes Duplicates)

SELECT name, email FROM customers
UNION
SELECT name, email FROM leads;

UNION performs a distinct operation on the combined result, removing duplicate rows. This requires sorting or hashing the entire result set, which adds overhead.

UNION ALL (Keeps Duplicates)

SELECT name, email FROM customers
UNION ALL
SELECT name, email FROM leads;

UNION ALL simply concatenates the result sets without checking for duplicates. It is faster because it skips the deduplication step.

When to Use Each

Use UNION ALL when:

• You know there are no duplicates between the sets
• Duplicates are acceptable or expected
• Performance is critical (UNION ALL is significantly faster on large datasets)
• Combining results from partitioned tables

Use UNION when:

• You need guaranteed unique rows in the output
• Combining overlapping datasets where duplicates must be eliminated

Rules for UNION

1. All SELECT statements must have the same number of columns
2. Corresponding columns must have compatible data types
3. Column names in the result come from the first SELECT
4. ORDER BY applies to the entire combined result and goes at the end

SELECT product_name, 'In Stock' AS status FROM inventory WHERE qty > 0
UNION ALL
SELECT product_name, 'Backordered' FROM backorders
ORDER BY product_name;

Simulating FULL OUTER JOIN in MySQL

SELECT a.*, b.*
FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id
UNION
SELECT a.*, b.*
FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id;

Performance Comparison

On large datasets, UNION ALL can be orders of magnitude faster than UNION because no sort or hash operation is needed, no row comparison occurs across sets, and results can be streamed without buffering. Always default to UNION ALL unless you specifically need deduplication.