为什么 null 不等于 null false [英] why is null not equal to null false

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

问题描述

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

并且一致认为,当尝试测试两个(可为空的)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))

当 A 和 B 为 NULL 时,(A=B) 仍然返回 FALSE,因为 NULL 不等于 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))

但是,我注意到这不是必需的(至少在 informix 11.5 上不是),我可以这样做:

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

where (A<>B)

如果 A 和 B 为 NULL,则返回 FALSE.如果 NULL 不等于 NULL,那么这不应该返回 TRUE 吗?

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:

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

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.

推荐答案

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

relational expressions involving NULL actually yield NULL again

编辑

这里,<>代表任意二元运算符,NULL是SQL占位符,value是任意值(NULL不是一个值):

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

  • NULL <>值 -> NULL
  • NULL <>NULL -> NULL
  • NULL <> value -> NULL
  • 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 真、假或未知,假设您不知道什么值(如果 any)X 持有?SQL 说它是未知的.X = Y 是真、假还是未知,假设两者都是未知的?SQL 表示结果未知.它对任何二元关系运算都是如此,这只是合乎逻辑的(即使模型中首先不是 NULL).

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 还提供了两个 一元 后缀运算符,IS NULLIS NOT NULL,它们根据操作数返回 TRUE 或 FALSE.

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 不是 NULL -> FALSE
  • NULL IS NULL -> TRUE
  • NULL IS NOT NULL -> FALSE

这篇关于为什么 null 不等于 null false的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