T-SQL: What is NOT(1=NULL)?

Question

I don't get the simple boolean algebra on my sql-server. According to msdn, the following statement should return "1", but on my server it returns "0". Can you help me?

SET ANSI_NULLS ON
SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END

Please have a look at msdn. There it clearly states: "Comparing NULL to a non-NULL value always results in FALSE." - no matter what the ANSI_NULLS-setting is. Thus "1=NULL" should be FALSE and NOT(FALSE) should thus be TRUE and the statement should return "1".

But on my machine, it returns "0"!

One explanation might be, that "1=NULL" evaluates to "UNKNOWN". NOT(UNKNOWN) is still UNKNOWN (msdn), which would force the CASE-Statement into the ELSE.

But then the official documentation of the equals-operator would be wrong. I cannot believe this!

Can anybody explain this behaviour?

Thank you very much for any help!

Edit (2012-03-15):

One thing I just found that might be of interest for some of you:

CREATE TABLE #FooTest (Value INT)
ALTER TABLE #FooTest WITH CHECK ADD CONSTRAINT ccFooTestValue CHECK (Value>1)
PRINT '(NULL>1) = ' + CASE WHEN NULL>1 THEN 'True' ELSE 'False' END
INSERT INTO #FooTest (Value) VALUES (NULL)

The print-Statement writes 'False', but the insertion runs without error. SQL-Server seems to negate the check-constraint in order to search for rows that do not fulfill the constraint-check:

IF EXISTS (SELECT * FROM inserted WHERE NOT(Value>NULL)) <Generate error>

Since the check-constraint evaluates to UNKNOWN, the negation is also UNKNOWN and SqlServer does not find any row violating the check-constraint.

Minefield

Your UNKNOWN version of explanation works for me perfectly well, and so I conclude that you've answered your own question. Unless, of course, you wanted us to explain their behaviour. :) – Tarrel

Answer 1

The MSDN page for Equals that you link to definitely appears incorrect.

Check the MSDN page for SET ANSI_NULLS.

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN.

To get that example SQL statement to work as expected, you should use compare using "IS NULL" or "IS NOT NULL" instead of using the equals operator (=). For example:

SELECT CASE WHEN NOT(1 IS NULL) THEN 1 ELSE 0 END

OR

SELECT CASE WHEN (1 IS NOT NULL) THEN 1 ELSE 0 END

Shushubert

Thank you for the other source that seems to describe the behaviour correctly. I simply could not believe that msdn is really wrong on such a very basic topic. But msdn being inconsistent in itself on this topic is enough for me. Thanks, question answered :) – Minefield

Answer 2

Yes that link is wrong. File a documentation bug on Microsoft Connect.

Sql uses three valued logic not boolean logic. true, false, and unknown

Most comparison operators (i.e. excluding IS [NOT] NULL) involving NULL result in unknown not True or False. Negating unknown yields unknown as per the truth tables shown here.

Presbyterial

Answer 3

You want to read the documentation on ANSI_NULLS. SQL actually implements a ternary logic, not boolean logic, where a comparison operation can result in true, false or undefined. Basically, this means that the explanation you proffered is correct.

This can be demonstrated with the following query:

SET ANSI_NULLS ON
SELECT CASE
  WHEN (1=NULL) THEN 0
  WHEN NOT(1=NULL) THEN 1    
  ELSE -1
END

Which results in -1 on my machine (SQL Server 2005 Enterprise). Changing the first line to SET ANSI_NULLS OFF produces 1 as expected.

So, is the official documentation wrong? I'd submit that is somewhat misleading. It says that it results in FALSE. Obviously this is wrong. What the documentation meant to say is that comparing a non-null to NULL always results in a mismatch whose value also depends on ANSI_NULLS.

Of course, on SQL Server 2012, the ANSI_NULLS setting has been removed, and therefore setting it any which way will not change the result.

Andreeandrei

Answer 4

It's not boolean logic, its trinary logic: {True, False, I Don't Know.} Break it down this way:

IF 1=NULL
    print 'True'
else
    print 'False'

Generates False because 1=NULL equals NULL, aka "not True"

IF not(1=NULL)
    print 'True'
else
    print 'False'

Also generates False because not(1=NULL) equals not(NULL) equals NULL, aka "not True". This gets you to

SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END

which as per above is the same as

SELECT CASE WHEN NULL THEN 1 ELSE 0 END

which, since NULL is not true, resovles to the ELSE clause.

In short, as far as I'm concerned the documentation is incorrect. Distressing, but not unique, and so not entirely surprising.

Paradisiacal

Answer 5

Try using EXISTS in a subquery, it uses 2 valued logic and will give you the true/false you are looking for.

Colfin

Answer 6

From BOL (credit to Thomas):

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.

So I guess the NOT operation is checking 1=NULL which is unknown and because this is not a variable or literal NULL gets the ELSE part of your comparison as you hypothesised.

Shadowgraph

Answer 7

1=NULL seems to return FALSE only when ANSI_NULLS is OFF. Otherwise it's indeterminate. The msdn page probably needs to be edited to clarify that point.

SET ANSI_NULLS OFF
SELECT CASE WHEN (1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
, CASE WHEN NOT(1=NULL) THEN 'true' ELSE 'false or unknown' END --returns true
go

SET ANSI_NULLS ON
SELECT CASE WHEN (1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
, CASE WHEN NOT(1=NULL) THEN 'true' ELSE 'false or unknown' END --returns false or unknown
go

Cabob