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
Ward Pond's SQL Server blog : Database Programming: NULL and (NOT) IN Don’t Mix Well
.jpg)
1 comment:
Where is my copyright notice, Todd? The original post has a copyright notice on it which you have failed to reproduce here.
There are multiple cases here where you are cutting and pasting my employer's copyrighted content and listing yourself as the author. In some cases, you include the original link at the bottom of the post; in some cases, you don't.
Can you point me at any posts YOU wrote, as opposed to cutting and pasting the copyrighted work of others?
Please immediately cease and desist this activity with respect to my blog. My employer has lawyers; if you continue this activity I will notify them of it.
Post a Comment