SQL--true、false、unknown 和 null

什么是谓词？

SQL 的保留字中，有很多都被归为谓词一类。例如，“=、<、>”等比较谓词，以及 BETWEEN、LIKE、IN、IS NULL 等。

谓词是一种特殊的函数，返回值是真值。前面提到的每个谓词，返回值都是 true、false 或者 unknown（一般的谓词逻辑里没有unknown，但是 SQL 采用的是三值逻辑，因此具有三种真值）。

1、SQL中的bool类型的值有三种

普通编程语言里的布尔型只有 true 和 false 两个值，这种逻辑体系被称为二值逻辑。而 SQL 语言里，除此之外还有第三个值 unknown，因此这种逻辑体系被称为三值逻辑（three-valued logic）。

三个真值之间有下面这样的优先级顺序。

AND 的情况：false ＞ unknown ＞ true

OR 的情况：true ＞ unknown ＞ false

优先级高的真值会决定计算结果。例如 true AND unknown，因为unknown的优先级更高，所以结果是 unknown。而true OR unknown的话，因为 true 优先级更高，所以结果是 true。

unknown是因关系数据库采用了 NULL 而被引入的，他不是“未知”的这个意思，而是“无意义”的这个意思。而null是指“未知”的意思。

注意：unknown不能像true或者false一样，直接在SQL中使用，比如where Tel=unknown 。

2、null不是值，null与数学运算符一起使用的结果永远是unknown

为什么对 NULL 使用比较谓词后得到的结果永远不可能为真呢？这是因为，NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的标记，而比较谓词只适用于值。因此，对并非值的 NULL 使用比较谓词本来就是没有意义的。

常听到的“列的值为 NULL” 、“NULL 值”这样的说法本身就是错误的。因为 NULL 不是值！（如果有人认为 NULL 是值，那么它是什么类型的值？关系数据库中存在的值必然属于某种类型，比如字符型或数值型等。所以，假如 NULL 是值，那么它就必须属于某种类型。）

消除 NULL 的具体方法，这里总结如下。

(1) 首先分析能不能设置默认值。

(2) 仅在无论如何都无法设置默认值时允许使用 NULL。

笔者认为，如果遵守这两条原则，那就足以避免 NULL 带来的各种问题，使系统开发能够更加顺利地进行。

另外，注意：要想 和 null 比较 只能用 is null 或者 is not null，这样才会返回true或者false。另外永远记住一点，null和<,>,=,<>这些放在一起结果永远是unknown，比如如 2=null，结果肯定是unknown，而unknown在三值逻辑中不是true也不是false，在写where子句的筛选条件时尤其要注意。举例来讲：

 

我们经常会遇到判断筛选条件的结果（为true/false/unknown的一种，且SQL只会取返回结果是true的记录），它们通常是and 或or连接这些单个条件的，如：where age>18 and sex=0或where age<18 or sex =unknown。

请务必牢记：

and运算，只要有一边是unknown，另一边是false，那结果就是false，其它情况下，只要任意一边有unknown，结果就是unknown。

or运算，只要一边是unknown，那么结果永远就是unknown

not unknown 的结果是 unknown

case与null

当case使用的变量或列的值可能为null时，唯一正确的使用方式如下：

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

而不是：

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

3、NOT IN 和 NOT EXISTS 不是等价的

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

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

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

4、ALL运算符与null

以下是ALL运算符语法：

scalar_expression comparison_operator ALL ( subquery )

在上面语法中，

• scalar_expression是任何有效的表达式。
• comparison_operator是任何有效的比较运算符，包括等于(=)，不等于(<>)，大于(>)，大于或等于(>=)，小于(<)，小于或等于(<=)。
• 括号内的子查询(subquery)是一个SELECT语句，它返回单个列的结果。 此外，返回列的数据类型必须与标量表达式的数据类型相同。

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

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

如果all里面的子查询返回的单列中有null的存在，比如子查询结果如下面这个情况，那么具体的分析步骤如下所示。

--1. 执行子查询获取年龄列表
SELECT *
  FROM Class_A
 WHERE age < ALL ( 22, 23, NULL );
--2. 将ALL 谓词等价改写为AND
SELECT *
  FROM Class_A
 WHERE (age < 22) AND (age < 23) AND (age < NULL);
--3. 对NULL 使用“<”后，结果变为 unknown
SELECT *
  FROM Class_A
 WHERE (age < 22) AND (age < 23) AND unknown;
--4. 如果AND 运算里包含unknown，则结果不为true
SELECT *
  FROM Class_A
 WHERE false 或 unknown;
--5.查询结果为空

5、极值函数（max， min）、count以外的聚合函数（sum，average）与null

极值函数在输入为空表（空集）时会返回 NULL。即从一个为空的集合中，选取最大或最小值，会得到null。

因此，建议在使用这些可能返回null的函数的地方，外面套一层isnull函数（SqlServer）来处理。

count与null

COUNT 函数的使用方法有 COUNT(*) 和 COUNT( 列名 ) 两种，它们的区别有两个：第一个是性能上的区别；第二个是 COUNT(*) 可以用于 NULL， 而 COUNT( 列名 )与其他聚合函数一样，要先排除掉 NULL 的行再进行统计。

第二个区别也可以这么理解：COUNT(*) 查询的是所有行的数目，而COUNT( 列名 ) 查询的则不一定是。

注：资料来源：SQL总复习三：true、false、unknown 和 null - 码农教程 (manongjc.com)