SQL 为什么 null 不等于 null false

why is null not equal to null false

提问by rouble

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

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

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

共识是，当尝试测试两个（可为空的）sql 列之间的相等性时，正确的方法是：

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.

当 A 和 B 为 NULL 时，(A=B) 仍然返回 FALSE，因为 NULL 不等于 NULL。这就是为什么需要额外检查的原因。

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:

但是，我注意到这不是必需的（至少在 informix 11.5 上不是），我可以这样做：

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?

如果 A 和 B 为 NULL，则返回 FALSE。如果 NULL 不等于 NULL，那么这不应该返回 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

鉴于 A 或 B 可以为 NULL，是否足以检查它们的不等式

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 threadfor the answer to this question.

请参阅此线程以获取此问题的答案。

回答by Ben S

Because that behavior follows established ternary logicwhere NULL is considered an unknown value.

因为该行为遵循既定的三元逻辑，其中 NULL 被视为未知值。

If you think of NULL as unknown, it becomes much more intuitive:

如果您将 NULL 视为未知，它会变得更加直观：

Is unknown aequal to unknown b? There's no way to know, so: unknown.

是unknown a等于unknown b？没有办法知道，所以：unknown。

回答by just somebody

relational expressions involving NULL actually yield NULL again

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

edit

编辑

here, <>stands for arbitrary binary operator, NULLis the SQL placeholder, and valueis any value (NULLis nota value):

在这里，<>代表任意的二进制运算符，NULL是SQL占位符，value是任何值（NULL是不是一个值）：

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

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

the logic is: NULLmeans "no value" or "unknown value", and thus any comparison with any actual valuemakes no sense.

逻辑是：NULL意味着“没有价值”或“未知价值”，因此与任何实际价值的任何比较都是没有意义的。

is X = 42true, false, or unknown, given that you don't know what value (if any) Xholds? SQL says it's unknown. is X = Ytrue, 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).

是X = 42真、假还是未知，因为您不知道什么值（如果有）X？SQL 说它是未知的。是X = Y真的、假的还是未知的，因为两者都是未知的？SQL 说结果是未知的。它对任何二元关系运算都是如此，这只是合乎逻辑的（即使模型中首先不是 NULL）。

SQL also provides two unarypostfix operators, IS NULLand IS NOT NULL, these return TRUE or FALSE according to their operand.

SQL 还提供了两个一元后缀运算符，IS NULL并且IS NOT NULL，它们根据其操作数返回 TRUE 或 FALSE。

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

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

回答by Adam Robinson

All comparisons involving nullare undefined, and evaluate to false. This idea, which is what prevents nullbeing evaluated as equivalent to null, also prevents nullbeing evaluated as NOT equivalent to null.

所有涉及null的比较都是未定义的，并且评估为假。这个想法防止null被评估为等价于null，也防止null被评估为不等价于null。

回答by Robert Greiner

The short answer is... NULLs are weird, they don't really behave like you'd expect.

简短的回答是... NULL 很奇怪，它们的行为并不像您期望的那样。

Here's a great paper on how NULLs work in SQL. I think it will help improve your understanding of the topic. I think the sections on handling null values in expressions will be especially useful for you.

这是一篇关于 NULL 如何在 SQL 中工作的好论文。我认为这将有助于提高您对该主题的理解。我认为有关处理表达式中的空值的部分对您特别有用。

http://www.oracle.com/technology/oramag/oracle/05-jul/o45sql.html

http://www.oracle.com/technology/oramag/oracle/05-jul/o45sql.html

回答by Andrew

The default (ANSI) behaviour of nulls within an expression will result in a null (there are enough other answers with the cases of that).

表达式中空值的默认 (ANSI) 行为将导致空值（对于这种情况，还有足够多的其他答案）。

There are however some edge cases and caveats that I would place when dealing with MS Sql Server that are not being listed.

然而，在处理未列出的 MS Sql Server 时，我会放置一些边缘情况和警告。

• Nulls within a statement that is grouping values together will be considered equal and be grouped together.
• Null values within a statement that is ordering them will be considered equal.
• Null values selected within a statement that is using distinct will be considered equal when evaluating the distinct aspect of the query

• 将值组合在一起的语句中的空值将被视为相等并被组合在一起。
• 对它们进行排序的语句中的空值将被视为相等。
• 在评估查询的不同方面时，在使用 distinct 的语句中选择的空值将被视为相等

It is possible in SQL Server to override the expression logic regarding the specific Null = Null test, using the SET ANSI_NULLS OFF, which will then give you equality between null values - this is not a recommended move, but does exist.

在 SQL Server 中，可以使用 SET ANSI_NULLS OFF 来覆盖有关特定 Null = Null 测试的表达式逻辑，这将为您提供空值之间的相等性 - 这不是推荐的移动，但确实存在。

SET ANSI_NULLS OFF

select result =
    case
        when  null=null then 'eq' 
        else 'ne'
    end

SET ANSI_NULLS ON

select result =
    case
        when  null=null then 'eq' 
        else 'ne'
    end

回答by Alireza Rinan

Here is a Quick Fix

这是一个快速修复

ISNULL(A,0)=ISNULL(B,0)

ISNULL(A,0)=ISNULL(B,0)

0 can be changed to something that can never happen in your data

0 可以更改为数据中永远不会发生的事情

回答by Erwin Smout

"Is unknown a equal to unknown b? There's no way to know, so: unknown."

“未知 a 等于未知 b 吗？没有办法知道，所以：未知。”

The question was : why does the comparison yield FALSE ?

问题是：为什么比较结果为 FALSE？

Given three-valued logic, it would indeed be sensible for the comparison to yield UNKNOWN (not FALSE). But SQL does yield FALSE, and not UNKNOWN.

给定三值逻辑，比较产生 UNKNOWN（不是 FALSE）确实是明智的。但是 SQL 确实会产生 FALSE，而不是 UNKNOWN。

One of the myriads of perversities in the SQL language.

SQL 语言中的无数变态之一。

Furthermore, the following must be taken into account :

此外，还必须考虑以下几点：

If "unkown" is a logical value in ternary logic, then it ought to be the case that an equality comparison between two logical values that both happen to be (the value for) "unknown", then that comparison ought to yield TRUE.

如果“未知”是三元逻辑中的逻辑值，那么应该是这样的情况：两个逻辑值之间的相等比较都恰好是（值）“未知”，那么该比较应该产生 TRUE。

If the logical value is itself unknown, then obviously that cannot be represented by putting the value "unknown" there, because that would imply that the logical value is known (to be "unknown"). That is, a.o., how relational theory proves that implementing 3-valued logic raises the requirement for a 4-valued logic, that a 4 valued logic leads to the need for a 5-valued logic, etc. etc. ad infinitum.

如果逻辑值本身是未知的，那么显然不能通过将值“未知”放在那里来表示，因为这意味着逻辑值是已知的（“未知”）。也就是说，关系理论如何证明实现 3 值逻辑会提高对 4 值逻辑的要求，4 值逻辑导致需要 5 值逻辑，等等等等。