EXCEPT in SQL: Set Difference and the Oracle MINUS Equivalent

EXCEPT returns the rows of the first query that are absent from the second one. It is set difference in its purest form. Oracle calls the same operator MINUS; the syntax and meaning are identical.

What EXCEPT does

Take every user and subtract the banned ones. What remains is exactly the user_id values present in the first set but missing from the second.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Like UNION, the operator requires the same number of columns and compatible types in both branches. The comparison is over the entire row, not just the first column. One key detail: EXCEPT treats NULL as a single value, so a row with NULL on the left is cancelled by a row with NULL on the right, something a plain = will never do.

• Column names come from the first SELECT.
• The result contains no duplicates: EXCEPT applies an implicit DISTINCT.
• Row order is not guaranteed, so add ORDER BY at the very end if you need one.

EXCEPT vs EXCEPT ALL

Plain EXCEPT collapses repeats: even if a value shows up ten times on the left, it appears at most once in the result. EXCEPT ALL keeps multiplicity: out of N occurrences on the left it subtracts M occurrences on the right, leaving max(N - M, 0) copies.

-- DISTINCT difference: each surviving id appears once
SELECT user_id FROM orders
EXCEPT
SELECT user_id FROM orders WHERE status = 'refunded';

-- Multiset difference: per-row counts are subtracted
SELECT user_id FROM orders
EXCEPT ALL
SELECT user_id FROM orders WHERE status = 'refunded';

Gotcha: EXCEPT ALL exists in PostgreSQL but not in MySQL (which had no EXCEPT at all before 8.0.31), and ClickHouse has its own duplicate semantics. Don't assume the multiset behavior is the default; confirm which variant your engine actually supports.

EXCEPT vs NOT EXISTS and LEFT JOIN

The same difference is often written with NOT EXISTS or LEFT JOIN ... IS NULL. They are not one-to-one synonyms, and the way each handles NULL and duplicates decides which one to reach for.

-- Set difference via NOT EXISTS
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM banned b WHERE b.user_id = u.user_id
);

-- Same idea via anti-join
SELECT u.user_id
FROM users u
LEFT JOIN banned b ON b.user_id = u.user_id
WHERE b.user_id IS NULL;

The key differences:

• EXCEPT removes left-side duplicates on its own; NOT EXISTS and LEFT JOIN keep them unless you add an explicit DISTINCT.
• EXCEPT compares the whole row across every column, whereas NOT EXISTS only checks the conditions you spell out in its WHERE.
• NOT EXISTS handles NULL in the right-hand table correctly; the classic NOT IN breaks on NULL and silently returns nothing. For a single column this is the most common trap.
• When you need to reconcile several columns at once, EXCEPT reads more cleanly: SELECT a, b, c ... EXCEPT SELECT a, b, c ... beats a verbose NOT EXISTS with three conditions.

On performance, NOT EXISTS usually wins on large tables: the planner turns it into a hash anti join and never fully materializes both branches just to deduplicate. EXCEPT first builds both sets and hashes them, which is more memory-hungry.

Reconciliation through difference

The main practical job of EXCEPT is two-way reconciliation. To find drift between a source and a target, compute the difference in both directions.

-- Rows in source but missing in target
SELECT id, user_id, amount FROM orders_source
EXCEPT
SELECT id, user_id, amount FROM orders_target;

If the query returns nothing, the source is fully contained in the target. The symmetric check swaps the branches, or you combine both differences with UNION ALL plus a direction label. This is far more reliable than comparing COUNT(*) alone: equal row counts still don't guarantee the rows match.

Bottom line: EXCEPT is a concise way to express a difference across the whole column set with automatic deduplication, ideal for reconciliation. When speed on large volumes matters or you need to preserve duplicates, reach for NOT EXISTS.