本文是《sql进阶教程》阅读笔记,感兴趣可以阅读该书对应章节,这本适合有一定sql基础的同学阅读。另外作者《sql基础教程》也值得一看。
一、 NOT IN 和 NOT EXISTS 不是等价的
在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN
改写成EXISTS
这是等价改写,并没有什么问题。问题在于,将 NOT IN
改写成 NOT EXISTS
时,结果未必一样
请看下面这两张班级学生表:
Class_A
name(名字) | age(年龄) | city(住址) |
---|---|---|
布朗 | 22 | 东京 |
拉里 | 19 | 埼玉 |
伯杰 | 21 | 千叶 |
Class_B
name(名字) | age(年龄) | city(住址) |
---|---|---|
齐藤 | 22 | 东京 |
田尻 | 23 | 东京 |
山田 | 东京 | |
和泉 | 18 | 千叶 |
武田 | 20 | 千叶 |
石川 | 19 | 神奈川 |
注:B 班山田的年龄是NULL
要求:与 B 班住在东京的学生年龄不同的 A 班学生” 即:希望查询到的是拉里和伯杰。因为布朗与齐藤年龄相同。
--创建表A
CREATE TABLE Class_A(
id serial PRIMARY KEY,
name VARCHAR(10),
age INT,
city VARCHAR(50)
);
INSERT INTO Class_A(name,age,city) VALUES
('布朗',22,'东京'),
('拉里',19,'埼玉'),
('伯杰',21,'千叶');
-- 创建B
CREATE TABLE Class_B(
id serial PRIMARY KEY,
name VARCHAR(10),
age INT,
city VARCHAR(50)
);
INSERT INTO Class_B(name,age,city) VALUES
('齐藤',22,'东京'),
('田尻',23,'东京'),
('山田',null,'东京'),
('和泉',18,'千叶'),
('武田',20,'千叶'),
('石川',19,'神奈川');
查询与B 班住在东京的学生年龄不同的A 班学生的SQL 语句
SELECT * FROM Class_A
WHERE age NOT IN (
SELECT age
FROM Class_B
WHERE city = '东京'
)
实际上,如果山田的年龄不是 NULL ;是存在值的,但是为什么查询结果为空呢。关键就在这个NULL上。
通过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;
*/
如果 NOT IN 子查询中用到的表里被选择的列中存在 NULL ,则 SQL 语句整体的查询结果永远是空。
为了得到正确的结果,我们需要使用 EXISTS 谓词
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 却不可以互相替换的混乱现象
二、限定谓词和 NULL
SQL 里有 ALL 和 ANY 两个限定谓词。因为 ANY 与 IN 是等价的,所以我们不经常使用 ANY。ALL 可以和比较谓词一起使用,用来表达“与所有的××都相等”,或“比所有的××都大”的意思。
给 B 班表里为 NULL 的列添上具体的值;然后,使用这张新表来思考一下用于查询“比 B 班住在东京的所有学生年龄都小的 A 班学生”的 SQL 语句。
Class_A
name(名字) | age(年龄) | city(住址) |
---|---|---|
布朗 | 22 | 东京 |
拉里 | 19 | 埼玉 |
伯杰 | 21 | 千叶 |
Class_B
name(名字) | age(年龄) | city(住址) |
---|---|---|
齐藤 | 22 | 东京 |
田尻 | 23 | 东京 |
山田 | 20 | 东京 |
和泉 | 18 | 千叶 |
武田 | 20 | 千叶 |
石川 | 19 | 神奈川 |
--注意:如果没有将null替换,则依然不能查出任何值。
SELECT * FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B WHERE city = '东京' );
三、限定谓词和极值函数不是等价的
使用极值函数代替 ALL 谓词;即使出现NULL也没关系。 除去 count(*)的聚集函数会忽略NULL
-- 即使山田的年龄为NULL。
-- 查询比B 班住在东京的年龄最小的学生还要小的A 班学生
SELECT * FROM Class_A
WHERE age < ( SELECT MIN(age)
FROM Class_B
WHERE city = '东京' );
注意:如果Class_B中没有住在东京的同学,或者查询结果为NULL。 则查询到的结果会是空。这个时候可以使用COALESCE 函数将极值函数返回的 NULL处理成合适的值。
ALL 谓词 和极值函数表达的命题含义:
ALL 谓词:他的年龄比在东京住的所有学生都小 —— Q1
极值函数:他的年龄比在东京住的年龄最小的学生还要小 ——Q2
小结
- NULL 不是值。 因为 NULL 不是值,所以不能对其使用谓词。
- 对 NULL 使用谓词后的结果是 unknown 。