SQL Notes
Learn how to use the EXCEPT operator in SQL to find rows in one result set that do not exist in another, with syntax, rules, and practical examples.
Sometimes you need to find what is in one dataset but not in another. Which customers have never placed an order? Which products are in the Mumbai warehouse but not in Delhi? Which employees attended the first training session but missed the second?
The EXCEPT operator (called MINUS in Oracle) returns rows from the first query that do not appear in the second query. It gives you the difference between two result sets — everything that is unique to the first set.
This operation is fundamentally important in data analysis and compliance work. Whenever you need to find gaps — customers who have not ordered, students who have not submitted assignments, employees who have not completed training — EXCEPT provides an elegant, readable solution. It answers the question "what is present here but missing there?" which comes up constantly in real-world database work.
Simple Example
AllEmployees:
| EmpID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
| 4 | Sneha |
| 5 | Vikas |
EmployeesWithBonus:
| EmpID | Name |
|---|---|
| 1 | Rahul |
| 3 | Amit |
| 5 | Vikas |
-- Who did NOT receive a bonus?
SELECT EmpID, Name FROM AllEmployees
EXCEPT
SELECT EmpID, Name FROM EmployeesWithBonus;Result:
| EmpID | Name |
|---|---|
| 2 | Priya |
| 4 | Sneha |
Priya and Sneha are in AllEmployees but not in EmployeesWithBonus.
Order Matters
EXCEPT is not symmetric — swapping the queries gives different results:
-- Employees WITHOUT bonus (in A but not B)
SELECT Name FROM AllEmployees
EXCEPT
SELECT Name FROM EmployeesWithBonus;
-- Returns: Priya, Sneha
-- Employees with bonus but not in AllEmployees (in B but not A)
SELECT Name FROM EmployeesWithBonus
EXCEPT
SELECT Name FROM AllEmployees;
-- Returns: empty (everyone with a bonus is in AllEmployees)Think of it as subtraction: A EXCEPT B means "A minus whatever overlaps with B."
Real-World Example: Customers Who Never Ordered
-- Find customers who have accounts but never placed an order
SELECT CustomerID, Name, Email
FROM Customers
EXCEPT
SELECT DISTINCT c.CustomerID, c.Name, c.Email
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID;This is one of the most common EXCEPT patterns — finding records in a master table that have no corresponding entries in a related table.
Real-World Example: Missing Inventory
-- Products available online but out of stock in the physical store
SELECT ProductID, ProductName
FROM OnlineInventory
WHERE Stock > 0
EXCEPT
SELECT ProductID, ProductName
FROM StoreInventory
WHERE Stock > 0;This helps identify products customers can buy online but cannot find in the store.
Real-World Example: Training Compliance
-- Employees who have NOT completed mandatory safety training
SELECT EmpID, Name, Department
FROM Employees
WHERE Status = 'Active'
EXCEPT
SELECT e.EmpID, e.Name, e.Department
FROM Employees e
JOIN TrainingRecords tr ON e.EmpID = tr.EmpID
WHERE tr.CourseName = 'Safety Training' AND tr.Status = 'Completed';HR can use this to identify employees who still need to complete their training.
EXCEPT with the Same Table
-- Employees earning above average who are NOT managers
SELECT EmpID, Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees)
EXCEPT
SELECT EmpID, Name, Salary
FROM Employees
WHERE Designation = 'Manager';This finds high-earning non-managers — potentially employees ready for promotion.
EXCEPT vs NOT IN
EXCEPT and NOT IN can achieve similar results, but they handle NULLs differently:
Understanding this difference is crucial for writing correct queries. NOT IN has a well-known pitfall: if any value in the subquery's result is NULL, the entire NOT IN condition evaluates to UNKNOWN (neither true nor false), which effectively returns no rows at all. This is a subtle bug that has tripped up countless developers. EXCEPT handles NULLs correctly by treating them as comparable values during the set difference operation, making it the safer choice in most situations.
-- Using EXCEPT
SELECT CustomerID FROM Customers
EXCEPT
SELECT CustomerID FROM Orders;
-- Using NOT IN (equivalent when no NULLs)
SELECT CustomerID FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);Important difference: if the NOT IN subquery returns any NULL values, the entire NOT IN condition returns no results. EXCEPT handles NULLs correctly.
-- Dangerous: If any CustomerID in Orders is NULL, this returns NOTHING
SELECT CustomerID FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);
-- Safe: EXCEPT handles NULLs properly
SELECT CustomerID FROM Customers
EXCEPT
SELECT CustomerID FROM Orders;EXCEPT vs NOT EXISTS
NOT EXISTS is another alternative:
-- Using NOT EXISTS
SELECT c.CustomerID, c.Name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);
-- Using EXCEPT
SELECT CustomerID, Name FROM Customers
EXCEPT
SELECT c.CustomerID, c.Name FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;NOT EXISTS is more flexible (can compare specific columns and include additional conditions), while EXCEPT is more concise for simple set differences.
Multiple EXCEPT Operations
You can chain EXCEPT operations:
-- Employees not in Engineering AND not in Marketing
SELECT EmpID, Name FROM Employees
EXCEPT
SELECT EmpID, Name FROM Employees WHERE Department = 'Engineering'
EXCEPT
SELECT EmpID, Name FROM Employees WHERE Department = 'Marketing';Note: EXCEPT is left-associative. The first EXCEPT executes first, then the second EXCEPT is applied to that result.
EXCEPT with ORDER BY
ORDER BY goes at the very end:
SELECT Name, Department FROM Employees
EXCEPT
SELECT Name, Department FROM FormerEmployees
ORDER BY Name;Database Support
| Database | Keyword | Support |
|---|---|---|
| PostgreSQL | EXCEPT | Full support |
| SQL Server | EXCEPT | Full support |
| SQLite | EXCEPT | Full support |
| Oracle | MINUS | Same as EXCEPT |
| MySQL | EXCEPT | From version 8.0.31 |
Oracle uses MINUS instead of EXCEPT — the behavior is identical:
-- Oracle syntax
SELECT Name FROM Employees
MINUS
SELECT Name FROM FormerEmployees;Simulating EXCEPT in Older MySQL
For MySQL versions without EXCEPT support:
-- Using LEFT JOIN
SELECT a.CustomerID, a.Name
FROM Customers a
LEFT JOIN Orders o ON a.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;
-- Using NOT EXISTS
SELECT CustomerID, Name FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);Common Mistakes to Avoid
Mistake 1: Forgetting that order matters. A EXCEPT B is different from B EXCEPT A. Think carefully about which set you want to subtract from.
Mistake 2: Using NOT IN with potentially NULL subqueries. EXCEPT is safer when NULLs are possible in the second query.
Mistake 3: Expecting EXCEPT to work in old MySQL. Check your MySQL version. Use LEFT JOIN or NOT EXISTS as alternatives.
Mistake 4: Comparing too many columns accidentally. EXCEPT compares entire rows. If you only want to compare by ID, select only the ID column.
Best Practices
- Use EXCEPT for clean set difference operations — it is more readable than NOT IN or NOT EXISTS
- Prefer EXCEPT over NOT IN when NULLs might be involved
- Remember order matters — first query is the base, second is subtracted
- Use MINUS in Oracle — same logic, different keyword
- Combine with UNION or INTERSECT for complex set operations
- Add source identification when debugging complex EXCEPT chains
EXCEPT is your tool for finding what is missing or unique to one dataset compared to another. It is essential for compliance checking, gap analysis, data reconciliation, and finding records that lack corresponding entries in related tables.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for EXCEPT Operator in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, set, operators, except
Related SQL Complete Guide Topics