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.

Answer 1

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.