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

wljmcqd8 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(113)

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

sql-server

来源：https://stackoverflow.com/questions/43321100/why-is-notnull-null-false

2条答案

按热度按时间

cbwuti441#

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

赞(0）回复(0）举报 2023-10-15

h9a6wy2h2#

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 .

赞(0）回复(0）举报 2023-10-15

我来回答

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

2条答案

相关问题

热门标签

最新问答