Levon's Blog

微信: L6241425

0%

sql进阶教程03-三值逻辑和NULL

发表于 更新于 分类于 ， 阅读次数： Valine：
本文字数： 3.4k 阅读时长 ≈ 3 分钟

大多数编程语言都是基于二值逻辑的，即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的 逻辑体系——三值逻辑，即逻辑真值除了真和假，还有第三个值“不确定”。三值逻辑经常会带来一些意想不到的情况，这让程序员很是烦恼。

1.1 三值逻辑还是四值逻辑

SQL 里只存在 一种 NULL。然而在讨论 NULL 时，我们一般都会将它分成两种类型来思考。 因此这里先来介绍一些基础知识，即两种 NULL 之间的区别。

两种 NULL 分别指的是“未知”(unknown)和“不适用”(not applicable, inapplicable)。

  • 以“不知道戴墨镜的人眼睛是什么颜色”这种情 况为例，这个人的眼睛肯定是有颜色的，但是如果他不摘掉眼镜，别人就 不知道他的眼睛是什么颜色。这就叫作未知。

  • 而“不知道冰箱的眼睛是什 么颜色”则属于“不适用”。因为冰箱根本就没有眼睛，所以“眼睛的颜色” 这一属性并不适用于冰箱。

image-20220318162307153

Codd 曾经认为应该严格地区分两种类型的 NULL，并提倡在关系数据 库中使用四值逻辑 。现在所有的 DBMS 都将两种类型的 NULL 归为了一类并采用了三值逻辑。

1.2 IS NULL

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

1
2
3
4
5
6
--以下的式子都会被判为 unknown 

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

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

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

第二个原因是，IS NULL 这样的谓词是由两个单词构成的，所以人们 容易把 IS 当作谓词，而把 NULL 当作值。我们应该把 IS NULL 看作是一个谓词。因此，如果可以的话，写成 IS_NULL 这样也许更合适。

1.3 真值unknown

真值 unknown 和作为 NULL 的一种的 UNKNOWN(未知)是不同的东西。（前者是明确的布尔型的真值，后者既不是值也不 是变量）。

x 是真值 unknown 时，x=x 被判断为 true，而 x 是 UNKNOWN 时被判断为 unknown。

1
2
3
4
5
6
-- 这个是明确的真值的比较 
unknown = unknown 				→ true


-- 这个相当于NULL = NULL 
UNKNOWN = UNKNOWN 				→ unknown
  • SQL 遵循的三值逻辑的真值表
image-20220318163228105

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

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

  • AND 的情况: false > unknown > true
  • OR 的情况: true > unknown > false

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

1.4 比较谓词和 NULL

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

“把命题和它的否命题通 过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为 排中律(Law of Excluded Middle)。

1
2
3
-- 查询年龄是 20 岁或者不是 20 岁的学生 

SELECT * FROM Students WHERE age = 20 OR age <> 20;

遗憾的是，在 SQL 的世界里，排中律是不成立的。如果有一条存在NULL。

1
2
3
-- 添加第 3 个条件 :年龄是 20 岁，或者不是 20 岁，或者年龄未知 

SELECT * FROM Students WHERE age = 20 OR age <> 20 OR age IS NULL;
  • CASE WHEN时也要注意
1
2
3
4
5
6
7
8
9
-- col_1 为 1 时返回○、为 NULL 时返回 × 的 CASE 表达式? 

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

-- 修改成下面的

CASE WHEN col_1 = 1 THEN '○' 
WHEN col_1 IS NULL THEN '×' END
  • NOT IN 和 NOT EXISTS
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 这条 SQL 语句真的能正确地查询到NULL age 的学生吗?遗憾的是不能。 结果是空，查询不到任何数据。

SELECT * FROM Class_A
WHERE age NOT IN ( SELECT age
FROM Class_B
WHERE city = '东京' );



-- 正确的 SQL 语句: NULL age将被查询到
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = B.age AND B.city = '东京' );

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

1.5 限定谓词和极值函数对待NULL非等价

  • 限定谓词
1
2
3
4
5
 -- 查询比 B 班住在东京的所有学生年龄都小的 A 班学生
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B
WHERE city = '东京' );
  • 极值函数
1
2
3
4
5
-- 查询比 B 班住在东京的年龄最小的学生还要小的 A 班学生 
SELECT *
FROM Class_A
WHERE age < ( SELECT MIN(age) FROM Class_B
WHERE city = '东京' );
  • 总结

极值函数在统计时会把为 NULL 的数据排除掉。

  1. ALL谓词:他的年龄比在东京住的所有学生都小
  2. 极值函数:他的年龄比在东京住的年龄最小的学生还要小

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

1.6 聚合函数和 NULL

COUNT 以外 的聚合函数也是如此。

1
2
3
4
5
-- 查询比住在东京的学生的平均年龄还要小的 A 班学生的 SQL 语句? 
SELECT * FROM Class_A
WHERE age < ( SELECT AVG(age)
FROM Class_B
WHERE city = '东京' )

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

聚合函数和极值函数的这个陷阱是由函数自身带来的，所以仅靠为具体列加上 NOT NULL 约束是无法从根本上消除的。因此我们在编写 SQL 代码的时候需要特别注意。

1.7 总结

  • NULL 不是值。

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

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

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

  • 要想解决 NULL 带来的各种问题，最佳方法应该是往 表里添加NOT NULL约束来尽力排除NULL。这样就可以回到美妙的二值逻 辑世界(虽然并不能完全回到)。

给作者打赏，可以加首页微信，咨询作者相关问题！