SQL 中的三值逻辑

1. 前言

大多数编程言语都是基于二值逻辑的，即逻辑真值只要真和假两个。而 SQL 言语则采用一种特别的逻辑系统——三值逻辑，即逻辑真值除了真和假，而 SQL 言语里，除此之外还有第三个值 unknown ，因而这种逻辑系统被称为三值逻辑（three-valued-logic）。

2. 两种 Null

1. 表明不知道 (unknown)：“不知道戴墨镜的人眼睛是什么色彩”这种状况为例，这个人的眼睛肯定是有色彩的，可是假设他不摘掉眼镜，他人就不知道他的眼睛是什么色彩。这就叫作不知道。
2. 表明不适用 (not applicable 或 inapplicable)：“不知道冰箱的眼睛是什么色彩”则归于“不适用”。由于冰箱根本就没有眼睛，所以“眼睛的色彩”这一属性并不适用于冰箱。

“冰箱的眼睛的色彩”这种说法和“圆的体积”“男性的临产次数”相同，都是没有意义的。平常，咱们习惯了说“不知道”，可是“不知道”也分很多种。“不适用”这种状况下的 NULL ，在语义上更接近于“无意义”，而不是“不确定”。

总结：“不知道”指的是“尽管现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“没有办法知道”。

3. 为什么是 is Null 而不是 = Null ？

“咱们先从界说一个表明‘尽管丢失了，但却适用的值’的符号开端。咱们把它叫作 A-Mark。这个符号在联系数据库里既不被当作值（value），也不被当作变量 （variable）。”（E.F. Codd，The Relational Model for Database Management ：Version 2 , P.173） “关于 NULL 的很重要的一件事情是，NULL 并不是值。”（C.J. Date， An Intruction To Database System （6th edition ）, P.619）

对 NULL 运用比较谓词后得到的成果总是 unknown 。而查询成果只会包括 WHERE 子句里的判别成果为 true 的行，不会包括判别成果为 false 和 unknown 的行。不仅仅等号，对 NULL 运用其他比较谓词，成果也都是相同的。

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

那么，为什么对 NULL 运用比较谓词后得到的成果永久不可能为真呢？这是由于，NULL 既不是值也不是变量。NULL 仅仅一个表明“没有值”的符号，而比较谓词只适用于值。因而，对并非值的 NULL 运用比较谓词本来便是没有意义的。(Null仅仅一个作为区别的符号，并不是一个值)

“列的值为 NULL ”“NULL 值”这样的说法自身便是错误的。由于 NULL 不是值，所以不在界说域（domain）中。相反，假设有人认为 NULL 是值，那么请区别一下：它是什么类型的值？联系数据库中存在的值必然归于某种类型，比如字符型或数值型等。所以，假设 NULL 是值，那么它就有必要归于某种类型。（ SQL 里的 NULL 和其他编程言语里的 NULL 是彻底不同的东西）

4. 第三个真值 “unknown”

因联系数据库采用了 NULL 而被引入了 “第三个真值”。这儿有一点需求注意：真值 unknown 和作为 NULL 的一种的 UNKNOWN （不知道）是不同的东西。前者是清晰的布尔型的真值，后者既不是值也不是变量。（下文运用 unknown 表明 真值，UNKNOWN 表明 代表Null的一个 符号）

举个栗子： unknown = unknown 断定为 true 而 UNKNOWN = UNKNOWN ( 也便是 Null = Null ) 断定为 unknown

5. 包括三值逻辑的真值表

当两个值进行逻辑判别的时分的优先级，优先级高的真值会决议计算成果：

• AND 的状况： false ＞ unknown ＞ true
• OR 的状况： true ＞ unknown ＞ false

举个栗子： true AND unknown ，由于 unknown 的优先级更高，所以成果是 unknown 。而 true OR unknown 的话，由于 true 优先级更高，所以成果是 true 。

6. “排中律” 不再建立

“把出题和它的否出题通过‘或许’衔接而成的出题全都是真出题” 这个出题在二值逻辑中被称为排中律（Law of Excluded Middle）。顾名思义，排中律便是指不认可中间状态，对出题真伪的断定一清二楚，是古典逻辑学的重要原理。

举个栗子：现实生活中 一个学生 是20岁 或许 不是20岁，不会有第三种状况。可是在SQL中并不是这个样：

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

在现实生活中，上面的查询条件应该包括一切的学生，可是这儿的履行成果并不会查询到约翰。

--- 当查询到约翰哪一行时的断定
WHERE Null = 20 OR Null <> 20
--- 依据上文的描述，该条件会转换为
WHERE unknown OR unknown   等同于 WHERE unknown

若要查到一切学生需求再加上一个条件：OR age IS NULL

7. CASE 表达式和 NULL

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

上面的这个CASE表达式会在 col_1 为 1 时回来 ○ 、为 NULL 时回来 吗？显然一直不会回来 x 由于第二个 WHEN 子句是 col_1 = NULL 的缩写方式，依据上文的描述 col_1 = NULL 一直会回来 unknown 而 CASE 表达式的判别办法与 WHERE 子句相同，只认可真值为 true 的条件，所以 x 并不会呈现。下面才是正确的写法：

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

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

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

查询 “与 B 班住在东京的学生年纪不同的 A 班学生” 。也便是说，期望查询到的是拉里和伯杰。

-- 查询与 B 班住在东京的学生年纪不同的 A 班学生的 SQL 句子
SELECT *
FROM Class_A
WHERE age NOT IN ( SELECT age FROM Class_B WHERE city = '东京' );

这条 SQL 句子真的能正确地查询到这两名学生吗？惋惜的是不能。成果是空，查询不到任何数据。依据前文所说的规矩推导一下吧：

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

所以 上述查询句子 查询不到任何数据。为了得到正确的成果，需求运用 EXISTS 谓词。

-- 正确的SQL 句子：拉里和伯杰将被查询到
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = B.age AND B.city = '东京' );
--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;

9. 限定谓词和 NULL

-- 查询比 B 班住在东京的一切学生年纪都小的A 班学生 这儿会正常回来 拉里
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B WHERE city = '东京' );

假设山田年纪不详，就会有问题了。

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

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

将 9 中的表 Class_B 中 山田的年纪改为Null，履行下面的查询

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

这儿仍能正确查询出拉里和伯杰，这是由于，极值函数在统计时会把为 NULL 的数据排除掉。运用极值函数能使 Class_B 这张表里看起来就像不存在 NULL 相同。

区别意义：

1. ALL 谓词：他的年纪比在东京住的一切学生都小 Q1
2. 极值函数：他的年纪比在东京住的年纪最小的学生还要小 Q2

Q1 和 Q2 不等价的状况：

1. 表里存在 NULL 时它们是不等价的
2. 谓词（或许函数）的输入为空集的状况

这儿阐明一下状况2：B 班里没有学生住在东京。这时，运用 ALL 谓词的SQL 句子会查询到 A 班的一切学生。然而，用极值函数查询时一行数据都查询不到。这是由于，极值函数在输入为空表（空集）时会回来 NULL 。

--1. 极值函数回来NULL
SELECT *
FROM Class_A
WHERE age < NULL;
--2. 对NULL 运用“<”后成果为 unknown
SELECT *
FROM Class_A
WHERE unknown;

11. 聚合函数和 Null

实际上，当输入为空表时回来 NULL 的不仅仅极值函数，COUNT 以外的聚合函数也是如此。

-- 查询比住在东京的学生的平均年纪还要小的A 班学生的SQL 句子？
SELECT *
FROM Class_A
WHERE age < ( SELECT AVG(age) FROM Class_B WHERE city = '东京' );

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