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.