A query nobody would think twice about

You want every customer who isn't on an unsubscribe list. That's about as plain as SQL gets:

SELECT * FROM customers
WHERE email NOT IN (SELECT email FROM unsubscribed);

Three real customers in the table. One of them, bob@example.com, is genuinely on the unsubscribe list. The other two, alice@example.com and carol@example.com, were never unsubscribed from anything.

What it actually returns

customer_id | email
------------+-------
(0 rows)

Zero rows. Not two. Zero. Alice and Carol, who have nothing to do with any unsubscribe request, vanished from the result along with Bob. No error, no warning, just a query that runs cleanly and silently returns the wrong answer for every single customer in the table.

Why

The unsubscribed table has one row with a NULL email in it, a real, ordinary situation: someone triggered an unsubscribe flow without a captured address, a webhook fired with a missing field, a migration left a gap. That single NULL is enough to break the entire query.

Here's the mechanism. SQL doesn't use true/false logic, it uses three-valued logic: TRUE, FALSE, and UNKNOWN. Comparing anything to NULL produces UNKNOWN, not FALSE:

SELECT NULL = NULL AS eq, NULL != NULL AS neq;

 eq | neq
----+------
    |

Both NULL. Not TRUE, not FALSE. UNKNOWN, displayed as an empty result.

NOT IN (subquery) expands, conceptually, into != compared against every value the subquery returns, ANDed together. For Alice, that's roughly email != 'bob@example.com' AND email != NULL. The first comparison is TRUE. The second is UNKNOWN, because comparing anything to NULL always is. TRUE AND UNKNOWN evaluates to UNKNOWN, and a WHERE clause only keeps rows where the condition is definitely TRUE. UNKNOWN gets filtered out exactly like FALSE does. One NULL anywhere in the NOT IN subquery's results poisons the comparison for every row in the outer query, not just the row that would have matched it.

Two real, different fixes

Filter the NULL out of the subquery before it ever reaches the comparison:

SELECT * FROM customers
WHERE email NOT IN (
    SELECT email FROM unsubscribed WHERE email IS NOT NULL
);

Or sidestep the whole mechanism with NOT EXISTS, which checks row existence rather than comparing values directly, and never runs into three-valued logic in the first place:

SELECT c.* FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM unsubscribed u WHERE u.email = c.email
);

Both, confirmed directly against the real data above, correctly return Alice and Carol and correctly exclude Bob. Either is a real, complete fix. NOT EXISTS is the safer default to reach for out of habit, since it can't be broken by a NULL showing up in the subquery later, even one nobody added on purpose.

The takeaway

NOT IN and NOT EXISTS look interchangeable right up until the subquery's results can contain a NULL, and a lot of real subqueries can, even when the column looks like it "shouldn't" have one. The failure mode isn't a crash you'll notice in a log. It's a report, a filter, or an export that's quietly wrong for rows that were never supposed to be affected at all. Worth checking directly, once, on any NOT IN subquery pulling from a column you don't personally control the completeness of.