问题描述
我对以下推理感到困惑:
I was confused behind the reasoning of the following:
SELECT * FROM table WHERE avalue is null
返回 x 行数，其中 'avalue' 为空
Returns x number of rows where 'avalue' is null
SELECT * FROM table WHERE avalue <> true
是否不返回avalue"为空的行.
Does not return rows where 'avalue' is null.
我的推理(似乎不正确)是因为 null 是一个唯一值(它甚至不等于 null)意味着它应该显示在结果集也不等于 true .
My reasoning (which appears to be incorrect) is that as null is a unique value (it isn't even equal to null) means that it should show in the result set as it isn't equal to true either.
我猜你可以通过说 column <>value 你暗示该列有一个值，因此完全忽略 null 值.
I guess you could argue that by saying column <> value you imply that the column has a value therefore ignoring the null values altogether.
这背后的原因是什么，这在其他常见的 SQL DB 中是否相同?
What is the reasoning behind this and is this the same in other common SQL DB's?
我的推理(假设)告诉我这是违反直觉的，我想了解原因.
My reasoning (assumption) is telling me this is counter-intuitive and I wanted to learn why.
推荐答案
每个中途体面的 RDBMS 都以相同的方式执行此操作，因为它正确.
我在这里引用 Postgres 手册:
Every halfway decent RDBMS does it the same way, because it's correct.
I am quoting the Postgres manual here:
普通比较运算符会产生 null(表示未知")，而不是true 或 false，当任一输入为空时.例如， 7 = NULL 产生null，7 <>空.当这种行为不适合时，使用IS [ NOT ] DISTINCT FROM 构造:
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression
请注意，这些表达式的执行速度比简单的 表达式 <> 慢一些.表达式比较.
Note that these expressions perform a bit slower than simple expression <> expression comparison.
对于 boolean 值，还有更简单的 IS NOT [TRUE |FALSE].
要在第二个查询中获得您期望的内容，请编写:
For boolean values there is also the simpler IS NOT [TRUE | FALSE].
To get what you expected in your second query, write:
SELECT * FROM table WHERE avalue IS NOT TRUE;这篇关于为什么PostgreSQL在条件为<>时不返回空值?真的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！