SQL进阶技巧——什么是三值逻辑和NULL？

关于SQL我之前已经陆续更新了很多期，从入门到基础知识再到练习和面试技巧都有分享，大家感兴趣的话可以去看下这篇文章。

戎易大数据：几万字汇总了SQL全部知识点，必看！（含教程、面试技巧）

近几期主要围绕SQL进阶技巧来进行分享。

本篇主题为三值逻辑和NULL。

大多数编程语言都包括 布尔型 这种数据类型。当然，SQL里也有。SQL将布尔型定义为可以由用户直接操作的数据类型。此外，在WHERE子句等地方进行条件判断时也经常会用到布尔型的运算。

然而，大家知道普通编程语言里的布尔型和SQL语言里的布尔型之间有什么区别吗？普通语言里的布尔型只有true和false两个值，这种逻辑体系被称为 二值逻辑 。而SQL语言里，还有第三个值unknown，因此这种逻辑体系被称为 三值逻辑 。

那么，为什么SQL语言采用了三值逻辑呢？作为计算机基础的布尔代数是二值逻辑的，在二值逻辑的应用如此广泛的情况下，为什么关系数据库的世界特立独行，选择了三值逻辑这样风格迥异的逻辑体系呢？

问题就在于关系型数据库引进了 NULL ，所以不得不同时引进第三个值。而这第三个值给实际业务情况带来了许多不确定性。

接下来我们将给大家介绍一下三值逻辑，并通过具体的代码学习一下哪些情况需要格外留意。（前半部分更侧重理论知识，后半部分才涉及到具体代码）

01、理论

两种NULL、三值逻辑还是四值逻辑

说到三值逻辑，话题应该从NULL开始，因为NULL正是产生三值逻辑的“元凶”。

“两种NULL”这种说法大家可能会觉得很奇怪，因为SQL里只存在一种NULL。然而在讨论NULL时，我们一般都会将它分成两种类型来思考。因此这里先来介绍一些基础知识，即 两种NULL 之间的区别。

两种NULL分别指的是“ 未知 ”和“ 不适用 ”。以“不知道戴墨镜的人眼睛是什么颜色”这种情况为例，这个人的眼睛肯定是有颜色的，但是如果他不摘掉眼镜，别人就不知道他的眼睛是什么颜色。这就叫作未知。而“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛，所以“眼睛的颜色”这一属性并不适用于冰箱。

“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样，都是没有意义的。平时，我们习惯了说“不知道”，但是“不知道”也分很多种。“不适用”这种情况下的NULL，在语义上更接近于“无意义”，而不是“不确定”。这里总结一下：“未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”。

关系模型的发明者E.F. Codd最先给出了这种分类。下图是他对“丢失的信息”的分类。

关系数据库中“丢失的信息”的分类

Codd曾经认为应该严格地区分两种类型的NULL，并提倡在关系数据库中使用四值逻辑。不知道是幸运还是不幸，他的这个想法并没有得到广泛支持，现在所有的DBMS都将两种类型的NULL归为了一类并采用了三值逻辑。但是他的这种分类方法本身还是有很多优点的，因此后来依然有很多学者支持。

为什么必须写成“IS NULL”，而不是“＝NULL”

大家应该经常对上面这个标题里的问题感到困惑吧？相信刚学SQL的时候，大部分人都有过这样的经历：写了下面这样的SQL语句来查询某一列中值为NULL的行，结果却执行失败了。

-- 查询NULL时出错的SQL语句
SELECT ＊
FROM tbl_A
WHERE col_1 = NULL;

通过这条SQL语句，我们无法得到正确的结果。因为正确的写法是 col_1 IS NULL 。这和刚学C语言时写出的if (hoge = 0)的错误非常相似。那么为什么用“＝”去进行比较会失败呢？表示相等关系时用“＝”，这明明是我们在小学里就学过的常识。

这当然是有原因的。那就是，对NULL使用比较谓词后得到的结果总是 unknown 。而查询结果只会包含WHERE子句里的判断结果为 true 的行，不会包含判断结果为false和unknown的行。不只是等号，对NULL使用其他比较谓词，结果也都是一样的。所以无论col_1是不是NULL，比较结果都是unknown。

--以下的式子都会被判为 unknown
 1 = NULL
 2 > NULL
 3 < NULL
 4 <> NULL
NULL = NULL

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

“列的值为NULL”“NULL值”这样的说法本身就是错误的。因为NULL不是值，所以不在定义域中。相反，如果有人认为NULL是值，那么请仔细思考一下： 它是什么类型的值？ 关系数据库中存在的值必然属于某种类型，比如字符型或数值型等。所以，假如NULL是值，那么它就必须属于某种类型。

