EXISTS谓词的用法

在SQL中，谓词可以看作一类特殊的函数，这类函数只返回逻辑值（true，false，unknown），比>,< ,between等都是谓词。同样，我们平时使用的 WHERE 子句，其实也可以看成是由多个谓词组合而成的新谓词。只有能让 WHERE 子句的返回值为真的命题，才能从表（命题的集合）中查询到。exists谓词和其他的谓词不同的是，其他的谓词取值只能取单一值，称为标量值。而exists的参数是行的集合。

1.求缺席会议的记录

我们并不是要根据存在的数据查询“满足这样那样条件”的数据，而是要查询“数据是否存在”。从阶层上来说，这是更高一阶的问题，即所谓的“二阶查询”。这种时候正是 EXISTS 谓词大显身手的好时机。思路是先假设所有人都参加了全部会议，并以此生成一个集合，然后从中减去实际参加会议的人。这样就能得到缺席会议的人。

Meeting.png

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);

-- 通过笛卡尔乘积是假设所有的人都参加了会议，然后通过not exist把
-- 参加会议的人排除在外

image.png

2.通过双重否定表示肯定，表达全称量化

TestScores.png

2.1 所有科目分数都在 50 分以上的学生。

可以这样先按照学生id进行group by 然后用having条件筛选。

SELECT student_id FROM TestScores GROUP BY student_id 
HAVING SUM(CASE WHEN score > 50 THEN 1  ELSE 0 END) = COUNT(*)

用exists关键词和关联子查询

SELECT DISTINCT t1.student_id FROM TestScores t1 
WHERE NOT EXISTS(SELECT * FROM TestScores t2 WHERE t2.student_id = t1.student_id
AND t2.score < 50)

将查询条件“所有科目分数都在 50 分以上”转换成它的双重否定“没有一个科目分数不满 50 分”，然后用 NOT EXISTS 来表示转换后的命题。

2.2某个学生的所有行数据中，如果科目是数学，则分数在 80 分以上；如果科目是语文，则分数在 50 分以上。

SELECT DISTINCT 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);
-- 用CASE语句灵活筛选

3.查询那些工程完成到了工程1

image.png

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);

还是可以用exists语句来实现，同样是双重否定表示肯定

SELECT DISTINCT p0.project_id FROM Projects p0
WHERE NOT EXISTS
(SELECT * FROM Projects p1 WHERE p0.project_id = p1.project_id and
(CASE 
    WHEN p1.step_nbr <= 1 AND p1.`status` = '等待' THEN 1
  WHEN p1.step_nbr > 1 AND p1.`status` = '完成' THEN 1
    ELSE 0 END )=1)

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