EXCEPT

The EXCEPT operator returns distinct rows from the first SELECT query that do not appear in the second SELECT query. It is the set-theoretic difference — useful for finding records that exist in one data set but are absent from another, such as top-rated films that are not popular, or customers who have not placed an order.

EXCEPT syntax

SELECT select_list FROM A
EXCEPT
SELECT select_list FROM B;

Both queries must return the same number of columns in the same order with compatible data types. Order is significant: A EXCEPT B and B EXCEPT A produce different results. To sort the output:

SELECT select_list FROM A
EXCEPT
SELECT select_list FROM B
ORDER BY sort_expression;

EXCEPT example

Find top-rated films that are not in the most popular list:

SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films;

          title          | release_year
-------------------------+--------------
 The Shawshank Redemption|         1994
 12 Angry Men            |         1957
(2 rows)

The Godfather and The Dark Knight appear in both tables and are excluded from the result.

EXCEPT with ORDER BY

SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films
ORDER BY title;

          title          | release_year
-------------------------+--------------
 12 Angry Men            |         1957
 The Shawshank Redemption|         1994
(2 rows)

EXCEPT vs NOT IN vs anti-join

Three approaches to find rows in A that are absent from B:

-- EXCEPT: compares entire rows, deduplicates, handles NULLs correctly
SELECT * FROM top_rated_films
EXCEPT
SELECT * FROM most_popular_films;

-- NOT EXISTS anti-join: key-based, flexible, NULL-safe
SELECT t.*
FROM top_rated_films t
WHERE NOT EXISTS (
  SELECT 1 FROM most_popular_films m
  WHERE m.title = t.title AND m.release_year = t.release_year
);

-- NOT IN: avoid when subquery may return NULLs
SELECT * FROM top_rated_films
WHERE title NOT IN (SELECT title FROM most_popular_films);

EXCEPT is the most concise for whole-row comparison. The anti-join with NOT EXISTS is more flexible and performs well with indexes on the join key.

Key tips for using EXCEPT

• EXCEPT is directional: always double-check which query is on the left and which is on the right, as reversing them produces a completely different result.
• EXCEPT automatically deduplicates the output. Use EXCEPT ALL to preserve duplicates based on count differences between the two result sets.
• Prefer EXCEPT or NOT EXISTS over NOT IN when the second query may return NULL values — NOT IN with any NULL in the subquery returns zero rows.
• For large tables, a NOT EXISTS anti-join with an index on the join key often outperforms EXCEPT, which must hash or sort both full result sets.
• All set operators have equal precedence and are evaluated left to right. Use parentheses to control evaluation order when mixing UNION, INTERSECT, and EXCEPT.

Reference: PostgreSQL documentation — Combining Queries (UNION, INTERSECT, EXCEPT).