Database Programming: NULL and (NOT) IN Don’t Mix Well

Jens Suessmeyer is a Microsoft Consultant in Germany who frequently shares his useful techniques and insights both inside Microsoft and in the community at large. In his latest post, he answers a colleague’s question with a simple yet thorough repro which proves the titular point.

Here’s the money quote from Books OnLine:

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values together with IN or NOT IN can produce unexpected results.

As Jens’ repro shows, these “unexpected results” can include the entire comparison failing.

I wouldn’t have expected that.

Thanks to Jens for sharing. If you use a lot of (NOT) IN, you should definitely check his post.

-wp

this copyrighted material was originally posted at https://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.