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'为空的行数
SELECT * FROM table WHERE avalue <> true
Does not return rows where 'avalue' is null.
不返回'avalue'为空的行。
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.
我的推理（看似不正确）是因为null是一个唯一值（它甚至不等于null）意味着它应该在结果集中显示，因为它也不等于true。
I guess you could argue that by saying column <> value you imply that the column has a value therefore ignoring the null values altogether.
我想你可以说，通过说列<>值你暗示列有一个值，因此完全忽略空值。
What is the reasoning behind this and is this the same in other common SQL DB's?
这背后的原因是什么，这在其他常见的SQL DB中是一样的吗？
My reasoning (assumption) is telling me this is counter-intuitive and I wanted to learn why.
我的推理（假设）告诉我这是违反直觉的，我想知道为什么。
3 个解决方案
#1
29
Every halfway 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:当任一输入为空时，普通比较运算符产生null（表示“未知”），而不是true或false。例如，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 <> expression comparison.
请注意，这些表达式的执行速度比简单表达式<>表达式比较慢。
For boolean values there is also the simpler IS NOT [TRUE | FALSE].
To get what you expected in your second query, write:
对于布尔值，还有更简单的IS NOT [TRUE |假]。要获得您在第二个查询中的预期，请写入：
SELECT * FROM table WHERE avalue IS NOT TRUE;SQL小提琴。
#2
6
This link provides 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 here and 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不等于任何值，因为它不是一个值并且赢了甚至不等于任何价值......如果这是有道理的。
#3
2
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设计者决定采用第二种方式（自动包含空值），那么如果您不需要识别值，则会遇到更多麻烦
#1
29
Every halfway 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:当任一输入为空时，普通比较运算符产生null（表示“未知”），而不是true或false。例如，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 <> expression comparison.
请注意，这些表达式的执行速度比简单表达式<>表达式比较慢。
For boolean values there is also the simpler IS NOT [TRUE | FALSE].
To get what you expected in your second query, write:
对于布尔值，还有更简单的IS NOT [TRUE |假]。要获得您在第二个查询中的预期，请写入：
SELECT * FROM table WHERE avalue IS NOT TRUE;SQL小提琴。
#2
6
This link provides 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 here and 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不等于任何值，因为它不是一个值并且赢了甚至不等于任何价值......如果这是有道理的。
#3
2
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设计者决定采用第二种方式（自动包含空值），那么如果您不需要识别值，则会遇到更多麻烦