MySQL--三值逻辑和null

本文所用的例子和语法均参考《SQL基础教程》第2版。

排中律不成立

排中律就是指不认可中间状态，对命题真伪的判断黑白分明。
首先看一个排中律不成立的例子，如果排中律成立，那么下面的查询结果应该能选出所有行。

-- 我们首先创建一个表
CREATE TABLE Students
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER );

INSERT INTO Students VALUES('布朗', 22);
INSERT INTO Students VALUES('拉里',   19);
INSERT INTO Students VALUES('约翰',   NULL);
INSERT INTO Students VALUES('伯杰', 21);

-- 查询年龄是20岁或者不是20岁的学生
mysql> select * from students
    -> where age=20 or age<>20;
+--------+------+
| name   | age  |
+--------+------+
| 伯杰   |   21 |
| 布朗   |   22 |
| 拉里   |   19 |
+--------+------+
-- 若想将所有结果都查询出来，应该使用以下语句
mysql> select * from students
    -> where age=20
    ->   or age<>20
    ->   or age is null;
+--------+------+
| name   | age  |
+--------+------+
| 伯杰   |   21 |
| 布朗   |   22 |
| 拉里   |   19 |
| 约翰   | NULL |
+--------+------+

NOT IN 和NOT EXISTS

在对sql语句进行性能优化时，经常用到的一个技巧时将in改写成exists。这是等价改写，并没有什么问题。问题在于将not in 改写成not exists时，结果未必一样。
我们有class_a和class_b两张表。

CREATE TABLE Class_A
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

CREATE TABLE Class_B
(name VARCHAR(16) PRIMARY KEY,
 age  INTEGER,
 city VARCHAR(16) NOT NULL );

INSERT INTO Class_A VALUES('布朗', 22, '东京');
INSERT INTO Class_A VALUES('拉里',   19, '埼玉');
INSERT INTO Class_A VALUES('伯杰',   21, '千叶');

INSERT INTO Class_B VALUES('齐藤',  22,   '东京');
INSERT INTO Class_B VALUES('田尻',  23,   '东京');
INSERT INTO Class_B VALUES('山田',  NULL, '东京');
INSERT INTO Class_B VALUES('和泉',  18,   '千叶');
INSERT INTO Class_B VALUES('武田',  20,   '千叶');
INSERT INTO Class_B VALUES('石川',  19,   '神奈川');

mysql> select * from class_a;
+--------+------+--------+
| name   | age  | city   |
+--------+------+--------+
| 伯杰   |   21 | 千叶   |
| 布朗   |   22 | 东京   |
| 拉里   |   19 | 埼玉   |
+--------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from class_b;
+--------+------+-----------+
| name   | age  | city      |
+--------+------+-----------+
| 和泉   |   18 | 千叶      |
| 山田   | NULL | 东京      |
| 武田   |   20 | 千叶      |
| 田尻   |   23 | 东京      |
| 石川   |   19 | 神奈川    |
| 齐藤   |   22 | 东京      |
+--------+------+-----------+

我们考虑查询”与B班住在东京的学生年龄不同 的A班学生“。也就是说我们希望查询到的结果是拉里与伯杰。按照下面的语句无法实现：

mysql> select * from class_a
    -> where age not in (select age from class_b where city='东京');
Empty set (0.04 sec)

这是为什么呢？如果not in 子句查询中用到的表里被选择的列中存在null，则SQL语句整体的查询结果永远是空。
为了得到正确结果，我们使用exists谓词。

mysql> select * from class_a a
    -> where not exists (select * from class_b b where a.age=b.age and b.city='东京');
+--------+------+--------+
| name   | age  | city   |
+--------+------+--------+
| 伯杰   |   21 | 千叶   |
| 拉里   |   19 | 埼玉   |
+--------+------+--------+

产生这样的结果是因为exists谓词永远不会返回unknown。exists只会返回true或者false。因此就有了in 和exists可以互相替换使用，而not in 和not exists却不可以互相替换的混乱现象。

限定谓词和null

将上述表格的空值填为20，查询”比B班住在东京的所有学生年龄都小的A班学生“

mysql> select * from class_a
    -> where age<all (select age from class_b where city='东京');
+--------+------+--------+
| name   | age  | city   |
+--------+------+--------+
| 拉里   |   19 | 埼玉   |
+--------+------+--------+

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

将表格恢复到”山田“的年龄为null的时候，class_a和class_b表如下所示：

-- class_a表
+--------+------+--------+
| name   | age  | city   |
+--------+------+--------+
| 伯杰   |   21 | 千叶   |
| 布朗   |   22 | 东京   |
| 拉里   |   19 | 埼玉   |
+--------+------+--------+

-- class_b表
+--------+------+-----------+
| name   | age  | city      |
+--------+------+-----------+
| 和泉   |   18 | 千叶      |
| 山田   | NULL | 东京      |
| 武田   |   20 | 千叶      |
| 田尻   |   23 | 东京      |
| 石川   |   19 | 神奈川    |
| 齐藤   |   22 | 东京      |
+--------+------+-----------+

--使用限定谓词all查询
mysql> select * from class_a
    -> where age<all(select age from class_b where city='东京');
Empty set (0.00 sec)

--使用极值函数
mysql> select * from class_a
    -> where age<(select min(age) from class_b where city='东京');
+--------+------+--------+
| name   | age  | city   |
+--------+------+--------+
| 伯杰   |   21 | 千叶   |
| 拉里   |   19 | 埼玉   |
+--------+------+--------+

即使山田的年龄无法确定，这段代码也能查询到伯杰和拉里。这是因为极值函数在统计时会把null的数据排除掉。使用极值函数能使class_b这张表里看起来就像null不存在一样。

• all谓词：他的年龄比在东京住的所有学生都小
• 极值函数：他的年龄比在东京住的年龄最小的学生还要小
  在现实世界中，这两个命题是一个意思，但是当表里存在null时他们不是等价的。还有一种情况也不是等价的，就是谓词（或者函数）的输入为空集的情况。当class_b中没有住在东京的人的时候，还是执行上述语句，使用all谓词的SQL语句会查询到A班的所有学生。然而，用极值函数查询时一行数据都查询不到。这是因为，极值函数在输入为空表（空集）时会返回null。
  比较对象原本就不存在时，根据业务需求有时需要返回所有行，有时需要返回空集。需要返回虽有行时用all谓词，或者使用coalesce函数将极值函数返回的null处理成合适的值。