3

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
user1589188
  • 5,316
  • 17
  • 67
  • 130
  • 1
    This could help you. please have a look!!! http://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-means – Mitz Dec 16 '14 at 08:34

1 Answers1

4

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.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Thanks. If I am to relate your answer to the behaviour I am experiencing, I shall take "If both sides of the comparison are columns...the setting does not affect the comparison" as the intention of ANSI_NULLS. – user1589188 Dec 17 '14 at 00:04