神奇的 SQL 之温柔的陷阱 → 为什么是 IS NULL

发表于

开心一刻

开学了，表弟和同学因为打架，老师让他回去叫家长

表弟硬气的说：不用，我打得过他

老师板着脸对他说：和你打架的那位同学已经回去叫家长了

表弟犹豫了一会，依然硬气的说：可以，两个我也打得过

老师：…

1711244418401.jpg

NULL

NULL 用于表示缺失的值或遗漏的未知数据，不是某种具体类型的值

数据表中的 NULL 值表示该值所处的字段为空，值为 NULL 的字段没有值

尤其要明白的是：NULL 值与 0 或者 空字符串 是不同的！

经过这么一说明，本来不懵的你们是不是有点懵了？

谁来都得懵.jpg

懵就对了，不把你们搞懵，我没法进行下文呀！

两种 NULL

两种 NULL？

SQL 里不是只存在一种 NULL 吗？

你们的脑中闪过以上问题，是非常正常的，但请不要怀疑 两种NULL 的真实性，也不要恐慌你们认知中的正确性，慢慢往下看，这些疑虑都会消除

在讨论 NULL 时，我们一般都会将它分成两种类型来思考：未知（unknown）和 不适用（not applicable,inapplicable），我们来看一个例子，大家就明白这两种类型了

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

冰箱的眼睛的颜色 这种说法和 圆的体积、 男性的分娩次数 一样，都是没有意义的、不适用的

平时，我们习惯了说 不知道，但是 不知道 也分很多种；不适用 这种情况下的 NULL ，在语义上更接近于 无意义，而不是 不确定

这里总结一下：未知 指的是 虽然现在不知道，但加上某些条件后就可以知道；而 不适用 指的是 无论怎么努力都无法知道

关系模型的发明者 E.F. Codd 最先给出了这种分类

NULL 分类.png

通过这么一解释，大家对 两种 NULL 是否认可了？

为什么 IS NULL 而非 ＝ NULL

假设我们有表 t_sample_null

1
2
3
4
5
6
7
8
9
10
sql复制代码DROP TABLE IF EXISTS t_sample_null;
CREATE TABLE t_sample_null (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    remark VARCHAR(500) COMMENT '备注',
    primary key(id)
) COMMENT 'NULL样例';

INSERT INTO t_sample_null(name, remark)
VALUES('zhangsan', '张三'),('李四', NULL);

要查询备注为 NULL 的记录，SQL 该如何写？

很多新手小伙伴觉得这还不简单，秒秒钟写出如下 SQL

1
sql复制代码SELECT * FROM t_sample_null WHERE remark = NULL;

一执行会发现：SQL 不报错，但查不出结果

为什么查不出结果，不仅很多新手小伙伴有这样的问题，很多老手其实也没有弄清楚其中的缘由，他们只知道正确的写法是 IS NULL 而不能用 = NULL

1
sql复制代码SELECT * FROM t_sample_null WHERE remark IS NULL;

为什么 IS NULL 而非 ＝ NULL，问题我已经抛出来了，至于原因，我们继续往下看

三值逻辑

看到这个标题，你们第一反应是什么？

觉得我写错了，对不对？

自信点.jpg

你们不这么 自信，怎么体现这篇文章的价值？

有点扯远了，往回收一收

三目运算 你们知道，二值逻辑(true 和 false)你们也知道，但从没听过 三值逻辑

没听过不代表她不存在，在主流的编程语言中（C、JAVA、Python、JS 等）中，逻辑值确实只有 2 个：true 和 false，但在 SQL 中却还存在第三个逻辑值：unknown，这有点类似于我们平时所说的：对、错、不知道

逻辑值 unknown 和作为 NULL 的一种的 UNKNOWN （未知）是不同的东西，前者是明确的布尔型的逻辑值，后者既不是值也不是变量；为了便于区分，前者采用小写字母 unknown ，后者用大写字母 UNKNOWN 来表示

