1-8 EXISTS 谓词的用法

理论

什么是谓词

1. SQL 的保留字中，有很多都被归为谓词一类。例如，“= 、< 、> ”等比较谓词，以及 BETWEEN 、LIKE 、IN 、IS NULL 等
2. 谓词就是函数。但谓词与 SUM 或 AVG 这样的函数并不一样
3. 谓词是一种特殊的函数，返回值是真值。前面提到的每个谓词，返回值都是 true 、 false或者 unknown （一般的谓词逻辑里没有 unknown ，但是 SQL 采用的是三值逻辑，因此具有三种真值）
4. 谓词逻辑提供谓词是为了判断命题（可以理解成陈述句）的真假。

实体的阶层

1. 同样是谓词，但是与 = 、BETWEEN 等相比，EXISTS 的用法还是大不相同的。概括来说，区别在于“谓词的参数可以取什么值”
2. “x = y ”或“x BETWEEN y ”等谓词可以取的参数是像“13”或者“本田”这样的单一值，我们称之为标量值
3. 而 EXISTS 可以取的参数是行数据的集合

全称量化和存在量化

1. 谓词逻辑中有量词（限量词、数量词）这类特殊的谓词。我们可以用它们来表达一些这样的命题：“所有的 x 都满足条件 P”或者“存在（至少一个）满足条件 P 的 x ”。前者称为“全称量词”，后者称为“存在量词”，分别记作 ∀、∃。这两个符号看起来很奇怪。其实，全称量词的符号其实是将字母 A 上下颠倒而形成的，存在量词则是将字母 E左右颠倒而形成的。“对于所有的 x ，……”的英语是“for All x ，…”，而“存在满足……的 x ”的英语是“there Exists x that…”，这就是这两个符号的由来。
2. SQL 中的 EXISTS 谓词实现了谓词逻辑中的存在量词。然而对于全称量词， SQL 却并没有予以实现。
3. 因此在 SQL 中，为了表达全称量化，需要将“所有的行都满足条件P”这样的命题转换成“不存在不满足条件 P 的行”。

实践

查询表中“不”存在的数据

求“没有参加某次会议的人”

-- 求出缺席者的SQL 语句(1)：存在量化的应用
SELECT DISTINCT M1.meeting, M2.person
FROM Meetings M1 JOIN Meetings M2
WHERE NOT EXISTS
(SELECT * FROM Meetings M3 WHERE M1.meeting = M3.meeting
AND M2.person = M3.person);

结果:

全称量化(1) ：习惯“肯定 ⇔ 双重否定”之间的转换

通过这一部分内容的学习，希望大家能习惯从全称量化“所有的行都××”到其双重否定“不××
的行一行都不存在”的转换。

查询出“所有科目分数都在 50 分以上的学生”。

-- 将查询条件“所有科目分数都在 50 分以上”转换成它的双重否定“没有一个科目分数不满 50 分”，
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS -- 不存在满足以下条件的行
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.score < 50); -- 分数不满50 分的科目

查询出满足下列条件的学生。

1. 数学的分数在 80 分以上。
2. 语文的分数在 50 分以上。

命题转化: “某个学生的所有行数据中，如果科目是数学，则分数在 80 分以上；如果科目是语文，则分数在 50 分以上。”

SELECT student_id
FROM TestScores TS1
WHERE subject IN ('数学', '语文')
AND NOT EXISTS
(SELECT * FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id 
AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
			 WHEN subject = '语文' AND score < 50 THEN 1 ELSE 0 END)
GROUP BY student_id
HAVING COUNT(*) = 2; -- 必须两门科目都有分数

全称量化 (2) ：集合 VS 谓词——哪个更强大？

查询出哪些项目已经完成到了工程 1

-- 查询完成到了工程1 的项目：面向集合的解法
SELECT project_id
FROM Projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND status = '完成' THEN 1
WHEN step_nbr > 1 AND status = '等待' THEN 1
ELSE 0 END);

-- 查询完成到了工程1 的项目：谓词逻辑的解法
SELECT *
FROM Projects P1
WHERE NOT EXISTS
(SELECT status
FROM Projects P2
WHERE P1.project_id = P2. project_id -- 以项目为单位进行条件判断
AND status <> CASE WHEN step_nbr <= 1 -- 使用双重否定来表达全称量化命题
THEN '完成'
ELSE '等待' END);

本章要点

1. SQL 中的谓词指的是返回真值的函数。
2. EXISTS 与其他谓词不同，接受的参数是集合。
3. 因此 EXISTS 可以看成是一种高阶函数。
4. SQL 中没有与全称量词相当的谓词，可以使用 NOT EXISTS 代替。