Set operations
Concept  ·  SQL & Querying

EXCEPT/INTERSECT compare whole rows with NULL-equal semantics — the opposite instinct from NOT IN/=, and exactly why EXCEPT is the safe anti-join for nullable columns.

Set operations answer 'is this row in that set?', and for that question two NULLs in the same slot are the same absence — so they match. Comparison predicates answer 'are these values equal?', and there NULL yields UNKNOWN. One symbol-shaped concept, two semantics; knowing which your construct uses is the whole skill. 'Rows in A not in B' over nullable columns is EXCEPT — the NOT IN spelling walks into the P01 flagship.

See it break
morning
itemtorque
K14.2
K2NULL
K34.5
evening
itemtorque
K14.2
K2NULL
The trap
SELECT
  item,
  torque
FROM
  morning
WHERE
  (item, torque) NOT IN (
    SELECT
  item,
  torque
FROM
  evening
  )
ORDER BY
  item;
The fix
SELECT
  item,
  torque
FROM
  morning
EXCEPT
SELECT
  item,
  torque
FROM
  evening
ORDER BY
  item;

The bike-repair diff came back empty — NOT IN over the nullable torque poisons the result, where EXCEPT compares whole rows treating two NULLs as equal and keeps the one real difference.

The rule

Use EXCEPT, or NOT EXISTS with IS NOT DISTINCT FROM; never NOT IN over a nullable column here.

Shows up elsewhere
NOT IN meets NULLUNION dedups; UNION ALL doesn't
Try one →Prove it in practice →