Set Operations

🎭 SQL Set Operations: Mixing & Matching Your Data Like a DJ

The Big Idea: Combining Playlists

Imagine you have two music playlists. One is your “Morning Vibes” playlist, and another is your “Workout Jams” playlist. Sometimes you want to:

• Combine them into one mega-playlist
• Find songs that appear in both (your all-time favorites!)
• Find songs unique to one playlist (songs you only play in the morning)

That’s exactly what SQL Set Operations do—but with database tables instead of songs! 🎵

🧩 What Are Set Operations?

Set operations let you combine results from two or more SELECT queries into a single result. Think of each SELECT query as a separate list, and set operations as the rules for merging those lists.

The Golden Rule

Both SELECT queries must have:

• ✅ Same number of columns
• ✅ Compatible data types in matching positions

🔗 UNION: “Give Me Everything!”

What It Does

UNION combines two result sets and removes duplicates automatically.

The Analogy

You’re making a guest list for a party. You have two lists:

• List A: Friends from school
• List B: Friends from work

UNION gives you everyone from both lists, but if someone appears on both lists, their name only shows up once. No duplicate invitations! 🎉

Example

SELECT name FROM morning_playlist
UNION
SELECT name FROM workout_playlist;

morning_playlist:

workout_playlist:

Result (UNION):

Notice: Song B and Song C appeared in both, but only show once!

📋 UNION ALL: “Give Me EVERYTHING, Including Copies!”

What It Does

UNION ALL combines two result sets but keeps all duplicates.

The Analogy

Same party, but now you’re counting RSVPs. If someone is on both lists, you count them twice—maybe they confirmed twice! You want the complete, unfiltered picture.

Example

SELECT name FROM morning_playlist
UNION ALL
SELECT name FROM workout_playlist;

Result (UNION ALL):

Song B and Song C appear twice because they exist in both playlists!

💡 When to Use What?

• UNION: When duplicates are noise (unique customers, unique products)
• UNION ALL: When duplicates matter (counting totals, complete logs)

UNION ALL is also faster because it skips the duplicate-checking step!

🎯 INTERSECT: “What Do We Have in Common?”

What It Does

INTERSECT returns only rows that appear in BOTH result sets.

The Analogy

You and your friend are deciding what to watch. You each have a list of movies you like. INTERSECT finds movies that are on both lists—your overlap, your common ground! 🤝

Example

SELECT name FROM morning_playlist
INTERSECT
SELECT name FROM workout_playlist;

Result (INTERSECT):

Only Song B and Song C appear in both playlists!

Real-World Use

• Find customers who bought from both Store A AND Store B
• Find employees who worked on Project X AND Project Y
• Find products available in both warehouses

❌ EXCEPT: “What’s Unique to Me?”

What It Does

EXCEPT returns rows from the first query that DON’T appear in the second query. It’s subtraction!

The Analogy

You’re checking what’s special about your playlist. What songs do YOU have that your friend doesn’t? EXCEPT removes the overlap and shows you what’s exclusively yours. 🌟

Example

SELECT name FROM morning_playlist
EXCEPT
SELECT name FROM workout_playlist;

Result (EXCEPT):

Song A is only in morning_playlist—the others are in both!

🔄 Order Matters!

SELECT name FROM workout_playlist
EXCEPT
SELECT name FROM morning_playlist;

Result:

Now we get Song D—unique to workout_playlist!

📏 Set Operations Rules

Rule 1: Same Number of Columns

-- ❌ WRONG: Different column counts
SELECT name, price FROM products
UNION
SELECT name FROM services;

-- ✅ CORRECT: Same column count
SELECT name, price FROM products
UNION
SELECT name, cost FROM services;

Rule 2: Compatible Data Types

Columns must match in position and have compatible types.

-- ❌ WRONG: Text can't combine with number
SELECT name FROM employees
UNION
SELECT salary FROM employees;

-- ✅ CORRECT: Both are text
SELECT first_name FROM employees
UNION
SELECT last_name FROM employees;

Rule 3: Column Names Come from First Query

SELECT first_name AS person
FROM employees
UNION
SELECT product_name AS item
FROM products;

The result column is named “person” (from the first SELECT).

Rule 4: ORDER BY Goes at the End

SELECT name FROM customers
WHERE country = 'USA'
UNION
SELECT name FROM suppliers
WHERE country = 'USA'
ORDER BY name;  -- Sorts the final combined result

Rule 5: You Can Chain Operations

SELECT name FROM list_a
UNION
SELECT name FROM list_b
UNION
SELECT name FROM list_c;

This combines all three lists, removing duplicates across all!

🎨 Visual Summary

graph TD
    A["Table A"] --> U{UNION}
    B["Table B"] --> U
    U --> UA["All unique rows from A + B"]

    C["Table A"] --> UA2{UNION ALL}
    D["Table B"] --> UA2
    UA2 --> UB["All rows including duplicates"]

    E["Table A"] --> I{INTERSECT}
    F["Table B"] --> I
    I --> IC["Only rows in BOTH"]

    G["Table A"] --> EX{EXCEPT}
    H["Table B"] --> EX
    EX --> ED["Rows in A but NOT in B"]

🧪 Quick Comparison Table

💡 Pro Tips

1. Performance: UNION ALL is faster than UNION (no duplicate removal)
2. NULL handling: NULLs are considered equal in set operations
3. Order: EXCEPT and INTERSECT care about which table is first
4. Debugging: Test each SELECT separately before combining

🎬 Putting It All Together

Imagine an online store with two tables:

• online_customers: People who bought online
• store_customers: People who bought in-store

-- Everyone who ever bought anything
SELECT email FROM online_customers
UNION
SELECT email FROM store_customers;

-- Total transactions (with duplicates)
SELECT email FROM online_customers
UNION ALL
SELECT email FROM store_customers;

-- Loyal customers (both channels)
SELECT email FROM online_customers
INTERSECT
SELECT email FROM store_customers;

-- Online-only customers
SELECT email FROM online_customers
EXCEPT
SELECT email FROM store_customers;

🏆 You’ve Got This!

Set operations are your data combination toolkit:

• UNION = Merge & dedupe
• UNION ALL = Merge & keep all
• INTERSECT = Find overlap
• EXCEPT = Find differences

Remember the playlist analogy, and you’ll never forget which operation to use! 🎵✨