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

Question

(NULL = NULL) is false. Fine. Memorise as "NULL is defined not to be equal to anything".

(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)'

I'm well aware of that ... but that's not what this question is about. — Brondahl, Apr 10 '17 at 10:50
why not ? `NULL` and comparison on `NULL` is __undefined__ . So SQL implementatons are free to implement whatever they want without any justification. — Derlin, Apr 10 '17 at 10:52
[Three-valued logic](https://en.wikipedia.org/wiki/Three-valued_logic#Application_in_SQL) — Damien_The_Unbeliever, Apr 10 '17 at 10:59
@SalmanA #1 Have you run the Demo code? #2 Have you read the various answers? — Brondahl, Apr 10 '17 at 12:58
Your demo code makes **wrong** assumptions. `CASE WHEN (NULL=NULL) THEN 'true' ELSE 'false' END` implies that `NULL = NULL` is definitely not true, **it does not mean that it is false**. Nowhere in the accepted answer it says that NULL = NULL is false. — Salman A, Apr 10 '17 at 13:04
@Brondahl no. I didn't comment on those comments but on your question. — Salman A, Apr 10 '17 at 13:34
@SalmanA Fine. If you want to make snarky comments about how a question has false understanding in it (said misunderstanding being the specific cause of the question), rather than adding an answer that clarifies, or checking whether that clarification has already happened, then you're welcome to do so. — Brondahl, Apr 10 '17 at 14:16

score 7 · Accepted Answer · edited May 23 '17 at 12:10

7

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

edited May 23 '17 at 12:10

Community

1
1

answered Apr 10 '17 at 10:59

Derlin

9,572
2
32
53

Right ... yes, I see now ... My CASE queries haven't demonstrated "this value is true or false".... they've demonstrated that "this value is true **or not true**". I'm guessing if I put "IS NULL" checks in then my "Falses" would be shown to be "NULLs"? – Brondahl Apr 10 '17 at 11:01
nope, can't do that, because there really aren't bool values in SQL :( – Brondahl Apr 10 '17 at 11:04
Is it strictly accurate to say "You get `false` because SQL Server ..."? It feels more accurate to say "You get `not true`, and if you look carefully you'll find that there's no SQL query that checks for 'Is this value `false`', they all check for 'Is the value `true`' ... thus `UNDEFINED` ends up appearing to act a lot like `false`". – Brondahl Apr 10 '17 at 11:10
e.g. my `CASE` doesn't test `true`/`false`, it tests "which case is `true`, or everything else". Similarly a `WHERE` clause does the same ... it ONLY tests "is this expression `true`" – Brondahl Apr 10 '17 at 11:12
rephrased my answer. – Derlin Apr 10 '17 at 11:14
1

Cool. Minor nitpick ... you've switched between `unknown` and `undefined`. If those carry subtlely different meanings, could you clarify? If not could you standardise (just for benefit of future readers.) – Brondahl Apr 10 '17 at 11:16
1

@Brondahl: "SQL" (the query language) **does** have proper boolean values. But SQL Server (the DBMS product) does not support them. – Apr 10 '17 at 11:20
@Brondahl - the distinctions are also important because a `CHECK` constraint must not be `false` in order to be satisfied - i.e. the rule is different for the expressions there than for `CASE` or `WHERE` clauses. – Damien_The_Unbeliever Apr 10 '17 at 11:46

score 0 · Answer 2 · answered Mar 22 '19 at 00:29

0

The reason is that NULL is meant to represent the absence of a value or an unknown value. SQL is not consistent in choosing one of these meanings, but in most contexts NULL behaves like an unknown value.

NULL = NULL returns NULL because, if you compare two unknown values, the result is unknown. They could be identical or not.

There are ways to deal with NULL. The most common are IS NULL and IS NOT NULL syntaxes. But you can also compare two values in a NULL-safe way. In this case, the syntax to use depends on the DBMS you are using. More details here.

answered Mar 22 '19 at 00:29

Federico Razzoli

218
1
8

Hi @Federico, welcome to StackOverflow! Everything you've said is correct, but I don't feel like it adds anything to the existing accepted answer - was there something specific that you were trying to add? – Brondahl Mar 23 '19 at 09:16
1

I think that a "theoretical" answer on *why* NULL=NULL is NULL was missing. And the link I added explains little known syntaxes for various databases. To be honest and avoid future accusations, the link points to my blog. Anyway, you judge if the answer is appropriate or not. – Federico Razzoli Mar 23 '19 at 23:28

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

2 Answers2