Your SQL NOT IN is broken (and nobody told you)

If you've ever run a NOT IN subquery and gotten back nothing — not a single row — you're not crazy. The database isn't broken. It's doing exactly what SQL spec says it should do.

Here's the trick: NOT IN isn't really a special operator. It's NOT (column IN (...)). So when your subquery returns a NULL, the whole thing collapses. 5 NOT IN (1, 2, NULL) is NOT (5 = 1 AND 5 = 2 AND 5 = NULL), and 5 = NULL is NULL, not TRUE. So you get NULL, which treats like FALSE. Everything disappears.

The fix? Use NOT EXISTS or LEFT JOIN ... IS NULL if your data has NULLs. Or wrap the subquery in COALESCE. But honestly, most of us just work around it and move on.

Why this matters for us: a lot of us who code, build side hustles, or manage databases at work have been writing queries that silently return wrong results — and we've been blaming ourselves for months.