Skip to content

Oracle Set Operators 🕸️

Mentor's Note: Set operators are like "Result Math". Instead of joining tables side-by-side, we take two complete query results and stack them, overlap them, or subtract one from the other. 💡

🌟 The Scenario: The App Merger 📱

Your company has two separate apps: One for the Web and one for Mobile. - UNION: Get a list of ALL unique emails from both apps. - INTERSECT: Find users who use BOTH apps. - MINUS: Find users who use the Web app but have NOT downloaded the Mobile app.

💻 1. UNION & UNION ALL (The Stacker)

  • UNION: Combines results and removes duplicates.
  • UNION ALL: Combines results and keeps duplicates (Faster!).
SELECT email FROM web_users
UNION
SELECT email FROM mobile_users;

💻 2. INTERSECT (The Overlap) 🤝

Returns only rows that exist in both query results.

SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

💻 3. MINUS (The Subtractor) ✂️

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

SELECT product_id FROM inventory
MINUS
SELECT product_id FROM sales; -- "What hasn't sold?"

🏗️ Architect's Note: Performance & Rules 🛡️

  1. The "Union All" Rule: Always use UNION ALL instead of UNION if you know there are no duplicates. UNION forces an expensive "Sort Unique" operation.
  2. Column Rules:
  3. Both queries must have the same number of columns.
  4. The data types must be compatible.
  5. Headers are taken from the first query.

📈 Learning Path