Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

T-SQL Quirk: Using NOT IN With NULL Values

There is a little quirk in T-SQL 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:

not_in_null1

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.

About the Author

Tim Mitchell
Tim Mitchell is a business intelligence and SSIS consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or SSIS training? Contact Tim here: TimMitchell.net/contact

Be the first to comment on "T-SQL Quirk: Using NOT IN With NULL Values"

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: