SQL进阶系列之3三值逻辑与NULL

写在前面

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

• Why SQL存在三值逻辑？

  Because of NULL

理论篇

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

两种NULL：分别指未知(unknown)和不适用(not applicable)。举例:"不知道戴眼镜的人的眼睛是什么颜色"，为unknown，"不知道冰箱的眼睛是什么颜色"为not applicable。unknown:虽然现在不知道，但加了条件就可以知道；not applicable：无论怎么努力都不知道。

关系模型的祖师爷Codd博士曾提倡用四值逻辑，所幸的是没人采用。

• 为什么必须写成"IS NULL"而不是"= NULL"

因为对NULL使用谓词后的结果总是unknown，而查询结果只会包含where子句里判断结果为true的行，不会返回包括false和unknown的行。null没有类型，null既不是值也不是变量，只是一个表示"没有值"的标记，而比较谓词只适用于值。

• unknown、第三个真值

真值unknown和作为NULL的一种的UNKNOWN(未知)是不同的东西，前者是明确的布尔型的真值，后者既不是值也不是变量，为了便于区分，unknown为逻辑真值，UNKNOWN为未知。unknown = unknown判断为True,x是UNKNOWN时被判断为unknown。

三值逻辑的优先级：

and：false --> unknown --> true

or：true --> unknown --> false

-- a = 2, b=5,c = NULL
1. a < b AND b > c ==》 true AND unknown ==》 unknown
2. a > b OR b < c ==》 false OR unknown ==》 unknown
3. a < b OR b < c ==》 true OR unknown ==》 true
4. NOT (b <> c) ==》 NOT unknown ==》 unknown

实践篇

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

• 生活中：John是20岁 或者 John不是20岁永远成立。“把命题和它的否命题通过"或者"连接而成的命题全都是真命题”这个命题在二值逻辑中被称为排中律（Law of Excluded Middle）。
• SQL里：排中律不成立

name	age
Brown	22
Larry	19
Joan
Bird	21

-- 查询年龄是20岁或者不是20岁的学生(生活逻辑则所有人会被找出来)
SELECT * FROM Students WHERE age = 20 OR age <> 20;

-- 1.John的年龄是NULL(unknown)
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;
-- SQL逻辑将会没有John

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

-- 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不是等价的

在对SQL进行性能优化时，经常用到的一个技巧是将IN改写成EXISTS，这是等价改写

Table1:

name	age	city
Brown	22	东京
Larry	19	埼玉
Bird	21	千叶

Table2:

name	age	city
Kitten	22	东京
Tag	23	东京
Sandy		东京
Ming	18	奈良
Wutian	20	奈良
Lee	19	神奈川

-- NOT IN 查询与B班住在东京的同学年龄不同的A班学生的SQL语句（得不到想要的结果）
SELECT * FROM Table1 WHERE age NOT IN (SELECT age FROM Table2 WHERE city = '东京');

-- 1.执行子查询，得到年龄列表
SELECT * FROM Table1 WHERE age NOT IN (22,23,NULL);

-- 2.用NOT 和 IN等价改写NOT IN 
SELECT * FROM Table1 WHERE NOT age IN (22,23,NULL);

-- 3.用OR等价改写谓词IN
SELECT * FROM Table1 WHERE NOT ((age = 22) or (age = 23) or (age = NULL));

-- 4.使用德摩根律等价改写
SELECT * FROM Table1 WHERE NOT (age = 22) AND NOT (age = 23) AND NOT (age = NULL);

-- 5.用<>等价改写NOT和=
SELECT * FROM Table1 WHERE age <> 22 AND age <> 23 AND age <> NULL;

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

-- 7.如果AND运算里包含unknown,结果不可能为true
SELECT * FROM Table1 WHERE false OR unknown;

-- NOT EXISTS 能给出正确结果

-- 1. 在子查询里和NULL进行比较运算
SELECT * FROM Table1 T1 WHERE NOT EXISTS (SELECT * FROM Table2 T2 WHERE T1.age = NULL AND T2.city = '东京');

-- 2. 对NULL使用"="后，结果为unknown
SELECT * FROM Table1 T1 WHERE NOT EXISTS (SELECT * FROM Table2 T2 WHERE unknown AND T2.city = '东京');

-- 3. 如果AND运算里包含unknown，结果不会是true
SELECT * FROM Table1 T1 WHERE NOT EXISTS (SELECT * FROM Table2 T2 WHERE false OR unknown);

-- 4.子查询没有返回结果，因此相反地，NOT EXISTS为true
SELECT * FROM Table1 T1 WHERE true; 
-- 最终A中所有的都被取出来，EXISTS谓词永远不会返回unknown。 因此IN和EXISTS可以互相替代使用，但NOT IN 和NOT EXISTS不能相互替代。

限定谓词与NULL

SQL中有ALL和ANY两个谓词，ANY与IN等价，因此不常用。

-- 查询比B班住在东京的所有学生年龄都小的A班学生(如果山田的年龄为NULL,得不到正确结果)
SELECT * FROM Class_A WHERE age < ALL(SELECT age FROM Class_B WHERE city = '东京');

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

-- 1.执行子查询获得年龄列表(假设上表山田年龄为NULL)
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使用"<"后，结果变为NULL
SELECT * FROM Class_A WHERE (age < 22) AND (age < 23) AND unknown;

-- 4.如果AND运算符里包含unknown，则结果不为true
SELECT * FROM Class_A WHERE false OR unknown;

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

-- 查询比B班住在东京的年龄最小的学生还要小的A班学生(本例侥幸能得到正确结果)
SELECT * FROM Class_A WHERE age < (SELECT MIN(age) FROM Class_B WHERE city = '东京');
-- 极值函数会排除NULL

• ALL谓词：他的年龄比在东京住的而所有学生都小 -Q1
• 极值函数：他的年龄比在东京住的年龄最小的学生还要小 -Q2
• ALL和极值函数不等价的情况：极值函数在输入为空表时会返回NULL

-- 1. 极值函数返回NULL
SELECT * FROM Class_A WHERE age < NULL;

-- 2. 对NULL使用"<"后结果为unknown
SELECT * FROM Class_A WHERE unknown;

聚合函数和NULL

实际上，当输入为空表时返回NULL的还不止极值函数，COUNT以外的聚合函数都是如此

-- 查询比住在东京的学生的平均年龄还要小的A班学生的SQL语句
SELECT * FROM Class_A WHERE age < (SELECT AVG(age) FROM Class_B WHERE city = '东京');
此时若子查询中avg如果为NULL，也得不到任何行。

小结

• NULL不是值，也不是变量，而是一种没有值的标记，没有值的原因分为UNKNOWN和NOT APPLICABLE两种
• 因为NULL不是值，所以不能对其使用谓词
• 对NULL使用谓词的后果是unknown
• unknown参与到逻辑运算时，SQL的运行会和预想的不太一样
• 按步骤追踪SQL的执行过程能有效应对上面的情况。