一文详解SQL 中的三值逻辑

 

1. 前言

大多数编程语言都是基于二值逻辑的,即逻辑真值只有真和假两个。而 SQL 语言则采用一种特别的逻辑体系——三值逻辑,即逻辑真值除了真和假,而 SQL 语言里,除此之外还有第三个值unknown,因此这种逻辑体系被称为三值逻辑(three-valued-logic)。

 

2. 两种 Null

  • 表示未知 (unknown):“不知道戴墨镜的人眼睛是什么颜色”这种情况为例,这个人的眼睛肯定是有颜色的,但是如果他不摘掉眼镜,别人就不知道他的眼睛是什么颜色。这就叫作未知。
  • 表示不适用 (not applicable 或 inapplicable):“不知道冰箱的眼睛是什么颜色”则属于“不适用”。因为冰箱根本就没有眼睛,所以“眼睛的颜色”这一属性并不适用于冰箱。

“冰箱的眼睛的颜色”这种说法和“圆的体积”“男性的分娩次数”一样,都是没有意义的。平时,我们习惯了说“不知道”,但是“不知道”也分很多种。“不适用”这种情况下的 NULL ,在语义上更接近于“无意义”,而不是“不确定”。

总结:“未知”指的是“虽然现在不知道,但加上某些条件后就可以知道”;而“不适用”指的是“没有办法知道”。

 

3. 为什么是 is Null 而不是 = Null ?

“我们先从定义一个表示‘虽然丢失了,但却适用的值’的标记开始。我们把它叫作 A-Mark。这个标记在关系数据库里既不被当作值(value),也不被当作变量 (variable)。”(E.F. Codd,The Relational Model for Database Management :Version 2 , P.173) “关于 NULL 的很重要的一件事情是,NULL 并不是值。”(C.J. Date, An Intruction To Database System (6th edition ), P.619)

对 NULL 使用比较谓词后得到的结果总是 unknown 。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行,不会包含判断结果为 false 和 unknown 的行。不只是等号,对 NULL 使用其他比较谓词,结果也都是一样的。

-- 以下的式子都会被判为 unknown
1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

那么,为什么对 NULL 使用比较谓词后得到的结果永远不可能为真呢?这是因为,NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的标记,而比较谓词只适用于值。因此,对并非值的 NULL 使用比较谓词本来就是没有意义的。(Null只是一个作为区分的标记,并不是一个值)

“列的值为 NULL ”“NULL 值”这样的说法本身就是错误的。因为 NULL 不是值,所以不在定义域(domain)中。相反,如果有人认为 NULL 是值,那么请区分一下:它是什么类型的值?关系数据库中存在的值必然属于某种类型,比如字符型或数值型等。所以,假如 NULL 是值,那么它就必须属于某种类型。( SQL 里的 NULL 和其他编程语言里的 NULL 是完全不同的东西)

 

4. 第三个真值 “unknown”

因关系数据库采用了 NULL 而被引入了 “第三个真值”。这里有一点需要注意:真值 unknown 和作为 NULL 的一种的 UNKNOWN (未知)是不同的东西。前者是明确的布尔型的真值,后者既不是值也不是变量。(下文使用 unknown 表示 真值,UNKNOWN 表示 代表Null的一个 标记)

举个栗子:unknown = unknown判定为true而UNKNOWN = UNKNOWN( 也就是Null = Null) 判定为unknown

 

5. 包含三值逻辑的真值表

当两个值进行逻辑判断的时候的优先级,优先级高的真值会决定计算结果:

  • AND 的情况: false > unknown > true
  • OR 的情况: true > unknown > false

举个栗子: true AND unknown ,因为 unknown 的优先级更高,所以结果是 unknown 。而 true OR unknown 的话,因为 true 优先级更高,所以结果是 true 。

 

6. “排中律” 不再成立

“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题” 这个命题在二值逻辑中被称为排中律(Law of Excluded Middle)。顾名思义,排中律就是指不认可中间状态,对命题真伪的判定黑白分明,是古典逻辑学的重要原理。

举个栗子:现实生活中 一个学生 是20岁 或者 不是20岁,不会有第三种情况。

但是在SQL中并不是这个样:

-- 查询年龄是20 岁或者不是20 岁的学生
SELECT *
FROM Students
WHERE age = 20 OR age <> 20;

在现实生活中,上面的查询条件应该包含所有的学生,但是这里的执行结果并不会查询到约翰。

--- 当查询到约翰哪一行时的判定
WHERE Null = 20 OR Null <> 20

--- 根据上文的描述,该条件会转换为
WHERE unknown OR unknown   等同于 WHERE unknown

若要查到所有学生需要再加上一个条件:OR age IS NULL

 

7. CASE 表达式和 NULL

CASE col_1
  WHEN 1 THEN '○'
  WHEN NULL THEN '×'
END

