《sql进阶教程》之三值逻辑和NULL

本文是《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 。