Set operations combine the rows produced by compatible SELECT statements. Unlike a JOIN, which places related columns side by side, UNION and related operators stack result rows vertically. Interview questions often test duplicate behavior, compatible columns, operator support and where ORDER BY belongs.
Key Takeaways at a Glance:
- •UNION combines the results of two queries and removes duplicate rows; UNION ALL combines results while preserving all duplicates.
- •UNION ALL is significantly faster than UNION because it skips the expensive sorting and deduplication phase.
- •Column compatibility: Queries in a set operation must have the same number of columns with compatible data types in identical order.
- •INTERSECT returns rows present in both result sets; EXCEPT / MINUS returns rows in the first query but not in the second.
In Simple Words (The Mental Model)
While JOINs combine tables horizontally (side-by-side, adding columns), set operations combine queries vertically (stacking rows on top of each other). UNION stacks lists together and wipes out duplicates. UNION ALL just dumps everything together as-is without checking for duplicates.
Real-World Analogy
Think of combining two guest lists on paper. List 1 is the Bride's guests (50 names); List 2 is the Groom's guests (50 names). UNION ALL staples the two lists together into 100 lines. UNION reads through both lists with a highlighter, crosses out mutual friends so they only get one invitation, and produces 85 unique names.