SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using IN with possible NULL values

There is a little quirk with NULLs when using the NOT IN qualifier.  I use the term "quirk" loosely here because the behavior is exactly as intended, though it may not be obvious.  The following query shows a trivial example:


At first glance, one might think that we'll see returned the row for the Chevy MINIVAN.  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 with ANSI_NULLS ON, comparing any value to NULL yields False (technically it yields NULL, but you see what I mean).

A quick solution can be found, of course, by simply adding a NOT NULL to the lookup column in your subquery.  This isn't rocket science, and really isn't an advanced T-SQL topic, but 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.

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and Microsoft Data Platform MVP with over thirteen years of data management experience. He is the founder and principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.


Posted by Jeff Moden on 8 October 2009

There are some things that I just take for granted and the effect of comparisons with NULL using any form of relational operator is one of those things... in fact, I frequently depend on the fact that such comparisons will NOT include NULL values such as when I want to return things that are neither NULL, Empty Strings, nor BLANK strings.  For example...

WHERE SomeColumn > ' '

... will only return things that are not NULL, Empty Strings, nor BLANK strings and is actually faster than using IS NOT NULL somewhere in the mix.

BUT, I have to tell you, there are a whole lot of people that simply don't understand the impact (and, sometimes, usefullness) of such comparisons with NULL.

With that in mind, THANK YOU for taking the time to post such a simple but super important example especially since the option to turn off ANSI NULLS has been deprecated (see the following link for that announcement).


As a side bar, the code you've posted is a picture and can't be copied.  I wish the blog software would allow good folks like yourself to be able to publish such nicely formatted and useful code examples in a manner where they can be copied.

Posted by Tim Mitchell on 8 October 2009

Jeff, thanks for the kind words, as well as the heads-up on the announcement.

Leave a Comment

Please register or log in to leave a comment.