What is the difference between UNION and UNION ALL?
SQLLevel: FoundationFundamentals (Easy)
What is the difference between UNION and UNION ALL?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
UNION combines result sets and removes duplicates. UNION ALL combines them and keeps everything, including duplicates. UNION ALL is faster because it skips the deduplication step.
-- Removes duplicates (sorts/hashes the result)
SELECT email FROM users_2023
UNION
SELECT email FROM users_2024;
-- Keeps all rows, including duplicates
SELECT email FROM users_2023
UNION ALL
SELECT email FROM users_2024;
If you know there are no duplicates (e.g., the tables are from different time periods with no overlap), always use UNION ALL.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT