为什么是null不等于null false

本文介绍了为什么是null不等于null false的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在阅读这篇文章： 在SQL中获取null == null

一致的是，当试图测试两个（可空的）sql列之间的相等时，正确的方法是：

其中（（A = B）OR（A IS NULL AND B IS NULL）） 当A和B为NULL时，（A = B）仍然返回FALSE，因为NULL不等于NULL。这是为什么需要额外的检查。

当测试不等式时怎么办？从上面的讨论，它让我认为，为了测试不平等，我需要做一些像：

WHERE <> B）OR（A IS NOT NULL AND B IS NULL）OR（A IS NULL AND B IS NOT NULL））

但是，我注意到这是没有必要的（至少不是informix 11.5），我可以这样做：

其中（A&B）

如果A和B为NULL，这返回FALSE。如果NULL不等于NULL，那么不应该返回TRUE？

EDIT 这些都是好的答案，但我认为我的问题有点模糊。请允许我改写：

考虑到A或B可以为NULL，足以使用

检查其不等式。

其中（A& B）

我需要明确地检查它是这样：

WHERE（（<> B）OR AND B is NULL）OR（A IS NULL AND B IS NOT NULL））

a href =stackoverflow/questions/1835546/testing-inequality-with-columns-that-c​​an-be-null>线程来回答此问题。

解决方案

涉及NULL的关系表达式实际上再次产生NULL

>

这里，<> 代表任意二进制运算符， NULL 是SQL占位符， value 是任何值（ NULL 是 em> a value）：

• NULL< value - > NULL
• NULL& NULL - > NULL

： NULL 表示无值或未知值，因此与任何实际的值的任何比较都没有意义。

是 X = 42 true，false或未知，假设你不知道什么值（如果） X 成立？ SQL说它是未知的。是 X = Y true，false或未知，因为两者都是未知的？ SQL说结果是未知。并且它说任何二进制关系操作，这是唯一的逻辑（即使在模型中的NULL不在第一位）。

SQL也提供了两个< em 后缀操作符 IS NULL 和 IS NOT NULL ，这些返回TRUE或FALSE

• NULL IS NULL - > TRUE
• NULL IS NOT NULL - > FALSE

I was reading this article: Get null == null in SQL

And the consensus is that when trying to test equality between two (nullable) sql columns, the right approach is:

where ((A=B) OR (A IS NULL AND B IS NULL))

When A and B are NULL, (A=B) still returns FALSE, since NULL is not equal to NULL. That is why the extra check is required.

What about when testing inequalities? Following from the above discussion, it made me think that to test inequality I would need to do something like:

WHERE ((A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL))

However, I noticed that that is not necessary (at least not on informix 11.5), and I can just do:

where (A<>B)

If A and B are NULL, this returns FALSE. If NULL is not equal to NULL, then shouldn't this return TRUE?

EDIT These are all good answers, but I think my question was a little vague. Allow me to rephrase:

Given that either A or B can be NULL, is it enough to check their inequality with

where (A<>B)

Or do I need to explicitly check it like this:

WHERE ((A <> B) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL))

REFER to this thread for the answer to this question.

解决方案

relational expressions involving NULL actually yield NULL again

edit

here, <> stands for arbitrary binary operator, NULL is the SQL placeholder, and value is any value (NULL is not a value):

• NULL <> value -> NULL
• NULL <> NULL -> NULL

the logic is: NULL means "no value" or "unknown value", and thus any comparison with any actual value makes no sense.

is X = 42 true, false, or unknown, given that you don't know what value (if any) X holds? SQL says it's unknown. is X = Y true, false, or unknown, given that both are unknown? SQL says the result is unknown. and it says so for any binary relational operation, which is only logical (even if having NULLs in the model is not in the first place).

SQL also provides two unary postfix operators, IS NULL and IS NOT NULL, these return TRUE or FALSE according to their operand.

• NULL IS NULL -> TRUE
• NULL IS NOT NULL -> FALSE