为了让大家理解两者的不同，我们来看一个 x=x 这样的简单等式；x 是逻辑值 unknown 时，x=x 被判断为 true ，而 x 是 UNKNOWN 时被判断为 unknown

1
2
3
4
5
sql复制代码-- 这个是明确的逻辑值的比较
unknown = unknown → true

-- 这个相当于NULL = NULL
UNKNOWN = UNKNOWN → unknown

逻辑值表

因为有了 unknow 这个逻辑值，逻辑运算的情况就比二值逻辑复杂

NOT

NOT 逻辑值.png

AND

AND 逻辑值.png

OR

OR 逻辑值.png

图中蓝色部分是三值逻辑中独有的运算，这在二值逻辑中是没有的

其余的 SQL 谓词全部都能由这三个逻辑运算组合而来

从这个意义上讲，这个几个逻辑表可以说是 SQL 的 母体（matrix）

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

下面的逻辑运算，你们算对了吗？

1
2
3
4
5
6
sql复制代码-- 假设 a = 2, b = 5, c = NULL，下列表达式的逻辑值如下

a < b AND b > c  → unknown
a > b OR b < c   → unknown
a < b OR b < c   → true
NOT (b <> c)     → unknown

IS NULL 而非 = NULL

我们再回到问题：为什么 IS NULL 而非 ＝ NULL

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

不只是等号，对 NULL 使用其他比较谓词，结果也都是一样的

所以无论 remark 是不是 NULL ，比较结果都是 unknown ，那么永远没有结果返回

以下的式子都会被判为 unknown

1
2
3
4
5
6
sql复制代码-- 以下的式子都会被判为 unknown
= NULL
> NULL
< NULL
<> NULL
NULL = NULL

这下大家都清楚其中缘由了吧？

640 (4).png

但是，注意，但是来了，大家打起精神来！

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

列的值为 NULL 、NULL 值 这样的说法本身就是错误的，因为 NULL 不是值，所以不在定义域（domain）中

相反，如果有人认为 NULL 是值，那么我们可以倒过来想一下：它是什么类型的值？

关系数据库中存在的值必然属于某种类型，比如字符型或数值型等，所以，假如 NULL 是值，那么它就必须属于某种类型

NULL 容易被认为是值的原因有两个

第一个原因是高级编程语言里面，NULL 被定义为了一个常量（很多语言将其定义为了整数 0），这导致了我们的混淆。但是，SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西

第二个原因是 IS NULL 这样的谓词是由两个单词构成的，所以我们容易把 IS 当作谓词，而把 NULL 当作值。特别是 SQL 里还有 IS TRUE 、IS FALSE 这样的谓词，我们由此类推，从而这样认为也不是没有道理。但是正如讲解标准 SQL 的书里提醒人们注意的那样，我们应该把 IS NULL 看作是一个谓词。因此，写成 IS_NULL 这样也许更合适

说了这么多，是不是又触及到你们的知识盲区了？

学无止境.jpg

温柔的陷阱

理论已经讲完了，接下来我们结合实例来看看 NULL 的陷阱

比较谓词和 NULL

排中律不成立

排中律 指同一个思维过程中，两个相互矛盾的思想不能同假，必有一真，即 要么A要么非A
很多编程语言中，排中律 是成立的，但是 SQL 中还是如此吗？

假设我们有学生表：t_student

1
2
3
4
5
6
7
8
9
10
11
12
13
sql复制代码DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18);

SELECT * FROM t_student;

表中数据 yzb 的 age 是 NULL，也就是说 yzb 的年龄未知

在现实世界里，yzb 是 20 岁，或者不是 20 岁，二者必居其一，这毫无疑问是一个真命题

那么在 SQL 的世界里了，排中律 还适用吗? 我们来看一个 SQL

1
2
sql复制代码SELECT * FROM t_student
WHERE age = 20 OR age <> 20;

乍一看，这不就是查询表中全部记录吗？

我们来看下实际结果

