Uniformity of NULLs in SQL Server 2008 R2
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 this behavior to
SET ANSI_NULLS OFF
and the result will be 1
My question is, why after setting ANSI_NULLS to OFF, the next SELECT returns nothing?
select * from (select 'a', null) ta (c1, c2), (select 'b', null) tb (c1, c2)
where ta.c2 = tb.c2
source to share
I really can't answer why this is, but the behavior is documented.
From Set ANSI_NULLS (Transact-SQL)
SET ANSI_NULLS ON affects the comparison only if one of the operands of the comparison is either a variable that is NULL or 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 enabled and any applications that explicitly set the OFF option will generate an error. Avoid using this feature in new developments and plan to change applications that currently use this feature.
source to share