【SQL进阶教程】EXISTS谓词的用法

Posted on | In

谓词是一种特殊的函数，返回值是真值。谓词逻辑提供谓词是为了判断命题的真假。
参数是行数据的集合。
在EXISTS的子查询里，SELECT子句的列表可以有下面这三种写法。

  1. 通配符：SELECT *
  2. 常量：SELECT ‘这里的内容任意’
  3. 列名：SELECT col

在SQL中，为了表达全称量化，需要将“所有的行都满足条件P”这样的命题转换成“不存在不满足条件P的行”。

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

在有些情况下，不得不从表中查找出“不存在的数据”。例如下面这样的情况。

nYcLZj.jpg

求“没有参加某次会议的人”，该怎么做呢？目标结果如下所示，是各次会议缺席者的列表。

nYg3TA.png

思路是先假设所有人都参加了全部会议，并以此生成一个集合，然后从中减去实际参加会议的人，这样就能得到缺席会议的人。

1
2
3
4
5
6
7
8
-- 存在量化
SELECT DISTINCT M1.meeting, M2.person
FROM Meetings M1
CROSS JOIN Meetings M2
WHERE NOT EXISTS (SELECT *
                  FROM Meetings M3
                  WHERE M1.meeting = M3.meeting
                        AND M2.person = M3.person);

还可以用集合论的方法，像下面这样使用差集运算。

1
2
3
4
5
6
-- 差集运算
SELECT M1.meeting, M2.person
FROM Meetings M1, Meetings M2
EXCEPT
SELECT meeting, person
FROM Meetings;

通过以上两条SQL语句的比较可以明白，NOT EXISTS直接具备了差集运算的功能。

全称量化（1）：习惯“肯定<->双重否定”之间的转换

这里使用下面这样一张存储了学生考试成绩的表为例。

ntwWDI.jpg

先来看一个简单的问题：请查询出“所有科目分数都在50分以上的学生”。

1
2
3
4
5
6
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS (SELECT *
                  FROM TestScores TS2
                  WHERE TS1.student_id = TS2.student_id
                        AND TS2.score < 50);

nt0XSe.png

接下来把条件改得复杂一些。思考一下如何查询出满足下列条件的学生。

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

结果应该是学号分别为100、200、400的学生。

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT student_id
FROM TestScores TS1
WHERE subject IN ('数学', '语文') 
      AND NOT EXISTS (SELECT *
                      FROM TestScores TS2
                      WHERE TS1.student_id = TS2.student_id
                            AND 1 = CASE WHEN subject = '数学' AND score < 50 THEN 1
                                         WHEN subject = '语文' AND score < 80 THEN 1
                                         ELSE 0
                                    END);

接下来，思考一下如何排除掉没有语文分数的学号为400的学生。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT student_id
FROM TestScores TS1
WHERE subject IN ('数学', '语文') 
      AND NOT EXISTS (SELECT *
                      FROM TestScores TS2
                      WHERE TS1.student_id = TS2.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;

ntBocQ.png

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

EXISTS和HAVING有一个地方很像，即都是以集合而不是个体为单位操作数据。实际上，两者在很多情况下都是可以互换的。接下来通过比较来了解一下它们各自的优点和缺点。
假设存在下面这样的项目工程管理表。

ntRTjf.jpg

这里的问题是，从这张表中查询出哪些项目已经完成到了工程1。

1
2
3
4
5
6
7
8
-- 面向集合
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);

ntWwqS.png

用谓词逻辑该如何解决呢？

1
2
3
4
5
6
7
8
9
-- 谓词逻辑
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);

nthSmT.png

与HAVING相比，使用了NOT EXISTS代码看起来不是那么容易理解，这是它的缺点。但是这种写法也有优点。第一个优点是性能好。只要有一行满足条件，查询就会终止，不一定需要查询所有行的数据。而且还能通过连接条件使用“project_id”列的索引，这样查询起来会更快。第二个优点是结果里包含的信息量更大。如果使用HAVING，结果会被聚合，只能获取到项目ID，而如果使用EXISTS，则能把集合里的元素整体都获取到。

对列进行量化：查询全是1的行

ntoAUO.jpg

这张表的设计不好的原因是，数组中的元素可以自由地增加或者减少，而表中的列却不能这样。因此在设计表时有一条原则：让列具有一定的扩展性。数组中的元素不应该对应表中的列，而是应该对应行。

在使用这种模拟数组的表时遇到的需求一般都是下面这两种形式。

  1. 查询“都是1”的行
  2. 查询“至少有一个9”的行

对于这个问题，需要进行“列方向”的量化。

1
2
3
SELECT *
FROM ArrayTbl
WHERE 1 = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

nt7cuR.png

反过来，如果想表达“至少有一个9”这样的存在量化命题，可以使用ALL的反义谓词ANY。

1
2
3
SELECT *
FROM ArrayTbl
WHERE 9 = ANY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

ntHAaV.png

或者也可以使用IN谓词代替ANY。

1
2
3
SELECT *
FROM ArrayTbl
WHERE 9 IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

但是，如果左边不是具体值而是NULL，这种写法就不行了。这种情况下，需要使用COALESCE函数。

1
2
3
SELECT *
FROM ArrayTbl
WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;

ntHIoV.png