Why is `NOT(NULL=NULL)` false?

Question

(NULL = 1) is false. Fine. Memorise as "NULL is defined not to be equal to any other value".

(NULL = NULL) is false. Uhhh ... OK, fair enough . Memorise as "NULL represents an undefined value, so you never know whether it is or isn't equal to something else".

NOT(NULL = NULL) is false. Wait, What!?

Seriously, how can that be valid? How can the behaviour of the "NOT()" operator depend on details of the expression that was being evaluated!? Do all SQL systems do this?

---

Demo Query:

SELECT '"1 & 1"',
       '"1 = 1" is ' + (CASE WHEN (1=1) THEN 'true' ELSE 'false' END) AS 'a=b',
       '"1 <> 1" is ' + (CASE WHEN (1<>1) THEN 'true' ELSE 'false' END) AS 'a<>b',
       '"NOT(1=1)" is ' + (CASE WHEN NOT(1=1) THEN 'true' ELSE 'false' END) AS 'NOT(a=b)',
       '"NOT(1<>1)" is ' + (CASE WHEN NOT(1<>1) THEN 'true' ELSE 'false' END) AS 'NOT(a<>b)'
UNION
SELECT '"1 & 2"',
       '"1 = 2" is ' + (CASE WHEN (1=2) THEN 'true' ELSE 'false' END)AS 'a=b',
       '"1 <> 2" is ' + (CASE WHEN (1<>2) THEN 'true' ELSE 'false' END)AS 'a<>b',
       '"NOT(1=2)" is ' + (CASE WHEN NOT(1=2) THEN 'true' ELSE 'false' END)AS 'NOT(a=b)',
       '"NOT(1<>2)" is ' + (CASE WHEN NOT(1<>2) THEN 'true' ELSE 'false' END) AS 'NOT(a<>b)'
UNION
SELECT '"NULL & 1"',
       '"NULL = 1" is ' + (CASE WHEN (NULL=1) THEN 'true' ELSE 'false' END) AS 'a=b',
       '"NULL <> 1" is ' + (CASE WHEN (NULL<>1) THEN 'true' ELSE 'false' END) AS 'a<>b',
       '"NOT(NULL=1)" is ' + (CASE WHEN NOT(NULL=1) THEN 'true' ELSE 'false' END) AS 'NOT(a=b)',
       '"NOT(NULL<>1)" is ' + (CASE WHEN NOT(NULL<>1) THEN 'true' ELSE 'false' END) AS 'NOT(a<>b)'
UNION
SELECT '"NULL & NULL"',
       '"NULL = NULL" is ' + (CASE WHEN (NULL=NULL) THEN 'true' ELSE 'false' END)AS 'a=b',
       '"NULL <> NULL" is ' + (CASE WHEN (NULL<>NULL) THEN 'true' ELSE 'false' END)AS 'a<>b',
       '"NOT(NULL=NULL)" is ' + (CASE WHEN NOT(NULL=NULL) THEN 'true' ELSE 'false' END)AS 'NOT(a=b)',
       '"NOT(NULL<>NULL)" is ' + (CASE WHEN NOT(NULL<>NULL) THEN 'true' ELSE 'false' END) AS 'NOT(a<>b)'

Answer 1

The three-valued logic (3VL) defines the logical operators as:

+---------+---------+---------+---------+---------+
| p       | q       | p OR q  | p AND q | p = q   |
+---------+---------+---------+---------+---------+
| True    | Unknown | True    | Unknown | Unknown |
| False   | Unknown | Unknown | False   | Unknown |
| Unknown | True    | True    | Unknown | Unknown |
| Unknown | False   | Unknown | False   | Unknown |
| Unknown | Unknown | Unknown | Unknown | Unknown |
+---------+---------+---------+---------+---------+

The NOT behavior has the following truth table:

+---------+---------+
| p       | NOT p   |
+---------+---------+
| True    | False   |
| False   | True    |
| Unknown | Unknown |
+---------+---------+

So, in the expression NOT(NULL = NULL), you get:

NULL = NULL -> Unknown
NOT(Unknown) -> Unknown

Your case condition always acts like not fulfilled because your expression evaluates to Unknown, i.e. neither true nor false.

For more information on the way SQL Server works regarding nulls, have a look at Why does NULL = NULL evaluate to false in SQL server