Skip to content

SQL Set Operators (UNION, INTERSECT, EXCEPT) 🕸️

Mentor's Note: Set operators are the "Socializers" of SQL. They allow you to take results from Query A and Query B and mash them together based on mathematical rules. Think of Venn Diagrams! 💡

🌟 The Scenario: The Merged Contact List 📱

Imagine you have two separate lists of email subscribers: one from your Website and one from your Mobile App.

  1. UNION: "Combine both lists and remove anyone who is on both." (Unique list) 💎
  2. UNION ALL: "Combine both lists and keep everyone, even if they are repeated." (Fastest) ⚡
  3. INTERSECT: "Only show people who are on BOTH lists." 🤝
  4. EXCEPT / MINUS: "Show people on the Website list who are NOT on the App list." 🚫

💻 1. The UNION & UNION ALL Operators

Used to combine rows from two queries.

-- Scenario: Unique list of cities from both Tables
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- Scenario: Total list including duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

💻 2. The INTERSECT Operator 🤝

Returns only the common rows that exist in both queries.

-- Scenario: Find cities where we have both a Customer AND a Supplier
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

💻 3. The EXCEPT / MINUS Operator 🚫

Returns rows from the first query that are not present in the second.

-- Oracle uses 'MINUS'
SELECT city FROM customers
MINUS
SELECT city FROM suppliers;

-- Standard 'EXCEPT'
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

🎨 Visual Logic: Venn Diagrams

UNION:      [ ( A + B ) ]  <-- Full Combined Area
INTERSECT:  [   ( A ∩ B )   ]  <-- The Overlap Only
EXCEPT:     [ ( A ) - B ]  <-- Only the "Left" side

⚠️ The "Golden Rules" for Set Operations

To use these operators, your two queries must match: 1. Same Number of Columns: Both SELECTs must have 3 columns if the first one does. 2. Compatible Data Types: You can't match a 'Name' (String) with a 'Salary' (Number) in the same position. 3. Order: Only the last query can have an ORDER BY.

📈 Learning Path