NULL容易被认为是值的原因恐怕有两个。第一个是在C语言等编程语言里面，NULL被定义为了一个常量（很多语言将其定义为了整数0），这导致了人们的混淆。但是，其实SQL里的NULL和其他编程语言里的NULL是完全不同的东西。

第二个原因是，IS NULL这样的谓词是由两个单词构成的，所以人们容易把IS当作谓词，而把NULL当作值。特别是SQL里还有IS TRUE、IS FALSE这样的谓词，人们由此类推，从而这样认为也不是没有道理。

但是正如讲解标准SQL的书里提醒大家注意的那样，我们应该把IS NULL看作是一个谓词。因此，如果可以的话，写成IS_NULL这样也许更合适。

unknown、第三个真值

终于轮到真值unknown登场了。本节开头也提到过，它是因关系数据库采用了NULL而被引入的“ 第三个真值 ”。

这里有一点需要注意： 真值unknown和作为NULL的一种的UNKNOWN（未知）是不同的东西。 前者是明确的布尔型的真值，后者既不是值也不是变量。为了便于区分，前者采用 小写字母 unknown，后者用普通的 大写字母 UNKNOWN来表示。为了让大家理解两者的不同，我们来看一个x=x这样的简单等式。x是真值unknown时，x=x被判断为true，而x是UNKNOWN时被判断为unknown。

--这个是明确的真值的比较
unknown = unknown → true
--这个相当于NULL = NULL
UNKNOWN = UNKNOWN → unknown

接下来我们看一下SQL遵循的三值逻辑的真值表。

三值逻辑的真值表（NOT）

三值逻辑的真值表（AND）

三值逻辑的真值表（OR）

图中浅蓝色部分是三值逻辑中独有的运算，这在二值逻辑中是没有的。其余的SQL谓词全部都能由这三个逻辑运算组合而来。从这个意义上讲，这个矩阵可以说是SQL的母体。

NOT的话，因为真值表比较简单，所以很好记；但是对于AND和OR，因为组合出来的真值较多，所以全部记住非常困难。为了便于记忆，请注意这三个真值之间有下面这样的优先级顺序。

● AND的情况： false ＞ unknown ＞ true

● OR的情况： true ＞ unknown ＞ false

优先级高的真值会决定计算结果。 例如true AND unknown，因为unknown的优先级更高，所以结果是unknown。而true OR unknown的话，因为true优先级更高，所以结果是true。记住这个顺序后就能更方便地进行三值逻辑运算了。特别需要记住的是，当AND运算中包含unknown时，结果肯定不会是true（反之，如果AND运算结果为true，则参与运算的双方必须都为true）。这一点对理解后文非常关键。

关于理论部分的就介绍到这里，接下来我们将以具体的代码为例来分析一下三值逻辑是如何带来意料之外的结果的。

02、实践

比较谓词和NULL(1)：排中律不成立

我们假设约翰是一个人。那么，下面的语句（以下称为“命题”）是真是假？

约翰是20岁，或者不是20岁，二者必居其一。——P

大家觉得正确吗？没错，在现实世界中毫无疑问这是个真命题。我们不知道约翰是谁，但只要是人就有年龄。而且只要有年龄，那么就要么是20岁，要么不是20岁，不可能有别的情况。类似的还有“凯撒渡过了卢比孔河，或者没有渡过，二者必居其一”“有外星人，或者没有外星人，二者必居其一”等，这些都是真命题。像这样，“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为 排中律 。顾名思义，排中律就是指不认可中间状态，对命题真伪的判定黑白分明，是古典逻辑学的重要原理。“是否承认这一原理”被认为是古典逻辑学和非古典逻辑学的分界线。由此可见，排中律非常重要。

如果排中律在SQL里也成立，那么下面的查询应该能选中表里的所有行。

--查询年龄是20岁或者不是20岁的学生
SELECT ＊
FROM Students
WHERE age = 20
OR age <> 20;

遗憾的是，在SQL的世界里，排中律是不成立的。假设表Students里的数据如下所示。

那么这条SQL语句无法查询到约翰，因为约翰年龄不详。关于这个原因，我们在理论篇里学习过，即对NULL进行比较运算的结果是unknown。具体来说，约翰这一行是按照下面的步骤被判断的。

--1．约翰年龄是NULL（未知的NULL！
SELECT ＊
FROM Students
WHERE age = NULL
OR age <> NULL;
--2．对NULL使用比较谓词后，结果为unknown
SELECT ＊
FROM Students
WHERE unknown
OR unknown;
--3.unknown OR unknown的结果是unknown（参考“理论”中的矩阵）
SELECT ＊
FROM Students
WHERE unknown;

SQL语句的查询结果里只有判断结果为true的行。要想让约翰出现在结果里，需要添加下面这样的“ 第3个条件 ”。

