算法工程师-SQL进阶：温柔的陷阱-NULL

我们以往遇到的编程语言基本都是基于二值逻辑的，即逻辑真值只有true和false两个。而 SQL 语言则采用一种特殊的逻辑体系——三值逻辑，逻辑真值除了true和false，还有第三个值unknow，即 “不确定”。三值逻辑经常会给数据分析带来一些意想不到的难题，即使是资深的工程师，有时候也很难避免。

接下来，本节就介绍一下：SQL的温柔杀手-NULL

一、NULL怎么了

上面已经提到了，在 SQL 语言里，除true和false外，还有第三个逻辑值unknown，这种逻辑体系被称为三值逻辑。原因就在于，SQL里引进了 NULL，所以不得不同时引进第三个值。

1、NULL的两种状态

两 种 NULL 分 别 指 的 是“ 未 知 ”（UNKNOWN） 和“ 不 适 用 ”。

举个例子：

（1）戴墨镜的这个人的眼球是什么颜色的？因为这个人没摘掉眼镜，所以不知道他的眼睛是什么颜色，但他的眼睛肯定是一种颜色。这种情况叫做：“ 未 知 ”

（2）这个男的生了几次孩子？因为男人不能生孩子，所以生孩子不能适用于男人，这个问题是无意义的，这种情况叫做：“ 不 适 用 ”

这两种情况，在现在的SQL版本中统称为NULL。

2、NULL不是一种值

你有没有想过，为什么以下的式子都会被判为 unknown？

1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

这是因为，NULL 既不是值(value)也不是变量(variable)。NULL 只是一个表示“没有值”的标记，而比较谓词只适用于值。因此，对并非值的 NULL 使用比较谓词本来就是没有意义的 。

因此，对 NULL 使用比较谓词后得到的结果总是 unknown。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行，不会包含判断结果为 false 和 unknown 的行。判断是否为NULL，应该用谓词：IS NULL。

3、第三个真值：unknown

这里引入第三个真值，unknown，注意，unknown和前面说的NULL的一种情况：'UNKNOWN'(未知)是不一样的，unknown是与true和false概念一样的逻辑真值，而'UNKNOWN'的统称是NULL，它与比较谓词结合使用时的结果是：布尔值unknown

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

接下来看一下，三值逻辑的真值表：

NOT：

not true    →  false
not false   →  true
not unknown →  unknown

AND

true and true       →  true
true and false      →  false
true and unknown    →  unknown
false and unknown   →  false
false and false     →  false
unknown and unknown →  unknown

OR

true or true       →  true
true or false      →  true
true or unknown    →  true
false or unknown   →  unknown
false or false     →  false
unknown or unknown →  unknown

注意思考真值表里，unknown参与过的计算，为什么有的结果是确定的，有的还是unknown？

二、NULL有哪些陷阱

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

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

大家觉得正确吗？没错，在现实世界中毫无疑问这是个真命题。只要是人，就有年龄，只要有年龄，要么是

20 岁，要么不是 20 岁，不可能有别的情况。

像这样，“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为：排中律。顾名思义，排中律就是指不认可中间状态，对命题真伪的判定黑白分明。

但在SQL中，排中律不成立。

eg1: 有下面一张学生成绩单，记录了4个学生的语文成绩。如果想把及格和不及格的所有学生都查出来，下面SQL正确吗？

查询SQL：

SELECT name
  FROM Score2
 WHERE score < 60
    OR score >= 60

执行结果是：只有琪琪、倩倩和蓉蓉，涛涛会查不出来。

按理说及格和不及格加起来应该是所有同学啊，现在为什么少一个呢，原来是表里存在一个score为null的学生。

当SQL查到这一行时，score is null ，此时where条件：score<60 or score>=60 的判断结果会是什么呢？

NULL < 60 or NULL >= 60  →   unknown or unknown  →  unknown

也就是说，score is null的行的逻辑值会被判断为unknown，而where只返回结果为true的行，因此，最终结果会少一行。可以看出，当NULL存在时，SQL中的排中律就不成立了，切记~

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

eg2: 小明学习了case表达式后写了下面一段SQL，逻辑是：当col_1 为 1 时返回○、为 NULL 时返回 × ，你觉得能实现他想要的效果吗？

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

答案是：不能。

首先，这是一段简单的case表达式写法，会将col_1的值从上到下依次与when的值进行比较，一旦符合就break，不会再进行下面的分支判断。值得注意的是，这里是进行值的比较，看第二个条件分支，翻译过来就是：

when col_1 = NULL，前面我们已经说得很清楚了，NULL不是值，参与任何比较运算都是没有意义的，返回的逻辑真值都是unknown，因此，第二个分支永远不会触达。

怎么改呢？只要将NULL 改成 IS NULL，就可以了。

另外，case表达式最好有兜底逻辑，即，在END之前，把ELSE NULL 写上。

3、 NOT IN 陷阱

