The NOT IN trap

The question

Marketing wants to find early customers, those who joined before April, who live in a city where none of the newer customers live. You write the natural query:

SELECT name, city
FROM customers
WHERE joined_on < '2025-04-01'
  AND city NOT IN (SELECT city FROM customers WHERE joined_on >= '2025-04-01');

  city
---------

 Karachi
 Seoul

 name | city
------+------
(0 rows)

Why it returns nothing

city NOT IN (NULL, 'Karachi', 'Seoul', NULL) means

city <> NULL AND city <> 'Karachi' AND city <> 'Seoul' AND city <> NULL

For Asha, 'Pune' <> 'Karachi' and 'Pune' <> 'Seoul' are true, but 'Pune' <> NULL is unknown. true AND unknown is unknown, so the whole condition is unknown, and WHERE keeps only rows where it is true. The same happens for every row. One NULL in the list makes NOT IN unable to ever say true.

No error, no warning: just an empty result that looks like a valid answer.

The fix: NOT EXISTS

SELECT name, city
FROM customers c
WHERE joined_on < '2025-04-01'
  AND NOT EXISTS (
    SELECT 1 FROM customers n
    WHERE n.joined_on >= '2025-04-01' AND n.city = c.city
  );

    name    |   city
------------+----------
 Asha Rao   | Pune
 Ben Okafor | Lagos
 Chen Wei   | Shanghai
 Divya Iyer | Pune

NOT EXISTS asks “is there a newer customer in the same city?” For a NULL city, n.city = c.city is simply not true, so it never counts as a match and never poisons the answer.

Filtering the NULLs out of the list also works (… AND city IS NOT NULL inside the subquery) and returns the same four rows. But you have to remember it every time, and the next column that becomes nullable breaks the query again. NOT EXISTS does not depend on remembering.

Check yourself

1. Why does x NOT IN (1, 2, NULL) never evaluate to true?

   Show answer

   It means x <> 1 AND x <> 2 AND x <> NULL. The last comparison is unknown, so the whole condition is at best unknown, never true.

   I could explain this without looking

2. What is the safe way to write “rows of A with no matching row in B”?

   Show answer

   NOT EXISTS (SELECT 1 FROM B WHERE B.key = A.key).

   I could explain this without looking

3. When is NOT IN safe to use?

   Show answer

   With a list of constant values you wrote yourself, or a subquery whose column can never be NULL (and will not become nullable later).

   I could explain this without looking