➖ SQL `MINUS` / `EXCEPT` – Find Differences Between Two Queries

The SQL MINUS (or EXCEPT, depending on the database) operator is used to return rows from the first SELECT query that are not present in the second.

It’s a powerful tool to identify what’s missing, filter out duplicates, or perform data comparisons across timeframes or sources.

📘 What is SQL MINUS?

The MINUS operator (called EXCEPT in some systems like SQL Server and PostgreSQL) compares two result sets and returns only the rows from the first query that do not exist in the second.

🧾 SQL MINUS Syntax

For Oracle or some databases:

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

For SQL Server / PostgreSQL:

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

✅ Example: Find Employees Who Left

active_employees

name	department
Alice	IT
Bob	HR
Charlie	Sales

current_employees

name	department
Alice	IT
Charlie	Sales

Let’s find employees who are no longer working (i.e., they were in active_employees but not in current_employees):

SELECT name, department FROM active_employees
MINUS
SELECT name, department FROM current_employees;

🧾 Result:

name	department
Bob	HR

✅ Bob appears only in the first query — the result includes only that row.

🔁 MINUS vs MINUS ALL / EXCEPT ALL

Some databases support EXCEPT ALL, which returns all unmatched rows including duplicates, whereas:

MINUS or EXCEPT → removes duplicates
MINUS ALL or EXCEPT ALL → retains duplicates (if supported)

🧠 Use Cases for SQL MINUS / EXCEPT

Identify deleted or missing records
Detect users who signed up but never purchased
Compare product lists across platforms
Show orders canceled from a master order list
Find new or removed entries during data migration

⚠️ Common Mistakes to Avoid

Mistake	Fix / Best Practice
Mismatched column counts	Ensure both SELECT queries return same # columns
Different or incompatible data types	Use `CAST()` to align data types
Expecting duplicates to be retained	Use `EXCEPT ALL` (if supported)
Thinking column order doesn’t matter	Match both order and data types

🔍 SQL MINUS vs INTERSECT vs UNION

Operator	Description
`UNION`	Combines rows from both queries (no duplicates)
`UNION ALL`	Combines all rows including duplicates
`INTERSECT`	Returns only rows common to both queries
`MINUS`	Returns rows from the first query not in the second

📝 Summary

SQL MINUS / EXCEPT returns rows from one query that don’t exist in another
Automatically removes duplicates
Great for data cleanup, quality checks, and comparisons
Used in reporting systems, audits, and migration validation

MINUS

➖ SQL MINUS / EXCEPT – Find Differences Between Two Queries