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