Using NOT IN with NULL values

I’m writing this out of a revelation that when querying to see if a particular value is NOT IN a column that contains NULLS, you will not get the correct result set back.  This is somewhat distressful because you would expect when a specific value is not in the result set, then the query would return so.  Well, unfortunately that’s not the case.  Let’s look at an example:

[cc lang=”sql”]
— Insert two values (null and 1)
SELECT VAL
INTO #HASNULL
FROM
(
SELECT
VAL = NULL
UNION
SELECT
VAL = 1
) t

SELECT 1
WHERE 5 NOT IN
(SELECT VAL FROM #HASNULL)
[/cc]

From above, we see that we create a table named #HASNULL and insert two values, a one and a null. So when we query to see if the value 5 is in the result set, it should return a 1. But it does not. A little scary. While there may be a logistical reason for this, I’m not able to see it.

Let’s just make sure that the null is the problem. So, let’s delete the NULL and re-query:

[cc lang=”sql”]
DELETE FROM #HASNULL
WHERE VAL IS NULL

SELECT 1
WHERE 5 NOT IN
(SELECT VAL FROM #HASNULL)
[/cc]

There we have it. Now we see that a 1 is returned. This brings me to my next point. Use Exists when checking for existence.

[cc lang=”sql”]
SELECT 1
WHERE NOT EXISTS
(
SELECT 1
FROM #HASNULL
WHERE VAL = 5
)
[/cc]

As most developers know, if you want to return a result set where a value exists (or doesn’t exist) in another table, there are two ways to do it.  Either by using the EXISTS statement or the IN clause.  So what is the difference?

Well, ever since SQL 2005 there is not much (or any) of a difference.  In fact, the optimizer chooses the same plan for either the “exists” or the “in”.   Prior to SQL 2005 however, the IN clause forced an “OR” operation.  Unfortunately I don’t have SQL 2000 installed so I can’t show the execution plan. Even with the same execution plans, there is still the caveat of the above example.