排中律.gif

yzb 这条记录竟然没查出来！

20230115143818.png

这是为什么呢？

我们来分析下，yzb 的 age 是 NULL，那么这条记录的判断步骤如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql复制代码-- 1. 约翰年龄是 NULL (未知的 NULL !)
SELECT *
FROM t_student
WHERE age = NULL
OR age <> NULL;

-- 2. 对 NULL 使用比较谓词后，结果为unknown
SELECT *
FROM t_student
WHERE unknown
OR unknown;

-- 3．unknown OR unknown 的结果是unknown (参考三值逻辑的逻辑值表)
SELECT *
FROM t_student
WHERE unknown;

SQL 语句的查询结果里只有判断结果为 true 的行，所以 yzb 这条记录没查出来

要想让 yzb 这条记录出现在结果里，需要添加下面这样的第 3 个条件

1
2
3
4
5
sql复制代码-- 添加 3 个条件：年龄是20 岁，或者不是20 岁，或者年龄未知
SELECT * FROM t_student
WHERE age = 20 
    OR age <> 20
    OR age IS NULL;

CASE 表达式和 NULL

简单 CASE 表达式如下

1
2
3
4
sql复制代码CASE col_1
    WHEN = 1 THEN 'o'
    WHEN NULL THEN 'x'
END

这个 CASE 表达式一定不会返回 ×，没问题吧？

这是因为，第二个 WHEN 子句是 col_1 = NULL 的缩写形式；正如我们所知，这个式子的逻辑值永远是 unknown ，而且 CASE 表达式的判断方法与 WHERE 子句一样，只认可逻辑值为 true 的条件

正确的写法是像下面这样使用搜索 CASE 表达式

1
2
3
sql复制代码CASE WHEN col_1 = 1 THEN 'o'
    WHEN col_1 IS NULL THEN 'x'
END

NOT IN 和 NOT EXISTS 不等价

我们在对 SQL 语句进行性能优化时，经常用到的一个技巧是将 IN 改写成 EXISTS ，这是等价改写，并没有什么问题

但是，将 NOT IN 改写成 NOT EXISTS 时，结果未必一样

我们来看个例子，我们有如下两张表：t_student_A 和 t_student_B，分别表示 A 班学生与 B 班学生

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
sql复制代码DROP TABLE IF EXISTS t_student_A;
CREATE TABLE t_student_A (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(50) NOT NULL COMMENT '城市',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 60, '广州市'),
('bruce', 32, '北京市'),('yzb', NULL, '深圳市'),
('boss', 43, '深圳市');

DROP TABLE IF EXISTS t_student_B;
CREATE TABLE t_student_B (
    id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    name VARCHAR(50) NOT NULL COMMENT '名称',
    age INT(3) COMMENT '年龄',
    city VARCHAR(50) NOT NULL COMMENT '城市',
    remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
    primary key(id)
) COMMENT '学生信息';

INSERT INTO t_student_B(name, age, city)
VALUE
('马化腾', 45, '深圳市'),('马三', 25, '深圳市'),
('马云', 43, '杭州市'),('李彦宏', 41, '深圳市'),
('年轻人', 25, '深圳市');

SELECT * FROM t_student_A;
SELECT * FROM t_student_B;

需求：查询与 A 班住在深圳的学生年龄不同的 B 班学生，也就说查询出 ：马化腾 和 李彦宏

这个 SQL 该如何写？

有小伙伴觉得，这还不简单？秒秒钟就写出如下 SQL

1
2
3
4
5
6
sql复制代码-- 查询与 A  班住在深圳的学生年龄不同的 B 班学生 ?
SELECT * FROM t_student_B
WHERE age NOT IN (
    SELECT age FROM t_student_A 
    WHERE city = '深圳市'
);

你执行下就会发现结果并非如你所想

not_in_not_exists.gif

竟然没有查到任何数据！

640 (2).jpg

这是为什么呢？