上面的这个CASE表达式会在 col_1 为 1 时返回○、为 NULL 时返回吗?显然始终不会返回x因为第二个 WHEN 子句是 col_1 = NULL 的缩写形式,根据上文的描述 col_1 = NULL 始终会返回 unknown 而 CASE 表达式的判断方法与 WHERE 子句一样,只认可真值为 true 的条件,所以x并不会出现。下面才是正确的写法:

CASE 
  WHEN col_1 = 1 THEN '○'
  WHEN col_1 IS NULL THEN '×'
END

 

8. NOT IN 和 NOT EXISTS 不是等价的

在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN 改写成EXISTS 。这是等价改写,并没有什么问题。问题在于,将 NOT IN 改写成 NOT EXISTS 时,结果未必一样。

查询 “与 B 班住在东京的学生年龄不同的 A 班学生” 。也就是说,希望查询到的是拉里和伯杰。

-- 查询与 B 班住在东京的学生年龄不同的 A 班学生的 SQL 语句
SELECT *
FROM Class_A
WHERE age NOT IN ( SELECT age FROM Class_B WHERE city = '东京' );

这条 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;

--7.如果AND 运算里包含unknown,则结果不为true
SELECT *
FROM Class_A
WHERE false 或 unknown;

所以 上述查询语句 查询不到任何数据。为了得到正确的结果,需要使用 EXISTS 谓词。

-- 正确的SQL 语句:拉里和伯杰将被查询到
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = B.age AND B.city = '东京' );

--1. 在子查询里和NULL 进行比较运算
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = NULL AND B.city = '东京' );

--2. 对NULL 使用“=”后,结果为 unknown
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE unknown AND B.city = '东京' );


--3. 如果AND 运算里包含unknown,结果不会是true
SELECT *
FROM Class_A A
WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE false 或 unknown);


--4. 子查询没有返回结果,因此相反地,NOT EXISTS 为true
SELECT *
FROM Class_A A
WHERE true;

 

9. 限定谓词和 NULL

-- 查询比 B 班住在东京的所有学生年龄都小的A 班学生 这里会正常返回 拉里
SELECT *
FROM Class_A
WHERE age < ALL ( SELECT age FROM Class_B WHERE city = '东京' );

如果山田年龄不详,就会有问题了。

--1. 执行子查询获取年龄列表
SELECT *
FROM Class_A
WHERE age < ALL ( 22, 23, NULL);


--2. 将ALL 谓词等价改写为AND
SELECT *
FROM Class_A
WHERE (age < 22) AND (age < 23) AND (age < NULL);


--3. 对NULL 使用“<”后,结果变为 unknown
SELECT *
FROM Class_A
WHERE (age < 22) AND (age < 23) AND unknown;

--4. 如果AND 运算里包含unknown,则结果不为true
SELECT *
FROM Class_A
WHERE false 或 unknown;

 

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

将 9 中的表 Class_B 中 山田的年龄改为Null,执行下面的查询

-- 查询比B 班住在东京的年龄最小的学生还要小的A 班学生 
SELECT *
FROM Class_A
WHERE age < ( SELECT MIN(age) FROM Class_B WHERE city = '东京' );

这里仍能正确查询出拉里和伯杰,这是因为,极值函数在统计时会把为 NULL 的数据排除掉。使用极值函数能使 Class_B 这张表里看起来就像不存在 NULL 一样。

区分含义:

  • ALL 谓词:他的年龄比在东京住的所有学生都小 Q1
  • 极值函数:他的年龄比在东京住的年龄最小的学生还要小 Q2

Q1 和 Q2 不等价的情况:

  • 表里存在 NULL 时它们是不等价的
  • 谓词(或者函数)的输入为空集的情况

这里说明一下情况2:B 班里没有学生住在东京。这时,使用 ALL 谓词的SQL 语句会查询到 A 班的所有学生。然而,用极值函数查询时一行数据都查询不到。这是因为,极值函数在输入为空表(空集)时会返回 NULL 。

--1. 极值函数返回NULL
SELECT *
FROM Class_A
WHERE age < NULL;

--2. 对NULL 使用“<”后结果为 unknown
SELECT *
FROM Class_A
WHERE unknown;

 

11. 聚合函数和 Null

实际上,当输入为空表时返回 NULL 的不只是极值函数,COUNT 以外的聚合函数也是如此。

-- 查询比住在东京的学生的平均年龄还要小的A 班学生的SQL 语句?
SELECT *
FROM Class_A
WHERE age < ( SELECT AVG(age) FROM Class_B WHERE city = '东京' );

没有住在东京的学生时,AVG 函数返回 NULL 。因此,外侧的 WHERE 子句永远是 unknown ,也就查询不到行。

关于一文详解SQL中的三值逻辑的文章就介绍至此,更多相关SQL三值逻辑内容请搜索编程宝库以前的文章,希望以后支持编程宝库

 HAVING 子句始终要记得SQL是一种基于“面向集合”思想设计的语言。1. 寻找缺失的编号查询这张表里是否存在数据缺失。当前这张表的编号并不是连续的,缺少了 4 ...