What is the difference between UNION and UNION ALL?
UNION combines the results of two queries and removes duplicate rows, which requires extra sorting work. UNION ALL combines results and keeps all rows including duplicates, so it is faster. Use UNION when you need unique rows and UNION ALL when duplicates are fine or impossible, because it performs better.
- UNION combines results from two queries and removes duplicate rows.
- UNION ALL combines results and keeps duplicates, so it is faster.
- Both require the queries to have the same number and compatible types of columns.
The core difference
Both stack the results of two queries on top of each other. UNION then does the extra work of removing duplicates, while UNION ALL skips that step and returns everything.
SELECT city FROM customers
UNION -- unique cities only
SELECT city FROM suppliers;
SELECT city FROM customers
UNION ALL -- keeps duplicates, faster
SELECT city FROM suppliers;For both, the two queries must return the same number of columns with compatible types.
A smart line is prefer UNION ALL when you know duplicates cannot occur, because it avoids the sort and is faster. Showing you think about performance, not just correctness, stands out.
Frequently asked questions
When should you use UNION ALL over UNION?
Use UNION ALL when you know there are no duplicates or you want to keep them, since skipping the duplicate check makes it noticeably faster.
Why must columns match for a union?
The results are stacked into one set, so each query must return the same number of columns with compatible data types in the same order.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions