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:
   • Both queries must have the same number of columns.
   • The data types must be compatible.
   • Headers are taken from the first query.

---

📈 Learning Path

• ← Back: Joins Recap
• Next: PIVOT & UNPIVOT →