NULL equality in SQL Server 2008 R2 -

i know default null = null not true, so

if null = null select 1 else select 2 

will give 2

then can change behaviour by

set ansi_nulls off 

and result 1

my question why after setting ansi_nulls off, following select still returns nothing?

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

i can't answer why behavior documented.

from set ansi_nulls (transact-sql)

set ansi_nulls on affects comparison if 1 of operands of comparison either variable null or literal null. if both sides of comparison columns or compound expressions, setting not affect comparison.

and completeness.

in future version of sql server, ansi_nulls on , applications explicitly set option off generate error. avoid using feature in new development work, , plan modify applications use feature.