eg3: 有如下两个学生住址表，分别属于不同的班级。现在想找出：与clazz2中位于海淀区的学生年龄不同的所有clazz1班的学生。

你觉得这个SQL能实现吗？

SELECT name
  FROM clazz1
 WHERE age NOT IN
       ( SELECT age
           FROM clazz2
          WHERE city ='海淀区' );

答案是：NO！

原因是，上面的SQL中，子查询的结果中有有NULL存在，当使用谓词 not in 时，如果 in的对象中存在NULL，那其处理逻辑应该是这样的：

age not in (value1,null) → age != value1 and age != null  →  age != value1 and unknown  →  unknown 

因为null参与任何比较运算结果都unknown，unknown参与and运算的结果不可能为true，而where子句只会把逻辑值为true的筛选出来，因此，这个SQL的执行结果一定是空。

拯救方法是：用not exists 代替 not in 。

SELECT name
  FROM clazz1
 WHERE NOT EXISTS
      ( SELECT *
         FROM clazz2
        WHERE clazz1.age = clazz2.age
          AND clazz2.city ='海淀区' );

where子句处理NULL行的逻辑是：

（1）WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE clazz1.age = NULL
       AND clazz2.city ='海淀区');
（2）WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE unknown
       AND clazz2.city ='海淀区');
（3）WHERE NOT EXISTS
    (SELECT *
     FROM clazz2
     WHERE unknown or false; -- 子查询的结果是空行
（4）WHERE true; -- 子查询没有返回结果，因此相反地，NOT EXISTS 为 true

因此，age为null的文生同学，与任何人的年龄比较后的结果都是true，即："文生与任何人的年龄都不同"这句话总是对的。因此，这个null不会对整体结果产生任何影响。

可见，in虽然与exists可以互换，但是not in与not exists是不一样的哦~~

4、 ALL 与 NULL

ALL 谓词其实是多个以 AND 连接的逻辑表达式的省略写法，比如:

age < ALL(1,2,3) 就等价于：age < 1 AND age < 2 AND age < 3

想一想，当ALL的列表参数中，存在NULL，结果会怎么样？

eg4: 这里复用eg3中两个班的学生住址表，但是求的是：比clazz2中位于海淀区的学生年龄都小的所有clazz1班的学生。请看下面这个SQL能实现吗？

SELECT name
  FROM clazz1
 WHERE age < ALL
    ( SELECT age
       FROM clazz2
      WHERE city ='海淀区' );

答案是：不能！结果还是空。

相信聪明的读者已经能找到原因了，因为，子查询的结果中存在NULL，和前面的NOT IN 类似，ALL 运算实际执行时也是与每个列表的元素一一比较，然后进行AND的运算，最终结果不是false就是unknown。因此，where条件不会筛出任何一个结构，最终肯定返回空集。

补救方法是：用聚合函数代替ALL

SELECT name
  FROM clazz1
 WHERE age <
      ( SELECT MIN(age)
          FROM clazz2
          WHERE city ='海淀区' );

如果要比所有这些人的年龄都小，只需要比最小的年龄还小就行了啊。

这个为什么能正常呢？因为除count外的所有聚合函数都可以自动过滤掉NULL值。

5、聚合函数 与 NULL

eg5: 还是复用eg3中两个班的学生住址表，但是求的是：比clazz2中位于海淀区的学生的平均年龄小的所有clazz1班的学生。请看下面这个SQL能实现吗？

SELECT name
  FROM clazz1
 WHERE age <
      ( SELECT AVG(age)
          FROM clazz2
         WHERE city ='海淀区' );

答案是：可以。终于有一个能实现的了~.~

很显然，和eg4一样，AVG也是聚合函数，可以自动过滤null行，因此结果不受影响。

但是，你有没有想过，如果海淀区的学生的age全是null，结果会怎样呢？

如果聚合函数将null行过滤后，结果为空集合，或者传入聚合函数本身的集合本身就是空集，聚合函数会返回NULL。

那上面的SQL语句就会这样执行：

(1) WHERE age <
    (SELECT AVG(age)
     FROM clazz2
     WHERE city ='海淀区');
(2) WHERE age < NULL;
(3)  WHERE unknown;

因此，如果clazz2中一个海淀区的学生都没有，上面SQL的查询结果是空集。

三、总结

本节给大家讲解了，SQL中最最让人头疼的温柔陷阱：NULL。

简单总结一下：

• NULL 不是值，而是一种标记；
• 因为 NULL 不是值，所以不能对其使用谓词；
• 对NULL 使用谓词后的结果是 unknown；
• unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样；
• 按步骤追踪 SQL 的执行过程能有效应对 4 中的情况。

另外，要想避免 NULL 带来的各种问题，方法如下：

• 往表里添加 NOT NULL 约束来尽力排除 NULL；
• 如果可以的话，在where条件中可以先把null行过滤掉；
• 注意NOT IN 陷阱 和 ALL 陷阱，一定程度上，可以用NOT EXISTS 和 聚合函数解决。