SQL EXCEPT: Compare Two Sets in the Intended Direction

The practice task

Compare two fictional catalogue revisions and find the title IDs removed from the latest revision. Keep a repeated old ID to show that this set comparison is not a record-frequency calculation.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE old_catalog(id INTEGER);
CREATE TABLE new_catalog(id INTEGER);
INSERT INTO old_catalog VALUES (1),(1),(2),(3);
INSERT INTO new_catalog VALUES (2),(3),(4);

SELECT id FROM old_catalog
EXCEPT
SELECT id FROM new_catalog
ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

ID 1 appears only in the old catalogue, so it is the removed value. Its repeated source occurrence does not create two output rows. IDs 2 and 3 remain present in both revisions, while ID 4 is an addition rather than a removal.

Reversing the two SELECT statements would return ID 4. Always name the direction of the comparison in a report or test. A correct set operator used backwards can generate a perfectly valid result for the wrong business question.

EXCEPT compares complete result rows. Selecting a label alongside an ID means a changed label can make the same ID appear different between revisions. Compare only the identity columns for membership changes, and inspect field changes separately when they matter.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• State which input is the starting set.
• Choose the columns that define equality for this comparison.
• Do not use a distinct set difference to measure repeated-event counts.