EXCEPT and EXCEPT ALL

EXCEPT takes 2 queries and returns the rows of the first that do not appear in the second. It belongs to the same family as UNION: both queries must produce the same number of columns with compatible types, the column names come from the first query, and the output is deduplicated unless ALL is added. Where UNION adds the second set, EXCEPT subtracts it, and the subtraction is directional. A EXCEPT B and B EXCEPT A answer different questions.

Signups that never confirmed

The billing team's funnel logs every signup attempt in all_signups and every email confirmation in confirmed_signups. User 2 signed up twice, on March 2 and March 10, and confirmed once. Users 1 and 3 never confirmed, and user 4 confirmed a day after signing up. The switcher runs 3 formulations over those rows: plain EXCEPT, EXCEPT ALL, and the numbered-row rewrite that reproduces EXCEPT ALL on an engine without it.

EXCEPT

Users with an unconfirmed signup, each listed once.

SELECT user_id FROM all_signups
EXCEPT
SELECT user_id FROM confirmed_signups
ORDER BY user_id;

Reversed

The same operator with the sides swapped: confirmations with no signup.

SELECT user_id FROM confirmed_signups
EXCEPT
SELECT user_id FROM all_signups
ORDER BY user_id;

Counted

EXCEPT ALL's result, rebuilt from numbered occurrences for engines without it.

WITH a AS (
  SELECT user_id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY signup_date) AS n
  FROM all_signups
),
c AS (
  SELECT user_id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY confirmed_date) AS n
  FROM confirmed_signups
)
SELECT user_id
FROM (
  SELECT user_id, n FROM a
  EXCEPT
  SELECT user_id, n FROM c
) AS unmatched
ORDER BY user_id;

The first tab returns users 1 and 3. User 2 does not appear, because EXCEPT removes every copy of a row that has any match on the right, and user 2 has one. The second tab swaps the sides and returns nothing: both confirmed users also signed up, so subtracting the signups leaves no row. The order of the sides decides the question. The third tab is the counted version. Written EXCEPT ALL, the operator subtracts occurrence counts, so 2 copies of user 2 on the left minus 1 on the right leaves 1: the March 10 attempt that nobody confirmed. PostgreSQL, Trino and DuckDB accept EXCEPT ALL. On an engine without it, the tab numbers each occurrence with ROW_NUMBER and subtracts the pairs. That second attempt becomes the row (2, 2), which has no partner on the right and survives, so the tab returns 1, 2 and 3.

SELECT user_id FROM all_signups
EXCEPT
SELECT user_id FROM confirmed_signups

The animation plays the first tab. The 5 rows of all_signups come in, each is compared against the 2 confirmed rows, and rows 2 and 4 find a match and leave. Rows 1 and 3 remain, and the second copy of user 2 is removed as well, since the match is on the value, not on a particular row.

Set operators compare rows with IS NOT DISTINCT FROM semantics: 2 NULL values count as equal, so a NULL on the left is removed by a NULL on the right. That differs from WHERE user_id = ..., where NULL matches nothing. The difference is why EXCEPT is a safer reconciliation tool than NOT IN, which returns no rows at all when the right side contains a single NULL.

Reconciliation, where EXCEPT earns its keep

Any question shaped as what is in A and missing from B is an EXCEPT. A cleaning step reads a raw table and writes a curated one, and SELECT * FROM raw EXCEPT SELECT * FROM curated lists the rows it dropped, compared column by column, with no join condition to write. A migration copies a table to a new system, and running the subtraction in both directions produces a full diff: rows lost and rows invented. A contract test compares the rows a job produced today against the rows it was expected to produce, and an empty result in both directions is a pass.

Sometimes the answer needs columns of the left row beyond the ones being compared, or a filter on the right side that the left does not share. In that case the NOT EXISTS form from the previous section does the same subtraction on a key and keeps every column of the outer row. Pick by what the output needs: EXCEPT for a row-level diff with no join to write, NOT EXISTS for a subtraction on a key that returns the full left row.

Assemble the subtraction

The signup table counts attempts, so the query that lists unconfirmed attempts, including repeats, needs EXCEPT ALL and the sides in the right order. Arrange the tiles. Two of them do not belong.

List every signup attempt that was never confirmed, so that a user who tried twice and confirmed once still shows one remaining attempt, sorted by user.

About this lesson

This section is part of the Query Structure: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.