SQL笔记| EXISTS 谓词的用法

EXISTS是为了实现谓词逻辑中“量化”(quantification)这一强大功能而被引入SQL的。如果能理解这个概念并且能灵活运用谓词，数据库工程师的能力会提升很多。

理论篇

什么是谓词
SQL的保留字中，有很多被归为谓词一类。例如，“=、<、>”等比较谓词，以及BETWEEN、LIKE、IN、IS NULL等。再写SQL语句时我们几乎离不开这些谓词。谓词是一种特殊的函数，其返回值是真值。前面提到的每个谓词，返回值都是true、false或者unknown。
谓词逻辑提供谓词是为了判断命题（可以理解成陈述句）的真假。在谓词逻辑的出现之前，命题逻辑中并没有像这样能够深入调查命题内部的工具。谓词逻辑的出现具有划时代的意义，原因就在于为命题分析提供了函数式的方法。
实体的阶层
同样是谓词，与=、BETWEEN等相比，EXISTS的用法还是不大相同，概括来说，区别在于“谓词的参数可以取什么值”。“x=y”等谓词可以取的参数为单一值，我们称之为标量值。而EXISTS可以取的参数不像是单一值。

SELECT id FROM  Foo F WHERE EXISTS (SELECT * FROM Bar B WHERE F.id=B.id);

参数是行数据的集合。无论子查询中选择什么样的列，对于EXISTS来说都是一样的。在EXISTS的子查询里，SELECT子句的列表可以有下面三种写法。
1、通配符：SELECT *
2、常量：SELECT ‘这里的内容任意’
3、列名：SELECT col
但是，不管采用上面这三种写法中的哪一种，得到的结果都是一样的。
谓词逻辑中，我们可以根据输入值的阶数对谓词进行分类。=等输入值为一行的谓词叫做“一阶谓词”，而像EXISTS这样输入值为行的集合的谓词叫做“二阶谓词”。阶（order）是用来区分集合或谓词的阶数的概念。EXISTS因接受的参数是集合这样的一阶数试题（entity）而被称为二阶谓词，但是谓词也是函数的一种，因此我们也可以说EXISTS是高阶函数。
全称量化和存在量化
SQL支持EXISTS，但不支持FORALL，于是会有一些查询只能选择用EXISTS，那么代码写起来就会非常麻烦。SQL中的EXISTS谓词实现了谓词逻辑中的存在量词。没有全称量词并不算SQL的致命缺陷，因此全称量词和存在量词只要定义了一个，另一个就可以被推导出来。
所有的x都满足条件P=不存在不满足条件P的x
存在x满足条件P=并非所有的x都不满足条件P

实践篇

查询表中“不”存在的数据
我们从数据库中查询数据时，一般是从表里存在的数据中选出满足某些条件的数据。但是在有些情况下，我们不得不从表中查找出“不存在的数据”。这听起来可能很奇怪，但是这种需求并不算少。
思路是首先假设所有人都参加了全部会议，并以此生成一个集合，然后从中减去实际参加会议的人。这样就能得到缺席会议的人。

--求出缺席者的SQL语句（1）：存在量化的应用
SELECT DISTINCT M1.meeting,M2.person FROM Meetings M1 CROSS JOIN Meetings M2 WHERE NOT EXISTS (SELECT * FROM Meettings M2 WHERE M1.meeting = M3.meeting AND M2.person = M3.person );
--求出缺席者的SQL语句（2）：使用差集运算
SELECT M1.meeting,M2.person FROM Meetings M1 ，Meetings M2 
 EXCEPT
 SELECT meeting, person FROM Meetings ;

通过以上两条SQL语句的比较，我们可以明白，NOT EXISTS 直接具备了差集运算的功能。
全称量化（1）：习惯“肯定<=>双重否定”之间的转换
查询“所有科目分数都在50分以上的学生”。解法是，将查询条件“所有科目分数都在50分以上”转换成它的双重否定“没有一个科目分数不满50分”，然后用NOT EXISTS来表示转换后的命题。

SELECT DISTINCT student_id FROM TestScoress TS1 
 WHERE NOT EXISTS ( SELECT * FROM TestScores TS2 WHERE TS2.student_id=TS1.student_id AND TS2.score < 50 );

SELECT student_id FROM TestScoress TS1 
 WHERE subject in ('数学','语文') NOT EXISTS ( SELECT * FROM TestScores TS2 WHERE TS2.student_id=TS1.student_id AND 1= CASE WHEN subject ='数学'  and TS2.score < 80 THEN 1 WHEN subject ='语文'  and TS2.score < 50 THEN 1 ELSE 0 END )
 GRUOP BY student_id
 HAVING COUNT(*) =2 ;

全称量化（2）：集合与谓词——哪个更强大
EXISTS 和 HAVING 有一个地方很像，即都是以集合而不是个体为单位来操作数据的。实际上，两者在很多情况下是可以互换的，用其中一个写出的查询语句，大多时候也可以用另一个来写。

--查询完成到了工程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 );

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

--“列方向”的全称量化：不优雅的解法
SELECT * FROM  ArrayTb1 
 WHERE col1 =1 and col2=1 ... and col10=1;
--“列方向”的全称量化：优雅的解法
SELECT * FROM  ArrayTb1 
 WHERE 1= ALL(col1,col2,...,col10);

SQL语言准备了一个谓词帮助我们进行“列方向”的量化。如果想表达“至少有一个9”这样的存在量化命题，可以使用ALL的反义谓词ANY。

SELECT * FROM  ArrayTb1 
 WHERE 9= ANY(col1,col2,...,col10);
--查询全是NULL的行：正确解法使用COALESCE函数
SELECT * FROM  ArrayTb1 
 WHERE COALESCE(col1,col2,...,col10);