Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Try it

SQL Customers who never ordered ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Order matters ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 IN comparison, 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, or NOT IN (careful with NULLs). EXCEPT is the shortest when you only need the key columns.
  • Duplicates are removed even if the first query contains them, unless you use ALL.