PostgreSQL 三价逻辑详解
更多深度文章，请关注云计算频道：https://yq.aliyun.com/cloud
背景
在逻辑运算中有三种状态表示，真、假、不知道。
数据库的NULL表示没有值, 空的意思（在逻辑中属于 不知道）。
在三价逻辑运算中, 数据库的NULL相当于UNKNOWN的意思.
三价逻辑运算请参考 :
http://en.wikipedia.org/wiki/Three-valued_logic
来看看三价逻辑运算的真值表 :
Kleene logic
Below is a set of truth tables showing the logic operations for Kleene's logic.
| A AND B | True | Unknown | False |
|---|---|---|---|
| True | True | Unknown | False |
| Unknown | Unknown | Unknown | False |
| False | False | False | False |
| A OR B | True | Unknown | False |
|---|---|---|---|
| True | True | True | True |
| Unknown | True | Unknown | Unknown |
| False | True | Unknown | False |
| A | NOT A |
|---|---|
| True | False |
| Unknown | Unknown |
| False | True |
Lukasiewicz logic真值表略...
简单的解释一下, 在这里Unknown可能是true也可能是false。
因此 :
对于Unknown and true 可能是true and true也可能是false and true. 那么结果应该是true或者false. 最终还是不确定. 所以还是Unknown.
对于NOT Unknown. 也一样, 可能是NOT true也可能是NOT false, 结果有可能是true或者false, 最终还是不确定, 所以还是Unknown.
对于Unknown or true, 不管Unknown是true还是false, 结果都是true.
对于Unknown and false, 不管Unknown是true还是false, 结果都是false.
对于Unknown and Unknown, 可能是true and true或者true and false或者false and false最终结果不确定, 所以还是Unknown.
对于Unknown or Unknown, 可能是true or true或者true or false或者false or false最终结果不确定, 所以还是Unknown.
在PostgreSQL数据库中是如何处理的呢？
正文
验证以上真值表(仅验证含null的部分) :
ocz@db-172-16-3-150-> psql digoal
接下来测试where条件中的null.
digoal=# select 1 where null;
从测试结果可以看出WHERE子句中的 null和false一样, 被排除掉了.
接下来测试数据库基本的比较操作中用到null的地方的运算结果.
| Operator | Description |
|---|---|
| < | less than |
| > | greater than |
| <= | less than or equal to |
| >= | greater than or equal to |
| = | equal |
| <> or != | not equal |
以上操作符只要带有null的比较返回值都是null。
例如 :
digoal=# select 1 < null;
表示不知道null和null是否相等, 所以输出还是null.
digoal=# select null=null;
表示不知道null和null是否不相等, 所以输出还是null.
digoal=# select null <> null;
between and 相当于>= and <=, 例如 :
digoal=# select 1 between null and null;
not between and 相当于 < or >, 例如 :
digoal=# select null not between 1 and null;
2. case, 注意CASE中如果使用NULL, 是使用的=操作符. 所以null分支用于不会执行. 如下 :
digoal=# select case 1 when null then 'is null' else 'is not null' end;
其他表达式中的null :
表示不知道null+1等于多少, 结果输出还是null.
postgres=# select null+1;
注意
1. SQL中的特例, 某些使用场景中null和null被认为是相同的, 这打破了三价逻辑的规则. 如下.
postgres=# select null union select null;
2. 聚合函数, 除了count(*) 其他聚合函数都不处理null值. 例如 :
Table
| i | j |
|---|---|
| 150 | 150 |
| 200 | 200 |
| 250 | 250 |
| NULL | 0 |
Here AVG(i) is 200 (the average of 150, 200, and 250), while AVG(j) is 150 (the average of 150, 200, 250, and 0).
A well-known side effect of this is that in SQL AVG(z) is not equivalent with SUM(z)/COUNT(*).
聚合函数注意, 如果传入了distinct的话, 就要看函数的strict标记 :
* Aggregate functions that are called with DISTINCT are now passed
3. 在SQL92标准扩展文件F571定义了6个操作符, 仅返回true或false, 不返回unknown如下 :
| p | true | false | unknown |
|---|---|---|---|
| p IS TRUE | true | false | false |
| p IS NOT TRUE | false | true | true |
| p IS FALSE | false | true | false |
| p IS NOT FALSE | true | false | true |
| p IS UNKNOWN | false | false | true |
| p IS NOT UNKNOWN | true | true | false |
digoal=# select null is true;
4. 除此之外, PostgreSQL 中还包含两个逻辑操作符.
IS DISTINCT FROM 和 IS NOT DISTINCT FROM, 所有操作都返回true或者false, 不会返回null :
Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null.
digoal=# select null is distinct from null;
5. PostgreSQL transform_null_equals参数打开, 将'表达式=null'或'null=表达式'转换成'表达式 is null'. 如下 :
digoal=# set transform_null_equals=on;
注意这个参数不影响case表达式的判断, 和修改前结果一致 :
digoal=# select case null when null then 'is null' else 'is not null' end;
6. greatest和least不处理null值.
digoal=# select greatest(null,null);
7. 索引中的null值, PostgreSQL 8.3以前(不含8.3)的版本的BTREE索引不支持IS NULL的查询.
参见 HISTORY ：
* Allow col IS NULL to use an index (Teodor)
参考
1. http://en.wikipedia.org/wiki/Null_(SQL)
2. http://en.wikipedia.org/wiki/Three-valued_logic
3. http://www.databasedesign-resource.com/null-values-in-a-database.html
4. http://en.wikipedia.org/wiki/Propositional_logic
5. http://en.wikipedia.org/wiki/%C5%81ukasiewicz_logic
6. http://en.wikipedia.org/wiki/Stephen_Cole_Kleene
7. http://link.springer.com/chapter/10.1007%2F3-540-36596-6_7
8. http://www.postgresql.org/docs/9.2/static/functions-comparison.html