SQL Set Operators

Set operators allow you to combine the results of two queries. Unlike JOINS (which combine columns horizontally), SET operators combine results Vertically (stacking them on top of each other).

1. UNION vs UNION ALL (The Performance Choice)

UNION combines rows and removes duplicates. UNION ALL combines rows and keeps everything. Architect Tip: Always use UNION ALL unless you specifically need to hide duplicates. UNION requires an expensive 'Sort' operation to find duplicates, which can be 10x slower on large datasets.

2. INTERSECT and EXCEPT

• INTERSECT: Returns only rows that appear in BOTH queries. (The "Overlap").
• EXCEPT: Returns rows that appear in Query A but NOT in Query B. (The "Difference").

4. Interview Mastery

Q: "Why would I use EXCEPT instead of 'WHERE NOT IN' or 'NOT EXISTS'?"

Architect Answer: "`EXCEPT` handles NULLs differently and compares the *entire row*. `NOT IN` fails completely if the subquery returns even a single NULL. `EXCEPT` treats two NULLs as equal, allowing for a much safer and cleaner comparison of two similar tables (e.g., comparing a 'Backup' table with the 'Live' table to see what changed)."