Hugo Kornelis (10/11/2010)

---

Great question. Thanks, cengland.

In addition to the explanation: "Thorough testing shows this also applies to the IN statement", there is also a logical explanation.

The ANSI standard defines the IN operator as a series of OR'ed equation tests. In other word, the ANSI standard says that "x IN (a, b, c)" equates to "x = a OR x = b OR x = c". Or in the case of this question, "WHERE Column1 IN (1,NULL)" equates to "WHERE Column1 = 1 OR Column1 = NULL". Under ANSI null setting, any comparison to NULL always results in the truth value Unknown. So for the five rows in the sample table, here are the evaluation results:

Column1 | Column1 = 1 | Column1 = NULL | Column1 = 1 OR Column1 = NULL
--------+-------------+----------------+------------------------------
 1      | True        | Unknown        | True
 2      | False       | Unknown        | Unknown
 3      | False       | Unknown        | Unknown
 4      | False       | Unknown        | Unknown
 Null   | Unknown     | Unknown        | Unknown

Only rows where the condition evaluates to True will be returned, so that is only 1 row.

With ANSI NULLS OFF, the result of a NULL = NULL test changes to True, so the last line now changes to all True results. (I don't know if the result of a (not NULL) = NULL test changes to False under non-ANSI settings. I've never used them and since they are deprecated, I don't really care.)