title: T-SQL: What is NOT(1=NULL)?
body: <p>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?</p>
  
  <pre><code>SET ANSI_NULLS ON
  SELECT CASE WHEN NOT(1=NULL) THEN 1 ELSE 0 END
  </code></pre>
  
  <p>Please have a look at <a href="http://msdn.microsoft.com/en-us/library/ms175118%28v=sql.105%29.aspx" rel="nofollow">msdn</a>. 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".</p>
  
  <p>But on my machine, it returns "0"!</p>
  
  <p>One explanation might be, that "1=NULL" evaluates to "UNKNOWN". NOT(UNKNOWN) is still UNKNOWN (<a href="http://msdn.microsoft.com/en-us/library/ms189455.aspx" rel="nofollow">msdn</a>), which would force the CASE-Statement into the ELSE.</p>
  
  <p>But then the official documentation of the equals-operator would be wrong. I cannot believe this!</p>
  
  <p>Can anybody explain this behaviour?</p>
  
  <p>Thank you very much for any help!</p>
  
  <p>Edit (2012-03-15):</p>
  
  <p>One thing I just found that might be of interest for some of you:</p>
  
  <pre><code>CREATE TABLE #FooTest (Value INT)
  ALTER TABLE #FooTest WITH CHECK ADD CONSTRAINT ccFooTestValue CHECK (Value&gt;1)
  PRINT '(NULL&gt;1) = ' + CASE WHEN NULL&gt;1 THEN 'True' ELSE 'False' END
  INSERT INTO #FooTest (Value) VALUES (NULL)
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>IF EXISTS (SELECT * FROM inserted WHERE NOT(Value&gt;NULL)) &lt;Generate error&gt;
  </code></pre>
  
  <p>Since the check-constraint evaluates to UNKNOWN, the negation is also UNKNOWN and SqlServer does not find any row violating the check-constraint.</p>