Set Operations — UNION, INTERSECT & EXCEPT

⭕ Introduction: The Venn Diagram Ice Cream Club

Imagine you and your best friend are organizing an ice cream party:

• Your Favorite Flavors (Set A): Vanilla, Chocolate, Strawberry.
• Friend's Favorite Flavors (Set B): Chocolate, Mint, Cookie Dough.

To merge your lists, you use four sorting rings:

1. The Combined Menu (UNION): You merge both lists, but you don't list Chocolate twice. (Result: Vanilla, Chocolate, Strawberry, Mint, Cookie Dough).
2. The Double Menu (UNION ALL): You merge both lists, keeping duplicates. (Result: Chocolate listed twice).
3. The Shared Match (INTERSECT): You only list flavors you both agree on. (Result: Chocolate).
4. Your Exclusive List (EXCEPT): You list flavors you like but your friend doesn't. (Result: Vanilla, Strawberry).

In SQL, these are Set Operations! They let you combine the rows of two separate SELECT queries vertically.

---

🛠️ The Four Set Operations

For Set Operations to work, both queries must have:

1. The same number of columns.
2. Compatible data types in the same order.

UNION (Deduplicated) UNION ALL (Duplicates Kept) +---------+ +---------+ +---------+ +---------+ | Table A | + | Table B | | Table A | + | Table B | +---------+ +---------+ +---------+ +---------+ | Apple | | Apple | | Apple | | Apple | | Orange | | Banana | | Orange | | Banana | +---------+ +---------+ +---------+ +---------+ | | v v [Apple, Orange, Banana] [Apple, Orange, Apple, Banana]

• UNION: Combines rows from both queries and removes duplicates.
• UNION ALL: Combines rows from both queries, keeping duplicates. (This is much faster because the database doesn't have to sort and remove duplicates!).
• INTERSECT: Returns only rows that exist in both query results.
• EXCEPT (or MINUS in Oracle): Returns rows from the first query that do not exist in the second query.

---

💻 Code Examples

Let's combine lists of students from two different clubs.

SQL Set Queries

Multi-Language Execution

---

⚠️ Common Mistakes

1. Mismatched Column Counts

Running SELECT name FROM listA UNION SELECT name, email FROM listB. This will fail because the database engine cannot align a 1-column query on top of a 2-column query!

2. Confusing UNION vs. JOIN

Remember: JOINs expand tables horizontally (adding columns from other tables side-by-side). UNIONs expand tables vertically (stacking rows from different queries on top of each other).

---

🔍 Interview Corner

Q1: What is the difference between UNION and UNION ALL?

• UNION merges the results of two queries, sorts the merged set, and deletes any duplicate rows. This sorting action uses CPU and memory.
• UNION ALL simply stacks the results of both queries directly without sorting or deduplication. It is much faster and should be preferred unless deduplication is required.

Q2: How does the EXCEPT operator work?

The EXCEPT operator (known as MINUS in Oracle) returns all unique rows from the first query that are not present in the results of the second query.

---

📝 Summary

• Set operations combine rows from separate queries vertically.
• UNION: Merges lists and removes duplicates.
• UNION ALL: Merges lists, keeping duplicates (fastest).
• INTERSECT: Returns matching rows only.
• EXCEPT: Returns rows from list A that don't exist in list B.