--添加第3个条件：年龄是20岁，或者不是20岁，或者年龄未知
SELECT ＊
FROM Students
WHERE age = 20
OR age <> 20
OR age IS NULL;

像这样，现实世界中正确的事情在SQL里却不正确的情况时有发生。实际上约翰这个人是有年龄的，只是我们无法从这张表中知道而已。换句话说， 关系模型并不是用于描述现实世界的模型，而是用于描述人类认知状态的核心（知识）的模型。 因此，我们有限且不完备的知识也会直接反映在表里。

即使不知道约翰的年龄，他在现实世界中也一定“要么是20岁，要么不是20岁”——我们容易自然而然地这样认为。然而，这样的常识在三值逻辑里却未必正确。

比较谓词和NULL(2):CASE表达式和NULL

下面我们来看一下在CASE表达式里将NULL作为条件使用时经常会出现的错误。首先请看下面的简单CASE表达式。

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

这个CASE表达式一定不会返回×。这是因为， 第二个WHEN子句是col_1 = NULL的缩写形式。 正如大家所知，这个式子的真值永远是unknown。而且CASE表达式的判断方法与WHERE子句一样，只认可真值为true的条件。正确的写法是像下面这样使用搜索CASE表达式。

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

这种错误很常见，其原因是将NULL误解成了值。这一点从NULL和第一个WHEN子句里的1写在了同一列就可以看出。这里请再次确认自己已经记住“ NULL并不是值 ”这点。

NOT IN和NOT EXISTS不是等价的

在对SQL语句进行性能优化时，经常用到的一个技巧是将IN改写成EXISTS。这是等价改写，并没有什么问题。问题在于，将NOT IN改写成NOT EXISTS时，结果未必一样。

例如，请看下面这两张班级学生表。

A:

B:

需要注意的是，B班山田的年龄是NULL。我们考虑一下如何根据这两张表查询“与B班住在东京的学生年龄不同的A班学生”。也就是说，希望查询到的是拉里和伯杰。因为布朗与齐藤年龄相同，所以不是我们想要的结果。如果单纯地按照这个条件去实现，则SQL语句如下所示。

--查询与B班住在东京的学生年龄不同的A班学生的SQL语句？
SELECT ＊
FROM Class_A
WHERE age NOT IN ( 
   SELECT age
   FROM Class_B
   WHERE city =’东京’);

这条SQL语句真的能正确地查询到这两名学生吗？遗憾的是不能。结果是空，查询不到任何数据。

实际上，如果山田的年龄不是NULL（且与拉里和伯杰年龄不同），是能顺利找到拉里和伯杰的。然而，这里NULL又一次作怪了。我们一步一步地看看究竟发生了什么吧。

--1．执行子查询，获取年龄列表
SELECT ＊
FROM Class_A
WHERE age NOT IN (22, 23, NULL);
--2．用NOT和IN等价改写NOT IN
SELECT ＊
   FROM Class_A
WHERE NOT age IN (22, 23, NULL);
--3．用OR等价改写谓词IN
SELECT ＊
FROM Class_A
WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );
--4．使用德·摩根定律等价改写
SELECT ＊
FROM Class_A
WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);
--5．用<>等价改写NOT和=
SELECT ＊
FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);
--6．对NULL使用<>后，结果为unknown
SELECT ＊
FROM Class_A
WHERE (age <> 22) AND (age <> 23) AND unknown;
--7．如果AND运算里包含unknown，则结果不为true（参考“理论篇”中的矩阵）
SELECT ＊
FROM Class_A
WHERE false或unknown;

可以看出，这里对A班的所有行都进行了如此繁琐的判断，然而没有一行在WHERE子句里被判断为true。也就是说， 如果NOT IN子查询中用到的表里被选择的列中存在NULL，则SQL语句整体的查询结果永远是空。 这是很可怕的现象。

为了得到正确的结果，我们需要使用EXISTS谓词。

--正确的SQL语句：拉里和伯杰将被查询到
SELECT ＊
FROM Class_A  A
WHERE NOT EXISTS ( 
 SELECT ＊
 FROM Class_B B
 WHERE A.age = B.age
 AND B.city = ’东京’);

结果：

 name  age   city
-----  ----  ----
拉里    19    埼玉
伯杰    21   千叶

同样地，我们再来一步一步地看看这段SQL是如何处理年龄为NULL的行的。

--1．在子查询里和NULL进行比较运算
SELECT ＊
FROM Class_A A
WHERE NOT EXISTS ( 
 SELECT ＊
 FROM Class_B B
 WHERE A.age = NULL
 AND B.city =’东京’);
