Good question. But the explanation is not an explanation at all. I wrote a series of blog posts about NULLs, and one part covers (a.o.) this issue - you can find it at http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx. (I recommend reading the other three parts as well - links to parts 1 and 2 are in the opening paragraph, and a link to part 4 is in the comments).
savosin_sergey (12/7/2010)
Yes, question is instrustive (for me), but BOL says:Caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results
So, results may be different! By the way, using Set ansi_nulls off, the result is three rows: "2, 3, 4"
Results are unexpected, not undefined or unpredictable. Unexpected means that the results may not match human expectation, not that results may vary.
ANSI_NULLS is on by default, and ANSI_NULLS OFF is deprecated, so I think it's fair to assume ANSI_NULLS on unless explicitly told otherwise - except, maybe, when the author includes "it depends" in the answer options.