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的执行过程能有效应对上面的情况。