EXCEPT in PostgreSQL

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 identifying records present in one dataset but absent from another, such as products in a catalog that have never been ordered, or registered users who have not logged in.

Syntax

SELECT select_list FROM table_a
EXCEPT
SELECT select_list FROM table_b;

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

SELECT select_list FROM table_a
EXCEPT
SELECT select_list FROM table_b
ORDER BY sort_expression;

Practical Example

A software company tracks all registered users and separately tracks users who have completed onboarding. Find registered users who have not finished onboarding:

CREATE TABLE registered_users (
  user_id    SERIAL PRIMARY KEY,
  email      VARCHAR NOT NULL UNIQUE,
  full_name  VARCHAR NOT NULL
);

CREATE TABLE onboarded_users (
  user_id    INT NOT NULL,
  email      VARCHAR NOT NULL,
  full_name  VARCHAR NOT NULL
);

INSERT INTO registered_users (email, full_name) VALUES
  ('alice@example',  'Alice Nguyen'),
  ('bob@example',    'Bob Okafor'),
  ('clara@example',  'Clara Hoffman'),
  ('diana@example',  'Diana Park'),
  ('evan@example',   'Evan Torres');

INSERT INTO onboarded_users (user_id, email, full_name) VALUES
  (1, 'alice@example',  'Alice Nguyen'),
  (3, 'clara@example',  'Clara Hoffman');

Find registered users who have not onboarded:

SELECT email, full_name FROM registered_users
EXCEPT
SELECT email, full_name FROM onboarded_users
ORDER BY full_name;

Result:

        email        |  full_name
---------------------+--------------
 bob@example     | Bob Okafor
 diana@example   | Diana Park
 evan@example    | Evan Torres

Alice and Clara are excluded because they appear in onboarded_users.

EXCEPT with ORDER BY

SELECT email, full_name FROM registered_users
EXCEPT
SELECT email, full_name FROM onboarded_users
ORDER BY email;

EXCEPT ALL example

If duplicates matter (e.g., counting repeated events):

-- Returns rows that appear more times in registered than onboarded,
-- preserving the count difference
SELECT email FROM registered_users
EXCEPT ALL
SELECT email FROM onboarded_users;

EXCEPT vs NOT IN vs Anti-Join

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

-- EXCEPT: whole-row comparison, deduplicates, NULL-safe
SELECT email, full_name FROM registered_users
EXCEPT
SELECT email, full_name FROM onboarded_users;

-- NOT EXISTS anti-join: key-based, NULL-safe, flexible
SELECT r.email, r.full_name
FROM registered_users r
WHERE NOT EXISTS (
  SELECT 1 FROM onboarded_users o
  WHERE o.email = r.email
);

-- NOT IN: avoid when subquery may return NULLs
SELECT email, full_name FROM registered_users
WHERE email NOT IN (SELECT email FROM onboarded_users);

EXCEPT is the most concise for whole-row comparison. The NOT EXISTS anti-join performs better on large tables with an index on the join key. Avoid NOT IN when the subquery might return NULL values — it will silently return zero rows.

Set Operators Comparison

Testing with Vela

EXCEPT is commonly used in data-quality checks — for example, finding rows in a staging table that are absent from a target table after a migration. Use Vela’s database branching to run these validation queries against a production-data branch before and after migrations. Branching lets you compare the EXCEPT result set between the baseline and post-migration states without any risk to the live database.

Production Tips

• EXCEPT is directional — always verify which query is on the left. A common mistake is accidentally writing B EXCEPT A when A EXCEPT B is intended.
• EXCEPT deduplicates output by hashing or sorting both result sets. For large tables, a NOT EXISTS anti-join with an index on the comparison key is typically faster.
• Prefer EXCEPT or NOT EXISTS over NOT IN whenever the second query could return NULL values. NOT IN with any NULL in the subquery returns an empty result due to three-valued logic.
• All set operators (UNION, INTERSECT, EXCEPT) have equal precedence and are evaluated left to right. Use parentheses to control evaluation order when combining multiple set operators in a single query.
• Use EXPLAIN ANALYZE to inspect the execution plan. EXCEPT typically appears as a HashSetOp Except or SetOp node.