大多数编程语言都是基于二值逻辑的,即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的逻辑体系——三值逻辑,即逻辑真值除了真和假,还有第三个值“不确定”。
- 数据库里只要存在一个
NULL
,查询的结果就可能不正确。 - 而且,一般没有办法确定具体是哪个查询返回了不正确的结果,所以所有的结果看起来都很可疑。
- 没有谁能保证一定能从包含
NULL
的数据库里查询出正确的结果。要我说,这种情况着实令人束手无策。
布尔型(BOOL
型、BOOLEAN
型)
SQL-99 里将布尔型定义为可以由用户直接操作的数据类型。
普通语言里的布尔型只有 true
和 false
两个值,这种逻辑体系被称为二值逻辑。而 SQL 语言里,除此之外还有第三个值 unknown
,因此这种逻辑体系被称为三值逻辑(three-valued logic)。
理论篇:
NULL
正是产生三值逻辑的“元凶”。
两种 NULL
之间的区别:两种 NULL
分别指的是“未知”(unknown)和“不适用”(not applicable, inapplicable)
必须写成“IS NULL”,而不是“=NULL”
那么,为什么对 NULL
使用比较谓词后得到的结果永远不可能为真呢?这是因为,NULL
既不是值也不是变量。NULL
只是一个表示“没有值”的标记,而比较谓词只适用于值。因此,对并非值的 NULL
使用比较谓词本来就是没有意义的
比较谓词和 NULL(1):排中律不成立
-- 查询年龄是20岁或者不是20岁的学生
SELECT * FROM Students
WHERE age = 20 OR age <> 20;
如果排中律在 SQL 里也成立,那么下面的查询应该能选中表里的所有行。遗憾的是,在 SQL 的世界里,排中律是不成立的.
那么这条 SQL 语句无法查询到约翰,因为约翰年龄不详
比较谓词和 NULL(2):CASE 表达式和 NULL
下面我们来看一下在 CASE
表达式里将 NULL
作为条件使用时经常会出现的错误。首先请看下面的简单 CASE
表达式。
--col_1 为1时返回 ○、为 NULL 时返回 × 的 CASE 表达式?
CASE col_1
WHEN 1 THEN '○'
WHEN NULL THEN '×'
END
这个 CASE
表达式一定不会返回 ×。这是因为,第二个 WHEN
子句是 col_1=NULL
的缩写形式。正如大家所知,这个式子的真值永远是 unknown
。而且 CASE
表达式的判断方法与 WHERE
子句一样,只认可真值为 true
的条件。正确的写法是像下面这样使用搜索 CASE
表达式。
CASE WHEN col_1 = 1 THEN '○'
WHEN col_1 IS NULL THEN '×'
END
3. NOT IN 和 NOT EXISTS 不是等价的
在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN
改写成 EXISTS
。这是等价改写,并没有什么问题。
问题在于,将 NOT IN
改写成 NOT EXISTS
时,结果未必一样。
B 班山田的年龄是 NULL
。我们考虑一下如何根据这两张表查询“与 B 班住在东京的学生年龄不同的 A 班学生”。
也就是说,希望查询到的是拉里和伯杰。因为布朗与齐藤年龄相同,所以不是我们想要的结果。如果单纯地按照这个条件去实现,则 SQL 语句如下所示。
-- 查询与 B 班住在东京的学生年龄不同的 A 班学生的 SQL 语句?
SELECT * FROM Class_A WHERE age NOT IN ( SELECT age FROM Class_B WHERE city = '东京' );
这条 SQL 语句真的能正确地查询到这两名学生吗?遗憾的是不能。结果是空,查询不到任何数据。
实际上,如果山田的年龄不是 NULL
(且与拉里和伯杰年龄不同),是能顺利找到拉里和伯杰的。然而,这里 NULL
又一次作怪了。
我们一步一步地看看究竟发生了什么吧。
-- 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;
-- 7.如果 AND 运算里包含 unknown,则结果不为 true(参考“理论篇”中的矩阵)
SELECT * FROM Class_A WHERE FALSE 或unknown;
-- 正确的 SQL 语句:拉里和伯杰将被查询到
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
却不可以互相替换的混乱现象。