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.
EXCEPTis used in PostgreSQL and SQL ServerMINUSis used in Oracle
In this guide, you’ll learn:
- Syntax and rules for
EXCEPT/MINUS - How they compare to
JOIN,INTERSECT, andNOT 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 firstMINUS: 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 :