--2．对NULL使用“=”后，结果为 unknown
SELECT ＊
FROM Class_A A
WHERE NOT EXISTS ( 
 SELECT ＊
 FROM Class_B B
 WHERE unknown
 AND B.city =’东京’);
--3．如果AND运算里包含unknown，结果不会是true
SELECT ＊
FROM Class_A A
WHERE NOT EXISTS ( 
 SELECT ＊
 FROM Class_B B
 WHERE false或unknown);
--4．子查询没有返回结果，因此相反地，NOT EXISTS为true
SELECT ＊
FROM Class_A A
WHERE true;

也就是说，山田被作为“ 与任何人的年龄都不同的人 ”来处理了（但是，还要把与年龄不是NULL的齐藤及田尻进行比较后的处理结果通过AND连接，才能得出最终结果）。产生这样的结果，是因为 EXISTS谓词永远不会返回unknown 。

EXISTS只会返回true或者false。因此就有了IN和EXISTS可以互相替换使用，而NOT IN和NOT EXISTS却不可以互相替换的混乱现象。虽然写代码的时候很难做到绝对不依赖直觉，但作为数据库工程师来说，还是需要好好理解一下这种现象。

限定谓词和NULL

SQL里有ALL和ANY两个限定谓词。 因为ANY与IN是等价的，所以我们不经常使用ANY。 在这里，我们主要看一下更常用的ALL的一些注意事项。

ALL可以和比较谓词一起使用，用来表达“与所有的××都相等”，或“比所有的××都大”的意思。接下来，我们给B班表里为NULL的列添上具体的值。然后，使用这张新表来思考一下用于查询“比B班住在东京的所有学生年龄都小的A班学生”的SQL语句。

CLASS_A

CLASS_B

使用ALL谓词时，SQL语句可以像下面这样写。

--查询比B班住在东京的所有学生年龄都小的A班学生
SELECT ＊
FROM Class_A
WHERE age < ALL ( 
    SELECT age
    FROM Class_B
    WHERE city =’东京’);

结果：

name   age   city
-----  ----  ----
拉里    19     埼玉

查询到的只有比山田年龄小的拉里，到这里都没有问题。 但是如果山田年龄不详，就会有问题了。 凭直觉来说，此时查询到的可能是比22岁的齐藤年龄小的拉里和伯杰。然而，这条SQL语句的执行结果还是空。这是因为， ALL谓词其实是多个以AND连接的逻辑表达式的省略写法。 具体的分析步骤如下所示。

--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;

NULL如此随意和混乱，想必大家都感受到了吧？

限定谓词和极值函数不是等价的

使用极值函数代替ALL谓词的人应该不少吧。如果用极值函数重写刚才的SQL，应该是下面这样。

--查询比B班住在东京的年龄最小的学生还要小的A班学生
SELECT ＊
FROM Class_A
WHERE age < ( 
    SELECT MIN(age)
    FROM Class_B
    WHERE city =’东京’);

结果：

name   age   city
-----  ----  ----
拉里    19    埼玉
伯杰    21    千叶

没有问题。即使山田的年龄无法确定，这段代码也能查询到拉里和伯杰两人。这是因为， 极值函数在统计时会把为NULL的数据排除掉 。使用极值函数能使Class_B这张表里看起来就像不存在NULL一样。

也许有人会这么想：“这样的话任何时候都使用极值函数岂不是更安全？”。然而在三值逻辑的世界里，事情没有这么简单。ALL谓词和极值函数表达的命题含义分别如下所示。

● ALL谓词：他的年龄比在东京住的所有学生都小——Q1

● 极值函数：他的年龄比在东京住的年龄最小的学生还要小——Q2

在现实世界中，这两个命题是一个意思。但是，正如我们通过前面的例子看到的那样，表里存在NULL时它们是不等价的。其实还有一种情况下它们也是不等价的，大家知道是什么吗？

答案是，谓词（或者函数）的输入为空集的情况。例如Class_B这张表为如下所示的情况。

如上表所示， B班里没有学生住在东京 。这时，使用ALL谓词的SQL语句会查询到A班的所有学生。然而，用极值函数查询时一行数据都查询不到。这是因为， 极值函数在输入为空表（空集）时会返回NULL。 因此，使用极值函数的SQL语句会像下面这样一步步被执行。

--1．极值函数返回NULL
SELECT ＊
FROM Class_A
WHERE age < NULL;
--2．对NULL使用“<”后结果为 unknown
SELECT ＊
FROM Class_A
WHERE unknown;

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

聚合函数和NULL

实际上，当输入为空表时返回NULL的不只是极值函数，COUNT以外的聚合函数也是如此。所以下面这条看似普通的SQL语句也会带来意想不到的结果。

--查询比住在东京的学生的平均年龄还要小的A班学生的SQL语句？