SQL · Queries that survive scale
What is the difference between UNION and UNION ALL?
Short answer
UNION combines results and removes duplicate rows, which requires sorting or hashing. UNION ALL concatenates results and keeps duplicates, so it is usually faster.
Both operators stack the results of two queries. UNION performs a distinct operation across the combined result, while UNION ALL returns every row from both inputs.
SELECT id, email, 'customer' AS source
FROM customers
UNION ALL
SELECT id, email, 'lead' AS source
FROM leads;Rules and costs
- Both queries must return the same number of columns in the same order
- Column names come from the first query, not from later queries
- Comparable types are required; engines may coerce or reject mismatches
- UNION must detect duplicates, adding memory, sorting, or hashing
- ORDER BY applies to the final combined result, not to one branch
If the branches cannot overlap, for example a table partitioned by status or date, UNION ALL is both correct and cheaper. If duplicates must be removed, check whether the branches can be made disjoint so distinct work is unnecessary.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1When is UNION ALL semantically wrong?
- 2Where should ORDER BY go in a UNION query?
- 3How does the database remove duplicates for UNION?
- 4Can you use UNION with different column types?
Say it in the interview
UNION combines two result sets and removes duplicates, while UNION ALL keeps every row. Because duplicate removal needs sorting or hashing, UNION is usually more expensive. I default to UNION ALL when the branches are disjoint or duplicates are meaningful. I also make sure column counts, order, and types line up, and I put ORDER BY at the end of the whole statement.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.