为什么当条件是<>时，PostgreSQL不返回空值。真正 [英] Why does PostgreSQL not return null values when the condition is <> true

问题描述

我对以下原因感到困惑：

  SELECT * FROM table WHERE avalue为null 
  
 
 
 
返回xavalue为空的行数
  SELECT * FROM table WHERE avalue<> true 
  
 不返回avalue为null的行。 
 
 
 
我的推理（似乎不正确）是因为 null 是一个唯一的值甚至等于 null ）表示它应该显示在结果集中，因为它不等于 true 。 
 
 
 
我想你可以说，通过列<> value 表示列的值因此忽略 null 值。
 
 
 
这背后的原因是什么，这在其他常见的SQL DB中是一样的吗？ 
 
 
 
我的推理（假设）告诉我这是反直觉的，我想学习为什么。
解决方案
      href =http://www.postgresql.org/docs/current/interactive/functions-comparison.html>此处的Postgres手册：
 
 
当任一输入为null时，普通比较运算符产生null（表示未知），而不是
 true或false。例如， 7 = NULL 产生
 null， 7<> NULL 。当这种行为不合适时，使用
  IS [NOT] DISTINCT FROM 构造：
 表达式IS DISTINCT FROM表达式
表达式不是DISTINCT FROM表达式
  
 
 
请注意，这些表达式比简单的表达式执行速度慢一些<>表达式比较。
 
 
 
对于 boolean 值，还有更简单的 IS NOT [TRUE | FALSE] 。
 
要在第二个查询中获得预期结果，请写入：
  SELECT * FROM table WHERE  avalue IS NOT TRUE ;  
 SQL Fiddle。  
 
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
SELECT * FROM table WHERE avalue <> true
Does not return rows where 'avalue' is null. 

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. 

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? 

My reasoning (assumption) is telling me this is counter-intuitive and I wanted to learn why.
解决方案
Every halfway decent RDBMS does it the same way, because it's correct.

I am quoting the Postgres manual here:

  
Ordinary comparison operators yield null (signifying "unknown"), not
  true or false, when either input is null. For example, 7 = NULL yields
  null, as does 7 <> NULL. When this behavior is not suitable, use the
  IS [ NOT ] DISTINCT FROM constructs:
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:
SELECT * FROM table WHERE avalue IS NOT TRUE;
SQL Fiddle.

                        
这篇关于为什么当条件是&lt;&gt;时，PostgreSQL不返回空值。真正的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！