SQL Antipatterns, Volume 1

Mini-Antipattern: NOT IN (NULL)

If the logic of null isn’t confusing enough, there are edge cases where it’s even harder to avoid getting lost in the boolean rules.

You may have mastered the logic enough to understand that the following two queries are equivalent:

Fear-Unknown/mini/in-null.sql

SELECT * FROM Bugs WHERE status IN (NULL, 'NEW');

SELECT * FROM Bugs WHERE status = NULL OR status = 'NEW';

You know that comparing a value equals null is unknown, and that’s not true, so the first term of that comparison will never be satisfied. That’s okay, because the query still matches rows with “NEW”.

This gets really interesting when the search is negated.

Fear-Unknown/mini/not-in-null.sql

SELECT * FROM ...