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

Question

I am not getting 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 see msdn . It clearly states: "Comparing NULL with a value other than NULL always results in FALSE." - regardless of the ANSI_NULLS settings. Thus, "1 = NULL" must be FALSE, and NOT (FALSE) must be TRUE, and the statement must return "1".

But on my machine, it returns "0"!

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

But then the official documentation of the equality operator would be wrong. I can not believe this!

Can anyone explain this behavior?

Thanks so much for any help!

Edit (2012-03-15):

One thing I just found that might be of interest to 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 insert starts without errors. The SQL server seems to negate the check constraint for finding rows that do not check the constraints:

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

Since check-constraint evaluates to UNKNOWN, the negation is also UNKNOWN, and SqlServer does not find a string that violates the check constraint.

Answer 1

The MSDN page for Equals that you are linking to definitely looks incorrect.

Check the MSDN page for SET ANSI_NULLS .

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

To make this SQL example work as expected, you should use a comparison using "IS NULL" or "IS NOT NULL" instead of using the equality 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

Answer 2

Yes, this link is incorrect. Change the documentation error on Microsoft Connect .

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

Most comparison operators (that is, excluding IS [NOT] NULL ) with NULL result in unknown not true or false . Denial of unknown results is unknown according to the truth tables shown here .

Answer 3

You want to read the documentation for ANSI_NULLS . SQL actually implements triple logic, not logical logic, where a comparison operation can result in true, false, or undefined. This basically means that the explanation you proposed 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 

The result is -1 on my machine (SQL Server 2005 Enterprise). Changing the first line to SET ANSI_NULLS OFF calls 1 as expected.

So, is the official documentation wrong? I would say that this is somewhat misleading. It says it leads to false. This is obviously wrong. What documentation means is that comparing non-empty with NULL always leads to a mismatch, the value of which also depends on ANSI_NULLS .

Of course, on SQL Server 2012 , the ANSI_NULLS parameter was removed and therefore set it so that it does not change the result.

Answer 4

This is not logical logic, its trinal logic: {True, False, I don’t know.} Break it down like this:

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

Creates False because 1=NULL is NULL , aka "not True"

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

It also generates False , because not(1=NULL) is not(NULL) is NULL , aka "not True". It will lead you to

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

which, as stated above, is the same as

 SELECT CASE WHEN NULL THEN 1 ELSE 0 END 

which, since NULL not true, refers to an ELSE .

In short, as far as I know, the documentation is incorrect. Anxiety, but not unique, and therefore not entirely surprising.

Answer 5

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

Answer 6

From BOL (credit to Thomas ):

SET ANSI_NULLS ON affects the comparison only if one of the comparison operands 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.

So, I suppose the NOT operation checks 1=NULL , which is unknown and because it is not a variable or literal. NULL gets the ELSE part of your comparison as you expected.

Answer 7

1 = NULL seems to return FALSE only when ANSI_NULLS is off. Otherwise, it is vague. Probably, the msdn page needs to be edited to clarify this 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