postgresql 当条件<>为真时，为什么PostgreSQL不返回空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17679721/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does PostgreSQL not return null values when the condition is <> true
提问by Mr Shoubs
I was confused behind the reasoning of the following:
我对以下推理感到困惑：
SELECT * FROM table WHERE avalue is null
Returns x number of rows where 'avalue' is null
返回“avalue”为空的 x 行数
SELECT * FROM table WHERE avalue <> true
Does notreturn rows where 'avalue' is null.
难道不返回行，其中“安勤为空。
My reasoning (which appears to be incorrect) is that as nullis 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 trueeither.
我的推理（似乎不正确）是 as nullis 一个唯一值（它甚至不等于null）意味着它应该显示在结果集中，因为它不等于true任何一个。
I guess you could argue that by saying column <> valueyou imply that the column has a value therefore ignoring the nullvalues altogether.
我想你可能会争辩说column <> value你暗示该列有一个值，因此null完全忽略这些值。
What is the reasoning behind this and is this the same in other common SQL DB's?
这背后的原因是什么，这在其他常见的 SQL 数据库中是否相同？
My reasoning (assumption) is telling me this is counter-intuitive and I wanted to learn why.
我的推理（假设）告诉我这是违反直觉的，我想了解原因。
回答by Erwin Brandstetter
Everyhalfway decent RDBMS does it the same way, because it's correct.
I am quoting the Postgres manual here:
每个半像样的 RDBMS 都以相同的方式执行此操作，因为它是正确的。
我在这里引用Postgres 手册：
Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example,
7 = NULLyields null, as does7 <> NULL. When this behavior is not suitable, use theIS [ NOT ] DISTINCT FROMconstructs:expression IS DISTINCT FROM expression expression IS NOT DISTINCT FROM expression
当任一输入为空时，普通比较运算符产生空值（表示“未知”），而不是真或假。例如，
7 = NULL产生 null，就像7 <> NULL。当此行为不合适时，请使用以下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 <> expressioncomparison.
请注意，这些表达式的执行速度比简单expression <> expression比较要慢一些。
For booleanvalues there is also the simpler IS NOT [TRUE | FALSE].
To get what you expected in your second query, write:
对于boolean值，还有更简单的IS NOT [TRUE | FALSE].
要在第二个查询中获得您期望的内容，请编写：
SELECT * FROM table WHERE avalue IS NOT TRUE;回答by Mr Shoubs
This linkprovides a useful insight. Effectively as @Damien_The_Unbeliever points out, it uses Three-valued logic and seems to be (according to the article) the subject of debate.
此链接提供了有用的见解。正如@Damien_The_Unbeliever 所指出的那样，它使用了三值逻辑并且似乎（根据文章）是争论的主题。
A couple of other good links can be found hereand here.
I think it boils down to null not being a value, but a place holder for a value and a decision had to be made and this was it... so NULL is not equal to any value because it isn't a value and won't even not be equal to any value.... if that makes sense.
我认为它归结为 null 不是一个值，而是一个值的占位符，必须做出决定，就是这样......所以 NULL 不等于任何值，因为它不是一个值并且赢了甚至不等于任何值....如果这是有道理的。
回答by Fanda
It is normal. SQL Server does it in the same way. In SQL Server you can use
这是正常的。SQL Server 以相同的方式执行此操作。在 SQL Server 中，您可以使用
SELECT * FROM table WHERE ISNULL(avalue, 0) <> 1
For postgresql equivalent watch this: What is the PostgreSQL equivalent for ISNULL()
对于 postgresql 等效项，请注意：ISNULL() 的 PostgreSQL 等效项是什么
Consider to use NOT NULL column specification with default value, if it makes sense.
如果有意义，请考虑使用具有默认值的 NOT NULL 列规范。
EDIT:
编辑：
I think it is logic. NULL is not a value, so it is excluded from searching - you have to specify it explicitly. If SQL designers decides to go by second way (include nulls automatically), then you would get more troubles if you need to recognize no values
我认为这是逻辑。NULL 不是值，因此从搜索中排除它 - 您必须明确指定它。如果 SQL 设计者决定采用第二种方式（自动包含空值），那么如果您不需要识别任何值，则会遇到更多麻烦
