What is the difference between UNION and UNION ALL?
This question tests knowledge of set operations and performance implications. It evaluates optimization awareness.
Why Interviewers Ask This
Interviewers check if you understand the cost of removing duplicates. UNION_ALL is often faster. Knowing when to use which demonstrates practical optimization skills.
How to Answer This Question
State that UNION removes duplicates and sorts, adding overhead. UNION ALL keeps duplicates and simply appends, making it faster. Suggest using UNION ALL unless distinctness is strictly required.
Key Points to Cover
- UNION removes duplicates
- UNION ALL keeps duplicates
- UNION is slower due to sorting
- Performance trade-off
Sample Answer
UNION combines results and removes duplicates, performing a distinct operation which adds sorting work and slows execution. UNION ALL keeps all duplicates and usually runs faster because it simply appends result sets. I prefer UNION ALL unless I specifically need to eliminate duplicate rows.
Common Mistakes to Avoid
- Assuming UNION is always safer
- Not mentioning performance impact
- Confusing with INTERSECT
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
What is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is the ER model in DBMS?
Easy
FlipkartDescribe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is Join and explain its types?
Medium
FlipkartWhat is database normalization and why is it important?
Medium
Flipkart