⛔ SQL EXCEPT & MINUS – Compare and Subtract Query Results

🧲 Introduction – What is SQL EXCEPT / MINUS?

EXCEPT and MINUS are SQL set operators used to return rows from one SELECT query that do not exist in another. Think of it as query subtraction.

• EXCEPT is used in PostgreSQL and SQL Server
• MINUS is used in Oracle

🎯 In this guide, you’ll learn:

• Syntax and rules for EXCEPT / MINUS
• How they compare to JOIN, INTERSECT, and NOT IN
• Workarounds for unsupported databases

---

✅ 1. EXCEPT Syntax (PostgreSQL / SQL Server)

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

✅ Returns rows from the first query not present in the second.

---

➖ 2. MINUS Syntax (Oracle)

SELECT column1, column2 FROM table1
MINUS
SELECT column1, column2 FROM table2;

✅ Oracle equivalent of EXCEPT

---

🔁 3. EXCEPT vs INTERSECT vs UNION

Operator	Result Description	Duplicates Removed
UNION	All distinct rows from both queries	✅ Yes
INTERSECT	Rows present in both queries	✅ Yes
EXCEPT / MINUS	Rows in first, not in second	✅ Yes

---

🧠 4. MySQL Workaround for EXCEPT

SELECT column1, column2
FROM table1
WHERE (column1, column2) NOT IN (
  SELECT column1, column2 FROM table2
);

✅ MySQL does not support EXCEPT or MINUS—use NOT IN or LEFT JOIN instead.

---

🔍 5. Example – Customers with No Orders

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

✅ Returns customer IDs that do not appear in the orders table.

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use for data difference analysis	Using with different column counts
Combine with INTERSECT for completeness	Expecting MySQL to support it directly
Match column positions and types	Ignoring NULL impact on comparisons

---

📌 Summary – Recap & Next Steps

EXCEPT / MINUS helps you find rows that are unique to one result set. It’s useful for anti-joins, integrity checks, and de-duplication.

🔍 Key Takeaways:

• EXCEPT: Subtract second query from first
• MINUS: Same in Oracle
• Both remove duplicates automatically
• Not available in MySQL—use JOIN or NOT IN

⚙️ Real-World Relevance:
Used in audit reports, data quality checks, and access control testing.

➡️ Next: Learn CASE, COALESCE, or write anti-joins with LEFT JOIN and NULL filters.

---

❓ FAQ – SQL EXCEPT / MINUS

❓ What does SQL EXCEPT do?

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

❓ What is the difference between MINUS and EXCEPT?

✅ Functionally the same; MINUS is Oracle-specific.

❓ Does EXCEPT remove duplicates?

✅ Yes. Use EXCEPT ALL (if supported) to keep duplicates.

❓ How can I do EXCEPT in MySQL?

✅ Use NOT IN, LEFT JOIN with IS NULL, or subqueries.

---

Share Now :