SQL的温柔陷阱:三值逻辑与NULL
数据管道
共 2322字,需浏览 5分钟
·
2020-11-04 09:11
点击上方“数据管道”,选择“置顶星标”公众号
干货福利,第一时间送达
什么是NULL
两种 NULL
为什么必须写成“IS NULL”,而不是“= NULL”
DROP TABLE IF EXISTS t_sample_null;
CREATE TABLE t_sample_null (
id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name VARCHAR(50) NOT NULL COMMENT '名称',
remark VARCHAR(500) COMMENT '备注',
primary key(id)
) COMMENT 'NULL样例';
INSERT INTO t_sample_null(name, remark)
VALUES('zhangsan', '张三'),('李四', NULL);
-- SQL 不报错,但查不出结果
SELECT * FROM t_sample_null WHERE remark = NULL;
三值逻辑
-- 这个是明确的逻辑值的比较
unknown = unknown → true
-- 这个相当于NULL = NULL
UNKNOWN = UNKNOWN → unknown
三值逻辑的逻辑值表
OR 的情况:true > unknown > false
-- 假设 a = 2, b = 5, c = NULL,下列表达式的逻辑值如下
a < b AND b > c → unknown
a > b OR b < c → unknown
a < b OR b < c → true
NOT (b <> c) → unknown
“IS NULL” 而非 “= NULL”
-- 以下的式子都会被判为 unknown
= NULL
> NULL
< NULL
<> NULL
NULL = NULL
温柔的陷阱
比较谓词和 NULL
排中律指同一个思维过程中,两个相互矛盾的思想不能同假,必有一真,即“要么A要么非A” 假设我们有学生表:t_student
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student (
id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name VARCHAR(50) NOT NULL COMMENT '名称',
age INT(3) COMMENT '年龄',
remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
primary key(id)
) COMMENT '学生信息';
INSERT INTO t_student(name, age)
VALUE('zhangsan', 25),('wangwu', 60),('bruce', 32),('yzb', NULL),('boss', 18);
SELECT * FROM t_student;
SELECT * FROM t_student
WHERE age = 20 OR age <> 20;
咋一看,这不就是查询表中全部记录吗?我们来看下实际结果
-- 1. 约翰年龄是 NULL (未知的 NULL !)
SELECT *
FROM t_student
WHERE age = NULL
OR age <> NULL;
-- 2. 对 NULL 使用比较谓词后,结果为unknown
SELECT *
FROM t_student
WHERE unknown
OR unknown;
-- 3.unknown OR unknown 的结果是unknown (参考三值逻辑的逻辑值表)
SELECT *
FROM t_student
WHERE unknown;
-- 添加 3 个条件:年龄是20 岁,或者不是20 岁,或者年龄未知
SELECT * FROM t_student
WHERE age = 20
OR age <> 20
OR age IS NULL;
CASE col_1
WHEN = 1 THEN 'o'
WHEN NULL THEN 'x'
END
这个 CASE 表达式一定不会返回 ×。这是因为,第二个 WHEN 子句是 col_1 = NULL 的缩写形式。正如我们所知,这个式子的逻辑值永远是 unknown ,而且 CASE 表达式的判断方法与 WHERE 子句一样,只认可逻辑值为 true 的条件。正确的写法是像下面这样使用搜索 CASE 表达式
CASE WHEN col_1 = 1 THEN 'o'
WHEN col_1 IS NULL THEN 'x'
END
NOT IN 和 NOT EXISTS 不是等价的
DROP TABLE IF EXISTS t_student_A;
CREATE TABLE t_student_A (
id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name VARCHAR(50) NOT NULL COMMENT '名称',
age INT(3) COMMENT '年龄',
city VARCHAR(50) NOT NULL COMMENT '城市',
remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
primary key(id)
) COMMENT '学生信息';
INSERT INTO t_student_A(name, age, city)
VALUE
('zhangsan', 25,'深圳市'),('wangwu', 60, '广州市'),
('bruce', 32, '北京市'),('yzb', NULL, '深圳市'),
('boss', 43, '深圳市');
DROP TABLE IF EXISTS t_student_B;
CREATE TABLE t_student_B (
id INT(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
name VARCHAR(50) NOT NULL COMMENT '名称',
age INT(3) COMMENT '年龄',
city VARCHAR(50) NOT NULL COMMENT '城市',
remark VARCHAR(500) NOT NULL DEFAULT '' COMMENT '备注',
primary key(id)
) COMMENT '学生信息';
INSERT INTO t_student_B(name, age, city)
VALUE
('马化腾', 45, '深圳市'),('马三', 25, '深圳市'),
('马云', 43, '杭州市'),('李彦宏', 41, '深圳市'),
('年轻人', 25, '深圳市');
* FROM t_student_B;
-- 查询与 A 班住在深圳的学生年龄不同的 B 班学生 ?
SELECT * FROM t_student_B
WHERE age NOT IN (
SELECT age FROM t_student_A
WHERE city = '深圳市'
);
我们来看下执行结果
-- 1. 执行子查询,获取年龄列表
SELECT * FROM t_student
WHERE age NOT IN(43, NULL, 25);
-- 2. 用NOT 和IN 等价改写NOT IN
SELECT * FROM t_student
WHERE NOT age IN (43, NULL, 25);
-- 3. 用OR 等价改写谓词IN
SELECT * FROM t_student
WHERE NOT ( (age = 43) OR (age = NULL) OR (age = 25) );
-- 4. 使用德· 摩根定律等价改写
SELECT * FROM t_student
WHERE NOT (age = 43) AND NOT(age = NULL) AND NOT (age = 25);
-- 5. 用<> 等价改写 NOT 和 =
SELECT * FROM t_student
WHERE (age <> 43) AND (age <> NULL) AND (age <> 25);
-- 6. 对NULL 使用<> 后,结果为 unknown
SELECT * FROM t_student
WHERE (age <> 43) AND unknown AND (age <> 25);
-- 7.如果 AND 运算里包含 unknown,则结果不为true(参考三值逻辑的逻辑值表)
SELECT * FROM t_student
WHERE false 或 unknown;
-- 正确的SQL 语句:马化腾和李彦宏将被查询到
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
SELECT * FROM t_student_A A
WHERE B.age = A.age
AND A.city = '深圳市'
);
执行结果如下
同样地,我们再来一步一步地看看这段 SQL 是如何处理年龄为 NULL 的行的
-- 1. 在子查询里和 NULL 进行比较运算,此时 A.age 是 NULL
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
SELECT * FROM t_student_A A
WHERE B.age = NULL
AND A.city = '深圳市'
);
-- 2. 对NULL 使用“=”后,结果为 unknown
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
SELECT * FROM t_student_A A
WHERE unknown
AND A.city = '深圳市'
);
-- 3. 如果AND 运算里包含 unknown,结果不会是true
SELECT * FROM t_student_B B
WHERE NOT EXISTS (
SELECT * FROM t_student_A A
WHERE false 或 unknown
);
-- 4. 子查询没有返回结果,因此相反地,NOT EXISTS 为 true
SELECT * FROM t_student_B B
WHERE true;
总结
作者:youzhibing2904
https://www.cnblogs.com/youzhibing/p/11337745.html
评论