NULL equality in SQL Server 2008 R2

Question

I know by default NULL = NULL is not true, so

if null = null select 1 else select 2

will give you 2

Then you can change that behaviour by

SET ANSI_NULLS OFF

and the result will be 1

My question is why after setting the ANSI_NULLS to OFF, the following SELECT still returns nothing?

select * from (select 'a', null) ta (c1, c2), (select 'b', null) tb (c1, c2)
where ta.c2 = tb.c2

Answer 1

I really can't answer why it is like that but the behavior is documented.

From SET ANSI_NULLS (Transact-SQL)

SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

And also for completeness.

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.