SQL Notes
Learn how to use the MINUS operator in SQL (Oracle) and its equivalent EXCEPT in other databases to find rows in one result set that are absent from another.
The MINUS operator is Oracle's version of the set difference operation. It returns all rows from the first query that are not present in the second query. If you have learned about EXCEPT in SQL Server or PostgreSQL, MINUS does exactly the same thing — Oracle just uses a different keyword.
Think of MINUS like subtraction for datasets. You have a complete set of data, and you want to remove everything that appears in another set. The remaining rows are your result.
If you have experience with any other SQL database and are now working with Oracle, the only adjustment you need to make is using MINUS instead of EXCEPT. The behavior, rules, and patterns are identical. Oracle chose this keyword because it intuitively represents the mathematical set difference operation — you are literally subtracting one set from another. The rest of this lesson applies equally whether you use MINUS in Oracle or EXCEPT in other databases.
MINUS vs EXCEPT
| Database | Keyword | Behavior |
|---|---|---|
| Oracle | MINUS | Returns rows in first set not in second |
| SQL Server | EXCEPT | Same behavior as MINUS |
| PostgreSQL | EXCEPT | Same behavior as MINUS |
| MySQL 8.0.31+ | EXCEPT | Same behavior as MINUS |
| SQLite | EXCEPT | Same behavior as MINUS |
The functionality is identical — only the keyword differs. If you are working with Oracle, use MINUS. For everything else, use EXCEPT.
Simple Example
Consider two tables tracking employee assignments:
AllEmployees:
| EmpID | Name | Department |
|---|---|---|
| 101 | Rahul Sharma | Engineering |
| 102 | Priya Patel | Marketing |
| 103 | Amit Kumar | Engineering |
| 104 | Sneha Verma | HR |
| 105 | Vikas Gupta | Sales |
ProjectAssigned:
| EmpID | Name | Department |
|---|---|---|
| 101 | Rahul Sharma | Engineering |
| 103 | Amit Kumar | Engineering |
| 105 | Vikas Gupta | Sales |
-- Find employees NOT assigned to any project
SELECT EmpID, Name, Department FROM AllEmployees
MINUS
SELECT EmpID, Name, Department FROM ProjectAssigned;Result:
| EmpID | Name | Department |
|---|---|---|
| 102 | Priya Patel | Marketing |
| 104 | Sneha Verma | HR |
Priya and Sneha have no project assignments.
Order Matters
Just like subtraction in math, the order of operands matters:
-- A MINUS B: Employees without projects
SELECT Name FROM AllEmployees
MINUS
SELECT Name FROM ProjectAssigned;
-- Returns: Priya, Sneha
-- B MINUS A: Project members not in employee list (unlikely but possible)
SELECT Name FROM ProjectAssigned
MINUS
SELECT Name FROM AllEmployees;
-- Returns: empty set (all project members are employees)Real-World Example: Customer Churn Analysis
Find customers who were active last year but have not made any purchase this year:
-- Customers who purchased in 2025 but NOT in 2026
SELECT DISTINCT CustomerID, Name
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE EXTRACT(YEAR FROM OrderDate) = 2025
MINUS
SELECT DISTINCT CustomerID, Name
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE EXTRACT(YEAR FROM OrderDate) = 2026;This identifies churned customers who need re-engagement campaigns.
Real-World Example: Access Control Audit
Find employees who have system access but should not based on their current role:
-- Users with admin access
SELECT UserID, Username FROM SystemAccess WHERE AccessLevel = 'Admin'
MINUS
-- Users who should have admin access (managers and above)
SELECT e.EmpID, e.Username FROM Employees e
WHERE e.Designation IN ('Manager', 'Director', 'VP', 'CTO');This identifies unauthorized admin access that needs to be revoked.
Real-World Example: Course Prerequisite Check
Find students trying to enroll in advanced courses without completing prerequisites:
-- Students enrolled in Advanced Database (requires Intro to DB)
SELECT s.StudentID, s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.CourseID = 'CS401'
MINUS
-- Students who completed Intro to Database
SELECT s.StudentID, s.Name
FROM Students s
JOIN CompletedCourses cc ON s.StudentID = cc.StudentID
WHERE cc.CourseID = 'CS201' AND cc.Status = 'Passed';Using MINUS with Multiple Exclusions
Chain MINUS operations to exclude from multiple sources:
-- Employees available for new project assignment
-- (not on leave AND not already on a project AND not in probation)
SELECT EmpID, Name FROM Employees WHERE Status = 'Active'
MINUS
SELECT EmpID, Name FROM LeaveRecords WHERE LeaveDate >= SYSDATE
MINUS
SELECT EmpID, Name FROM ProjectAssignments WHERE EndDate IS NULL
MINUS
SELECT EmpID, Name FROM Employees WHERE JoinDate > ADD_MONTHS(SYSDATE, -3);MINUS with Aggregate Data
-- Product categories with declining sales (present last quarter, absent this quarter)
SELECT Category, 'Available' AS Status
FROM Products
WHERE ProductID IN (
SELECT ProductID FROM Sales WHERE SaleDate >= ADD_MONTHS(SYSDATE, -6)
AND SaleDate < ADD_MONTHS(SYSDATE, -3)
)
MINUS
SELECT Category, 'Available'
FROM Products
WHERE ProductID IN (
SELECT ProductID FROM Sales WHERE SaleDate >= ADD_MONTHS(SYSDATE, -3)
);Simulating MINUS in Non-Oracle Databases
If you need Oracle's MINUS functionality in other databases:
SQL Server and PostgreSQL
Simply use EXCEPT — it is the same operation:
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;Older MySQL (before 8.0.31)
Use LEFT JOIN with NULL check:
SELECT a.EmpID, a.Name
FROM AllEmployees a
LEFT JOIN ProjectAssigned p ON a.EmpID = p.EmpID
WHERE p.EmpID IS NULL;Or use NOT EXISTS:
SELECT EmpID, Name FROM AllEmployees a
WHERE NOT EXISTS (
SELECT 1 FROM ProjectAssigned p WHERE p.EmpID = a.EmpID
);MINUS vs NOT IN vs NOT EXISTS
| Approach | NULL Safe | Performance | Readability |
|---|---|---|---|
| MINUS/EXCEPT | Yes | Good (hash-based) | Excellent |
| NOT IN | No (fails with NULLs) | Varies | Good |
| NOT EXISTS | Yes | Good (correlated) | Moderate |
| LEFT JOIN + IS NULL | Yes | Good | Moderate |
MINUS/EXCEPT is generally the most readable option and handles NULLs correctly, making it the preferred approach when your database supports it.
Common Mistakes to Avoid
Mistake 1: Using MINUS in non-Oracle databases. Only Oracle uses MINUS. Use EXCEPT in SQL Server, PostgreSQL, MySQL 8.0.31+, and SQLite.
Mistake 2: Expecting MINUS to work on column subsets. MINUS compares entire rows. If you only want to compare by one column, select only that column.
Mistake 3: Forgetting that MINUS removes duplicates from the first set. Even without the subtraction, MINUS eliminates duplicates from the first query's results.
Mistake 4: Confusing the direction. A MINUS B gives you what is in A but not in B. Draw a Venn diagram if confused.
Best Practices
- Use MINUS in Oracle, EXCEPT everywhere else — same logic, different syntax
- Draw a Venn diagram when building complex set operations to visualize what you want
- Chain MINUS operations for multiple exclusion criteria
- Prefer MINUS over NOT IN for NULL safety
- Always test with sample data to confirm you are excluding the right rows
- Use meaningful column aliases in the first query since they become the result column names
The MINUS operator is Oracle's implementation of set difference — a fundamental operation for finding gaps, identifying non-compliance, and performing data reconciliation across datasets.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for MINUS 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, minus
Related SQL Complete Guide Topics