Sean Lange (4/30/2013)
I understand the way NULL is evaluated when doing comparisons.
select * from table where MyNullColumn = MyNonColumn.
I understand that returns NULL.
Not NULL - Unknown. Quite different concepts! (NULL means the absence of any data; Unknown is a result in three-valued logic that is the result of attempting to do a comparison with data that is absent)
However for the boolean condition in this question, the TRUE condition is not returned. Therefore it will fall to the else. Maybe I am missing the semantics here. Maybe Tom is just splitting hairs about the verbiage of BOL. Pretty sure we are all on the same page.
I'm not sure if I'd call it splitting hairs, but Tom is indeed commenting on the BOL verbiage. BOL says that the first statement or block is executed if the expression evaluates to True, and the second (following ELSE) is it evaluates to False. That would imply that neither is executed when it evaluates to Unknown. The correct wording would be either "first statement/block on true, second (following ELSE) otherwise", or "first statement/block on true, second (following ELSE) on false or unknown".
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis