Using NOT IN with NULL Values in T-SQL

Recently I discovered a little quirk in T-SQL when using NOT IN with NULL values in the list of values to check.  I use the term “quirk” loosely here because the behavior is exactly as designed in SQL Server, though it may not be obvious.

Using NOT IN with NULL Values

Here’s how it works. If you use a static list of values or a subquery in a NOT IN list in your WHERE clause, the presence of a single null value in the list of values will result in no results being returned. The following query shows a trivial example, in which the intent is to return the list of vehicles with a type other than ‘Coupe’ or NULL:

At first glance, it would be easy to expect to see returned the rows for the Ford Explorer and the BMW X5.  However, when you run the above query (with the default option of ANSI nulls set to ON), you’ll always receive zero rows returned.  Why?  Because the NOT IN is still a set-based comparison, and the NOT IN clause has to check every single value in that list. With ANSI_NULLS ON, comparing any value to NULL yields False (technically it yields NULL, but you see what I mean). Note that the same thing would happen if we used NOT IN with NULL values from a subquery rather than a static list.

The easy workaround is to simply add an additional IS NOT NULL check to the WHERE clause. If the list being checked is a subquery and not a static list, you could also add an IS NOT NULL qualifier to that query to ensure that no NULL values are returned.

Just one quick note that this behavior is not found in the IN clause. The reason for that difference is that NOT IN clause has to check every value in the list, while IN simply checks to see if any one value matches.

Although this is an easy fix, it’s one of those thing that can sneak up on you if you’re not expecting it, particularly if you have one of these deeply nested in a complex query. When you are aware of how this behaves, though, fixing your code to properly handle NOT IN with NULL values is relatively easy.