Is there a setting in SQL Server to have null = null evaluate to true?

Question

Is there a setting in SQL Server to have null = null evaluate to true?

Answer 1

From the MSDN Documentation:

SET ANSI_NULLS OFF

Will create the following comparison results:

  10  = NULL   False
NULL  = NULL   True
  10 <> NULL   True
NULL <> NULL   False

With the following setting, which is the default:

SET ANSI_NULLS ON

The same comparisons will give these results:

  10  = NULL   NULL (Unknown)
NULL  = NULL   NULL (Unknown)
  10 <> NULL   NULL (Unknown)
NULL <> NULL   NULL (Unknown)

Edit: Ok, so per comments, I tried some specific SQL to verify the claims that this did not work, and here's what I found:

SET ANSI_NULLS OFF
CREATE TABLE TestTable (USERNAME VARCHAR(20))
INSERT INTO TestTable VALUES (NULL)
SELECT * FROM TestTable WHERE USERNAME = USERNAME
SELECT * FROM TestTable WHERE USERNAME = NULL

Produces this output:

[USERNAME]
(0 row(s) affected)

[USERNAME]
NULL
(1 row(s) affected)

So I guess this setting is flawed. I've only seen and used this setting in one particular reporting query so I wasn't aware of the difference in query plans that makes it work in one instance and not in another.

Then there is no setting that works.

Even if it did work, as per other answers here, relying on this setting is a bad idea since it will be yanked out from SQL Server in a future version.

Oh well.

Answer 2

It is not SQL Server's fault, it is due to the ternary logic introduced by the NULL value. null=null will never be true, and null <> null is not true either.

you could use ANSI_NULL OFF but:

"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."

Wouldn't COALESCE do what you need?

Answer 3

Yeah turning that off doesn't seem good in general.

I am just mentioned this in case you are doing a comparison where you first object might not be a null in the comparison:

val IS NULL can be used to test if something is null or not.

NULL = NULL should be False because Unknown = Unknown is Unknown or False.

Answer 4

I will avoid the ansi_nulls setting and use isnull function although it could complicate certain queries.

Answer 5

This is similar to this question.

Answer 6

We're possibly dealing with this at the wrong level of abstraction. It appears that you have a query where you've stated it in such a fashion that you aren't getting the result you expect. So you've asked a question about how to change the database so it will give you what you expect, rather than what the database understood.

Wouldn't it be better to ask about how to restate your query so the database understands it as you intended?

Then you finish off with the assertion that it will complicate other queries. I think there was a general reacion of "ouch" from a lot of us who read that. because, given your apparent understanding of NULLs, that's probably true.

We might be able to help us discuss the your understanding of NULLs, if you would tell us what it is that you think will cause these problems.