The “magic” about trustable relationships with NULL and NOT IN

As a follow-up to my former post “Why you shouldn't´trust the friendship of NULL and the (NOT) IN predicate” I asked Paul Randal during our SQL Server Master training about the possible internal reason that the results can vary if you have NULL in the IN-list. The explanation is that easy that I did not even think of until Paul redirected my request to Bob Beauchemin who brought me back on the right track.

(Now) Sure that the

SomeColumn NOT IN (1,2,NULL)

will evaluate to

SomeColumn != 1 AND
SomeColumn != 2 AND
SomeColumn != NULL (which will evaluate to “unknown” as true and true and unknown will be unknown)

A simple sample would be the following:

    1:  SET ANSI_NULLS OFF

    2:   

    3:  Select 1

    4:  WHERE 2 NOT IN (1, NULL)

    5:   

    6:  -----------

    7:  1

    8:   

    9:  (1 row(s) affected)

   10:   

   11:   

   12:  SET ANSI_NULLS ON

   13:   

   14:  Select 1

   15:  WHERE 2 NOT IN (1, NULL)

   16:   

   17:  -----------

   18:   

   19:  (0 row(s) affected)

That might remind you at the ANSI NULLS setting to control the behavior of the NULL comparison. As you can see in line 1, after setting that off, the results will return the values treating NULL as a comparable value and returning false for NULL, evaluating the whole condition to true and displaying the value.

-Jens