SQL查询 — EXISTS谓词的用法

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

要点

谓词是一种特殊的函数，返回值是真值（True, False, Unknown）。
EXISTS的参数是行数据的集合。输出值是真值。EXISTS的特殊性在于输入值的阶数，其输入值为行的集合，叫作"二阶谓词"。SQL里的EXSITS谓词最高只能接受一阶的实体作为参数。
（ SQL不会出现三阶（“集合的集合”）以上的情况。）

全称量化：“for All x…”
存在量化: “there Exists x that…”
全称量词只要定义了一个，另一个就可以被推导出来。在SQL中，为了表达全称量化，需要将“所有的行都满足条件P”这样的命题转换成“不存在不满足条件P的行”。

---

应用示例

1. 查询表中"不"存在的数据

Meeting表有meeting列和person列，找出"没有参加某次会议的（某）人"
表Meetings

*差集运算：所有人都参加了全部的meeting - 实际参会者记录集合

SELECT DISTICT M1.meeting,M2.person
FROM Meeting M1 CROSS JOIN Meeting M2
WHERE NOT EXISTS (SELECT *
				  FROM Meeting M3
				  WHERE M1.meeting = M3.meeting
					AND M2.person =M3.person);

2. 全称量化 —— “肯定<=>双重否定”

TestScore表有列student_id, subject, score列，查询所有科目分数都在50分以上的学生
表TestScores

*命题转化成"没有一个科目分数不满50分的学生"（双重否定）

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

查询数学分数在80分以上，语文分数在50分以上的学生
*使用条件分支满足两个分数条件的同时，还需排除掉没有数学/语文分数的学生

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 <80 THEN 1
       								 WHEN subject = '语文' AND Score <50 THEN 1
       								 ELSE 0 END)
GROUP BY student_id
HAVING COUNT(*) = 2;  				    

3. 全称量化 —— HAVING子句和EXSITS谓词

EXISTS和HAVING都是以集合而不是个体为单位来操作数据。两者在许多情况下都可以互换。
Projects表有project_id, step_nbr, status列，查询表中哪些项目已经完成到了工程1（不超过工程1）
表Projects

命题为"某个项目的所有行数据中，工程编号是1以下的状态为已完成，工程编号大于1的还在等待"。
使用HAVING子句（只能取出project_id)

SELECT DISTINCT project_id
FROM Projects
GROUP BY project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND status = '完成' THEN 1
								step_nbr > 1 AND status = '等待' THEN 1
						   ELSE 0 END);

使用EXISTS谓词

SELECT *
FROM Projects P1
WHERE NOT EXISTS(SELECT status
				 FROM Projects P2
				 WHERE P1.student_id = P2.student_id
				   AND status <> CASE WHEN step_nbr <= 1 THEN '完成'
				   					  ELSE '等待' END);

4. （对列进行量化）使用谓词ALL，ANY

有模拟数组的表ArrayTbl，（1）查询都是1的行（2）查询至少有一个9的行
使用ALL

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

使用ANY

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

或使用IN谓词

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

若要查询全为Null的行的话，使用COALESCE函数
COALESCE()函数用于返回列表中第一个非NULL值。
COALESCE(value_1, value_2, …, value_n)
若列表中都为NULL，则COALESCE()函数返回NULL。

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

---