SQL进阶_3

三值逻辑和 NULL

大多数编程语言都是基于二值逻辑的，即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的逻辑体系——三值逻辑，即逻辑真值除了真和假，还有第三个值“不确定”。

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

理论篇

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

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

“未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”。

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

NULL 使用比较谓词后得到的结果总是 unknown。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行，不会包含判断结果为 false 和 unknown 的行。不只是等号，对 NULL 使用其他比较谓词，结果也都是一样的。 

为了便于记忆，请注意这三个真值之间有下面这样的优先级顺序。

● AND 的情况：false ＞ unknown ＞ true 

● OR 的情况：true ＞ unknown ＞ false

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

实践篇

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

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

“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为

排中律（Law of Excluded Middle）。

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

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

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

关于这个原因，我们在理论篇里学习过，即对 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;

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

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

--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 '○'
 WHEN col_1 IS NULL THEN '×'
 END

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

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

Class_A

 

Class_B 

需求：查询与 B 班住在北京的学生年龄不同的 A 班学生

用in 实现的sql

select * from Class_A where age not in (select age from Class_B where city = '北京')

查询结果

空空如也！这是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 谓词。

select * from Class_A a where 
                                not exists
         (select 1 from Class_B b where a.age = b.age and b.city = '北京');

查询结果 

分析流程

select * from Class_A a where not exists (select 1 from Class_B b where a.age = b.age and b.city = '北京');

-- 1. 在子查询里和 NULL 进行比较运算
select * from Class_A a where not exists (select 1 from Class_B b where a.age = null and b.city = '北京');

-- 2. 对 NULL 使用“=”后，结果为 unknown
select * from Class_A a where not exists (select 1 from Class_B b where unknown and b.city = '北京');

-- 3. 如果 AND 运算里包含 unknown，结果不会是 true
select * from Class_A a where not exists (select 1 from Class_B b where unknown or false);

-- 4. 子查询没有返回结果，因此相反地，NOT EXISTS 为 true
select * from Class_A a where true;

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

4. 限定谓词和 NULL

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

Class_A

Class_B  

需求：比B 班住在北京的所有学生年龄都小的 A 班学生

用all 实现的sql

select * from Class_A 
                where
             age < all(select age from Class_B b where b.city = '北京')

实现结果 

查询到的只有比胡汉三小的李四，到这里都没有问题。但是如果胡汉三年龄不详，就会有问题了。凭直觉来说，此时查询到的可能是比 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;

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

使用极值函数写上述的sql

select * from Class_A 
            where 
        age < (select min(age) from Class_B b where b.city = '北京')

实现结果 

没有问题。即使胡汉三的年龄无法确定，这段代码也能查询到李四和王五两人。这是因为，极值函数在统计时会把为 NULL 的数据排除掉。

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

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

在现实世界中，这两个命题是一个意思。但是，正如我们通过前面的例题看到的那样，表里存在 NULL 时它们是不等价的。

其实还有一种情况下它们也是不等价的，大家知道是什么吗？

        答案是，谓词（或者函数）的输入为空集的情况。下图。

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

此时极值函数的执行顺序

-- 1. 极值函数返回 NULL
select * from Class_A where age < null;

-- 2. 对 NULL 使用“<”后结果为 unknown
select * from Class_A where unknown;

6. 聚合函数和 NULL

需求：

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

sql

select * from  Class_A where age < (select avg(age) from Class_B where city = '北京')

没有住在北京的学生时，AVG 函数返回 NULL。因此，外侧的 WHERE子句永远是 unknown，也就查询不到行。使用 SUM 也是一样。

要点！！！

1. NULL 不是值。

2. 因为 NULL 不是值，所以不能对其使用谓词。

3. 对 NULL 使用谓词后的结果是 unknown。

4. unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样。