SQL进阶 | 三值逻辑与NULL

概述

        SQL中的三值逻辑是指逻辑运算符可以返回三种不同的值：真（true）、假（false）和未知（unknown）。这种逻辑是SQL所特有的，主要应用在比较两个或多个条件时。

        在二值逻辑中，逻辑运算符只能返回真或假。但在三值逻辑中，当比较两个条件时，如果其中一个条件的值为NULL（未知），那么比较的结果也是未知。例如，如果比较两个数字，一个是5，另一个是未知的数字，那么比较的结果就是未知。因为无法确定未知数字与5的大小关系。

        三值逻辑在SQL中的应用非常广泛，比如在查询数据库时，我们经常需要比较多个条件。如果其中一个条件的值未知，那么就无法确定查询结果的真假。因此，在SQL中必须使用三值逻辑来处理这种情况。

NULL到底是什么？

       在讨论 NULL 时，我们一般都会将它分成两种类型来思考。两种NULL分别指的是“未知”（unknown）和“不适用”（not applicable, inapplicable）。以“不知道戴墨镜的人眼睛是什么颜色”这种情况为例，这个人的眼睛肯定是有颜色的，但是如果他不摘掉眼镜，别人就不知道他的眼睛是什么颜色。这就叫作未知。而“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛，所以“眼睛的颜色”这一属性并不适用于冰箱。所以，“未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”。

        NULL既不是值也不是变量。NULL 只是一个表示“没有值”的标记，这也是为什么我们经常使用"is null"来判断值是否为空，而不是"= null"的原因，在where语句中，只有查询结果为true的记录才会查询出来，而"=null"的查询结果既不是true，也不是false，是unknown。可以理解为凡是对null进行逻辑判断的结果都为unknown。

unknown

        unknown作为第三个明确的布尔型真值，这是SQL采用的一种特别的逻辑体系，SQL也为我们提供了三值逻辑的真值表。

三值逻辑真值表（NOT）

x	NOT x
t	f
u	u
f	t

三值逻辑真值表（AND）

AND	t	u	f
t	t	u	f
u	u	u	f
f	f	f	f

三值逻辑真值表（OR）

OR	t	u	f
t	t	t	t
u	t	u	u
f	t	u	f

为了便于记忆，true、false、unknown有一定的优先级顺序：

• AND 的情况：false ＞ unknown ＞ true
• OR 的情况：true ＞ unknown ＞ false

根据这个优先级顺序，更好的理解真值表：

• NOT unknown的结果是unknown；
• AND运算，unknown和false运算结果为false，其他结果都为unknown；
• OR运算，unknown和true运算结果为true，其他结果都为unknown；

下面请看一个练习题。

问题：假设 a = 2, b = 5, c = NULL，此时下面这些式子的真值是什么？
1. a < b AND b > c
2. a > b OR b < c
3. a < b OR b < c
4. NOT (b <> c)

答案
1. unknown；2. unknown； 3. true； 4. unknown

CASE表达式和NULL

--col_1 为 1 时返回○、为 NULL 时返回 × 的 CASE 表达式？
CASE col_1
 WHEN 1 THEN '○'
 WHEN NULL THEN '×'
END

        这条SQL一定不会返回'x'，因为存在col_1 = NULL，真值是unknown，是永远不会返回结果的，应该像下面的SQL这样。

CASE WHEN col_1 = 1 THEN '○'
 WHEN col_1 IS NULL THEN '×'
 END

 NOT IN 和 NOT EXISTS 不是等价的

        如果NOT IN子查询中用到的表里被选择的列中存在NULL，则 SQL 语句整体的查询结果永远是空。

        EXISTS谓词永远不会返回unknown。EXISTS只会返回true或者false。

        因此就有了IN和EXISTS可以互相替换使用，而NOT IN和NOT EXISTS却不可以互相替换的混乱现象。

ALL运算符与NULL

        如果ALL里面的子查询返回的单列中有NULL的存在，那么这个ALL表达式就永远不会筛选出任何数据，结果肯定为空。

        因为ALL谓词其实是多个以AND连接的逻辑表达式的省略写法。

极值函数、COUNT以外的聚合函数与NULL

        极值函数在统计时会把为 NULL 的数据排除掉。但是在输入为空表时会返回null。COUNT以外的聚合函数也是如此。

        比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集。需要返回所有行时，需要使用 ALL 谓词，或者使用 COALESCE 函数将极值函数返回的 NULL 处理成合适的值。

        COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

-- 当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。
select coalesce(success_cnt, 1) from tableA

/** 当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），
当success_cnt为null，而period不为null的时候，返回period的真实值。
只有当success_cnt和period均为null的时候，将返回1。 **/
select coalesce(success_cnt,period,1) from tableA

COUNT与NULL

COUNT() 函数有两种形式：COUNT(*) 返回表中的所有行（包括NULL）,COUNT(1)与COUNT(*)得到的结果一致，而 COUNT(Expression) 忽略 NULL。因此，如果你提供一个允许 NULL 值的列名，那么 COUNT() 将返回具有非 NULL 值的所有行。

-- 计算非空的行数
SELECT COUNT(*) - COUNT(<Column Name>)

总结

• NULL 不是值。
• 因为 NULL 不是值，所以不能对其使用谓词。
• 对 NULL 使用谓词后的结果是 unknown。
• unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样。
• 解决 NULL 带来的各种问题，最佳方法应该是往表里添加 NOT NULL 约束来尽力排除 NULL。