其实又是 NULL 开始作怪了，我们一步一步来看看究竟发生了什么

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
sql复制代码-- 1. 执行子查询，获取年龄列表
SELECT * FROM t_student
WHERE age NOT IN(43, NULL, 25);

-- 2. 用 NOT 和 IN 等价改写NOT IN
SELECT * FROM t_student
WHERE NOT age IN (43, NULL, 25);

-- 3. 用 OR 等价改写谓词 IN
SELECT * FROM t_student
WHERE NOT ( (age = 43) OR (age = NULL) OR (age = 25) );

-- 4. 使用 德·摩根定律 等价改写
SELECT * FROM t_student
WHERE NOT (age = 43) AND NOT(age = NULL) AND NOT (age = 25);

-- 5. 用 <> 等价改写 NOT 和 =
SELECT * FROM t_student
WHERE (age <> 43) AND (age <> NULL) AND (age <> 25);

-- 6. 对 NULL 使用 <> 后，结果为 unknown
SELECT * FROM t_student
WHERE (age <> 43) AND unknown AND (age <> 25);

-- 7．如果 AND 运算里包含 unknown，则结果不为true（参考三值逻辑的逻辑值表）
SELECT * FROM t_student
WHERE false 或 unknown;

可以看出，在进行了一系列的转换后，没有一条记录在 WHERE 子句里被判断为 true

也就是说，如果 NOT IN 子查询中用到的表里被选择的列中存在 NULL ，则 SQL 语句整体的查询结果永远是空。这是很可怕的现象！

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

1
2
3
4
5
6
7
sql复制代码-- 正确的SQL 语句：马化腾和李彦宏将被查询到
SELECT * FROM t_student_B B
WHERE NOT EXISTS ( 
    SELECT * FROM t_student_A A
    WHERE B.age = A.age
    AND A.city = '深圳市' 
);

执行结果如下

not_exists.gif

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
sql复制代码-- 1. 在子查询里和 NULL 进行比较运算，此时 A.age 是 NULL
SELECT * FROM t_student_B B
WHERE NOT EXISTS ( 
    SELECT * FROM t_student_A A
    WHERE B.age = NULL
    AND A.city = '深圳市' 
);

-- 2. 对 NULL 使用 = 后，结果为 unknown
SELECT * FROM t_student_B B
WHERE NOT EXISTS ( 
    SELECT * FROM t_student_A A
    WHERE unknown
    AND A.city = '深圳市' 
);

-- 3. 如果 AND 运算里包含 unknown，结果不会是true
SELECT * FROM t_student_B B
WHERE NOT EXISTS ( 
    SELECT * FROM t_student_A A
    WHERE false 或 unknown
);

-- 4. 子查询没有返回结果，因此相反地，NOT EXISTS 为 true
SELECT * FROM t_student_B B
WHERE true;

也就是说，yzb 被作为 与任何人的年龄都不同的人 来处理了

EXISTS 只会返回 true 或者 false，永远不会返回 unknown，因此就有了 IN 和 EXISTS 可以互相替换使用，而 NOT IN 和 NOT EXISTS 却不可以互相替换的混乱现象

还有一些其他的陷阱，比如：限定谓词 和 NULL、限定谓词 和 极值函数 不是等价的、聚合函数 和 NULL 等等

分析方法我已经教给你们了，你们要会成长起来！

父辈30 我还没30.jpg

总结

1、NULL 用于表示缺失的值或遗漏的未知数据，不是某种具体类型的值，不能对其使用谓词

2、对 NULL 使用谓词后的结果是 unknown，unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样

3、IS NULL 整个是一个谓词，而不是：IS 是谓词，NULL 是值；类似的还有 IS TRUE、IS FALSE

4、要想解决 NULL 带来的各种问题，最佳方法应该是往表里添加 NOT NULL 约束来尽力排除 NULL 

我的项目中有个硬性规定：所有字段必须是 `NOT NULL`，建表的时候就加上此约束

参考

《SQL进阶教程》

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%