PostgreSQL谓词之EXISTS

**何为SQL谓词？** 我们在SQL中接触的 BETWEEN 、 LIKE 、 IN 、 IS NULL、 = 、 < 、 >等关键词都可以称之为SQL中的谓词，它是一种返回值只为真值（ true 、 false 或者 unknown）的特殊函数。它的目的是为了给SQL命题提供判断真假的手段，谓词逻辑的出现对SQL而言具有划时代的意义。 Codd在1969年曾提到过：“数据库并非是一种数据的集合，它是事实的集合。” 而这里的事实我们则可以理解为真命题。 **何为EXISTS？** EXISTS逻辑运算符用于检测子查询的结果集是否包含目标值。如果结果集中至少包含一条目标值，则EXISTS的结果为true,否则为false。 **EXISTS与其它谓词的区别？** EXISTS相比其它谓词有着更强的表达力。首先EXISTS的输出值和其它谓词是一样的，都是真值。但是EXISTS的输入值可以是集合，这就意味着EXISTS可以通过集合描述任意多的元素，也就意味着它有着更强的表达力，前面我们说过谓词其实也是一种特殊的函数，所以也有些人习惯把EXISTS称作高阶函数。 **NOT EXISTS和EXISTS的简单应用：** ``` create table exists_01 (id int,name varchar(12),des text); insert into exists_01 values (1,'Tom','ms1'),(2,'Jer','ms2'),(3,'Jim','es1'); ``` 如何筛选出des是ms系列的角色？ ① ``` pgadv=# select name from exists_01  where des like 'ms%'; name ------ Tom Jer (2 rows) ``` ② ``` pgadv=# select name from exists_01 where des in ('ms1','ms2'); name ------ Tom Jer (2 rows) ``` ③ ``` pgadv=# select name from exists_01 t1 where exists (select * from exists_01 t2 where t1.name=t2.name and des like 'ms%'); name ------ Tom Jer (2 rows ``` 有的人可能会问，使用exists为什么会显得更麻烦了？别心急，我们继续向下看： 我这里有一张简单的学生成绩表： pgadv=# select * from stuscores ; student_name | subject | score --------------+---------+------- 张三         | 数学    |    98 张三         | 语文    |    75 张三         | 英语    |    84 李四         | 数学    |    88 李四         | 语文    |    92 王华         | 语文    |    88 王华         | 英语    |    53 赵齐         | 数学    |    99 赵齐         | 英语    |    43 王华         | 数学    |    50 (10 rows) **如何取出所有科目都大于60分的学生的姓名？** 很多时候，我们会想着先找出大于60分的科目有哪些？然后再从中获取学生的姓名，但是这样的话就会有一个问题，就是有些学生他的科目既有大于60分的也有小于60分的，我们只做分数的筛选显然不能满足需求。 假设我们需要获取的值为a，那么所有的a都是满足科目大于60分（条件b）的值，根据德·摩根定律，所有a都满足条件b=不存在不满足条件b的a。 于是我们转换思路找到不满足大于60分的学生集合，然后通过NOT EXISTS筛选出所需目标值。 ``` SELECT DISTINCT student_name FROM stuscores t1 WHERE NOT EXISTS     (SELECT *      FROM stuscores t2      WHERE t1.student_name=t2.student_name        AND t2.score<60); ``` 我们继续来看一个稍微复杂些的需求，如果想要的学生姓名是数学大于90分，语文大于70分的学生有哪些呢？ *实现方式一：* ```  SELECT     *   FROM       ( SELECT * FROM stuscores where  subject='数学' AND score >= 90 ) as m1,       ( SELECT * FROM stuscores where  subject='语文' AND score >= 70 ) as m2     WHERE       m1.student_name = m2.student_name; 也可以写成： SELECT * FROM stuscores t1,      stuscores t2 WHERE t1.student_name=t2.student_name   AND t1.subject='数学'   AND t1.score >= 90   AND t2.subject='语文'   AND t2.score >= 70; ``` *实现方式二：* ``` SELECT * FROM stuscores t1 WHERE student_name IN     (SELECT student_name      FROM stuscores      WHERE subject='数学'        AND score >= 90)   AND subject='语文'   AND score >= 70 ; ``` *实现方式三：* ``` SELECT * FROM stuscores t1 WHERE EXISTS     (SELECT *      FROM stuscores t2      WHERE t1.student_name=t2.student_name        AND subject='数学'        AND score >= 90)   AND subject='语文'   AND score >= 70 ; ``` 大家发现，好像EXISTS实现起来没什么优势，我们目前只是筛选了两门学科，如果将其增加到三门甚至四门、五门呢？ *实现方式四：* ``` SELECT * FROM stuscores t1 WHERE NOT  EXISTS     (SELECT *      FROM stuscores t2      WHERE t1.student_name=t2.student_name AND            'true' = case  when subject='数学' AND score < 90 then 'true'                           when subject='语文' AND score < 70 then 'true'                           else 'false' end);                           student_name | subject | score --------------+---------+------- 张三         | 数学    |    98 张三         | 语文    |    75 张三         | 英语    |    84 赵齐         | 数学    |    99 赵齐         | 英语    |    43 (5 rows) ``` 我们会发现，赵齐虽然满足case when的条件，但是他并没有语文学科的成绩，于是我们再加上限定： 最终实现语句： ``` SELECT DISTINCT student_name FROM stuscores t1 WHERE subject IN ('数学',                   '语文')   AND NOT EXISTS     (SELECT *      FROM stuscores t2      WHERE t1.student_name=t2.student_name        AND 'true' = CASE WHEN subject='数学'        AND score < 90 THEN 'true' WHEN subject='语文'        AND score < 70 THEN 'true' ELSE 'false' END) GROUP BY student_name HAVING COUNT(*) = 2; -------------- 张三 (1 row) ``` 这样无论我们要求的学科有多少，只需要增加case when中的条件即可实现。 附录：stuscores表创建语句 ``` CREATE TABLE stuscores (student_name varchar, subject    VARCHAR(32) , score      INTEGER ); INSERT INTO stuscores VALUES('张三', '数学','98'); INSERT INTO stuscores VALUES('张三', '语文',75); INSERT INTO stuscores VALUES('张三', '英语',84); INSERT INTO stuscores VALUES('李四', '数学',88); INSERT INTO stuscores VALUES('李四', '语文',92); INSERT INTO stuscores VALUES('王华', '数学',50); INSERT INTO stuscores VALUES('王华', '语文',88); INSERT INTO stuscores VALUES('王华', '英语',53); INSERT INTO stuscores VALUES('赵齐', '数学',99); INSERT INTO stuscores VALUES('赵齐', '英语',43); ```