FresherTechnicalSQL

What is the difference between UNION and UNION ALL?

Try answering first

Model answer

Both stack the results of two queries into one. UNION removes duplicate rows from the combined result. UNION ALL keeps every row, including duplicates.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;      -- each city once

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;      -- every row from both

Removing duplicates means the database must sort or hash the whole result, which costs time and memory. UNION ALL just appends, so it is faster. Use UNION ALL by default, and UNION only when you need distinct rows and know duplicates can occur.

Both need the same number of columns in each query, with compatible types, and the column names come from the first query. ORDER BY goes once at the end.

Related set operators are INTERSECT (rows in both) and EXCEPT (rows in the first but not the second; MINUS in Oracle). MySQL supports them from 8.0.31.

Remember: UNION implies a hidden DISTINCT.

Key points

UNION removes duplicates, UNION ALL keeps them
UNION ALL is faster because it skips the dedup step
Queries need the same column count and compatible types
INTERSECT and EXCEPT are the other set operators

Likely follow-ups

How would you find rows present in one table but not the other?

Common mistakes

Using UNION when duplicates cannot occur and paying for the dedup
Placing ORDER BY inside each branch and expecting a global order