Interesting NULL issue

I had a question today from someone in my team wondering why their code wasn’t returning the correct results. They’d written:

SELECT
      *
FROM TableA;

SELECT
      *
FROM TableA
WHERE 1 = 1
    AND ID1 IN (SELECT ID1 FROM TableB);

SELECT
      *
FROM TableA
WHERE 1 = 1
    AND ID1 NOT IN (SELECT ID1 FROM TableB);

The first query returned about 600 rows, the second returned 300ish, the third returned nothing.

I spotted some NULLs in TableA.ID1, and added an OR ID1 IS NULL to the WHERE clause of the third query. This produced some results but not enough.

We looked at it for a little while longer, and eventually found some NULLs in TableB.ID1. These were causing the problem because a NULL represents unknown values. Therefore, when then query tries to evaluate if any particular value for TableA.ID1 is not in the list of TableB.ID1s from the subquery, it can’t be sure because one of the items in the list has an unknown value.

In summary, if you are doing a NOT IN, make sure you don’t have a NULL in your list anywhere, or the predicate will always return false and (if the predicate is in the WHERE clause) your query won’t return any values.