MINUS
SQL · SET Operators
Theory
MINUS returns the rows from the first query that do not appear in the second, with duplicates removed. Oracle calls it MINUS; the SQL standard, PostgreSQL, SQL Server and SQLite call it EXCEPT (Snowflake accepts both). SELECT email FROM newsletter MINUS SELECT email FROM customers lists subscribers who have not become customers.
Order matters: A MINUS B is not the same as B MINUS A, just like subtraction. Both queries need the same number of columns with compatible types, and NULLs are treated as equal, so it is safer than NOT IN when NULLs are present. It is a quick way to test whether two tables hold the same data: if A EXCEPT B and B EXCEPT A both return zero rows, the tables match. Older MySQL versions lack EXCEPT and use LEFT JOIN ... IS NULL or NOT EXISTS.
Why we use it
Finding what is missing is a daily task: subscribers who never bought, records in the source but not in the target, products never sold. MINUS / EXCEPT answers this with a single set operation.
When to use it
Use MINUS / EXCEPT to compare whole rows between two result sets, especially in data validation. If you need other columns from the first table, use NOT EXISTS or LEFT JOIN ... WHERE key IS NULL.
How it works
- Run the first SELECT (A)
- Run the second SELECT (B)
- Take a row from A
- Does the same row exist in B? Yes: Discard it. No: Keep it (once).
- Return rows found only in A
Syntax
SELECT columns FROM t1
MINUS
SELECT columns FROM t2;Example
SELECT email FROM newsletter
MINUS
SELECT email FROM customers;Key points
- Called EXCEPT in standard SQL, SQL Server, PostgreSQL and SQLite
- Order of the two queries matters: A - B is not B - A
- Removes duplicates and treats NULLs as equal
- Run both directions to check two tables are identical
- Older MySQL: use NOT EXISTS or LEFT JOIN ... IS NULL