SQL Reference ยท Statements and Clauses
SQL EXCEPT
Reference for SQL EXCEPT: return rows from the first query that are missing from the second, MINUS in Oracle, NULL handling and NOT EXISTS alternatives.
EXCEPT returns the rows of the first query that do not appear in the second. Duplicates are removed from the result. It is the set difference, so order matters: A EXCEPT B is not the same as B EXCEPT A.
Oracle calls the same operator MINUS, and 21c added EXCEPT as a synonym. Both queries need the same number of columns with compatible types.
SELECT id FROM customers EXCEPT SELECT customer_id FROM orders;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
select_statement EXCEPT [ ALL ] select_statement; -- Oracle select_statement MINUS select_statement;
| Part | Description |
|---|---|
EXCEPT |
Distinct rows in the first result that are not in the second. |
EXCEPT ALL |
Keeps duplicates: a row with m copies in the first and n in the second appears max(m - n, 0) times. PostgreSQL only. |
Returns: distinct rows from the first query that are absent from the second.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
EXCEPT |
Yes | Yes | 8.0.31+ | Yes | 21c+ (MINUS before) |
EXCEPT ALL |
No | Yes | 8.0.31+ | No | No |
Precedence vs UNION |
Left to right | Same level as UNION |
Same level | Same level | Left to right |
-- Oracle, all versions SELECT id FROM customers MINUS SELECT customer_id FROM orders;
Usage notes
- Direction matters. Swap the queries and you get the opposite answer, as the second example shows.
- NULLs are equal here. A NULL in both results cancels out, unlike a
NOT INcomparison, where a NULL in the list makes the whole test unknown. See anti-joins. - Only listed columns are compared. To see other columns of the surviving rows, join back or use
NOT EXISTS. - Equivalent forms:
NOT EXISTS,LEFT JOIN ... WHERE right.id IS NULL, orNOT IN(careful with NULLs).EXCEPTis the shortest when you only need the key columns. - Duplicates are removed even if the first query contains them, unless you use
ALL.
Related
UNION,INTERSECT,EXISTS, anti-joins- Lessons: Set operations, LEFT JOIN