A single `NULL` in the subquery can change results in surprising ways.
Section: Anti-joins
Be careful with NOT IN and NULLs
sql
sql
Prefer:
WHERE NOT EXISTS (...)
Use caution:
WHERE id NOT IN (SELECT some_nullable_column FROM t)Explanation
`NOT IN` can yield no rows if the subquery returns `NULL`. `NOT EXISTS` avoids that pitfall.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing Anti-joins
Use NOT EXISTS for anti-join behavior
Find customers who have never ordered.
LEFT JOIN ... IS NULL anti-join
Find unmatched rows by outer join plus null check.
Use EXISTS for semi-join behavior
Return customers who have at least one order without duplicating customer rows.
Use IN for simple membership checks
Another semi-join style when selecting from a single column set.