SQL08 EXISTS谓词

• 理论篇
• • • EXISTS谓词定义
    • 全称量化和存在量化
• 实践篇
• • 查询不存在的数据
  • • 全称量化到双重否定
    • 全称量化和谓词
    • • 集合的解法
      • 谓词的解法
    • 列的全称量词

理论篇

EXISTS谓词定义

谓词是一种特殊的函数，返回值是真值 。SQL中的真值有3种：True，False，Unknown。
谓词可以按照阶数进行划分：

• 一阶谓词：输入值为一行数据：比如 =
• 二阶谓词：输入值为行的集合：比如EXISTS
• 三阶谓词：输入值为“集合的集合”的谓词
• ……

全称量化和存在量化

• 全称量化：所有的x 都满足条件P”
• 存在量化：存在（至少一个）满足条件P 的x，也就是EXISTS谓词
  我们可以用存在量化也就是EXISTS谓词去表示全称量化。
  即 所有的x 都满足条件P 等价于 不存在不满足条件P 的x

实践篇

查询不存在的数据

查询没有参加会议的某人
思路如下：假设所有人每场会议都参加了，将这个集合减去现有的集合就是没有参加会议的集合。
首先生成会议和 出席者的集合

Select DISTINCT(m1.meeting,m2.persion)
FROM meeting m1,meeting m2

接着使用EXISTS谓词实现减法

SELECT DISTINCT(m1.meeting,m2.person)
  FROM meeting m1,
       meeting m2
 WHERE NOT EXISTS(
        SELECT *
          FROM meeting m3
         WHERE m1.meeting=m3.meeting
           AND m2.person=m3.person
       )

全称量化到双重否定

请查询所有科目分数都在50分以上的同学
等价于
请查询不存在科目分数小于50分的同学

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

接着我们进阶一下，请求出满足下述条件的学生

• 数学的分数在80分以上
• 语文的分数在50分以上

SELECT DISTINCT student_id
  FROM testscores t1
 WHERE NOT EXISTS(
        SELECT *
          FROM testscores t2
         WHERE t1.student_id=t2.student_id
           AND 1= CASE WHEN t2.subject='语文' AND t2.score<80 THEN 1 
   				  CASE WHEN t2.subject='数学' AND t2.score<50 THEN 1
                  ELSE 0 END
       )

对于400这个学生，他没有语文成绩，需要排除掉他

SELECT DISTINCT student_id
  FROM testscores t1
 WHERE subject IN ('数学', '语文')
   AND NOT EXISTS(
        SELECT *
          FROM testscores t2
         WHERE t1.student_id=t2.student_id
           AND 1= CASE WHEN t2.subject='语文' AND t2.score<80 THEN 1 
   				  CASE WHEN t2.subject='数学' AND t2.score<50 THEN 1
                  ELSE 0 END
       )
GROUP BY t1.student_id
HAVING COUNT(*)=2 -- 排除400的学生

全称量化和谓词

从上述表中查询出哪些项目已经完成到了工程1

集合的解法

对于某个工程，当工程编号小于等于1时，必须是完成，当工程编号大于1时，必须是等待。等待的个数+完成的个数=总步数。

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

谓词的解法

对于某个工程，当工程编号小于等于1时，必须是完成，当工程编号大于1时，必须是等待。
等价于
对于某个工程，当工程编号小于等于1时，不存在状态是等待，当工程编号大于1时，不存在状态是完成
条件可以写成

CASE WHEN step_nbr <=1 THEN  status='完成'  ELSE status='等待'END

完整SQL如下

SELECT project_id
  FROM projects p1
 WHERE NOT EXISTS(
        SELECT *
          FROM projects p2
         WHERE p2.status<>CASE WHEN step_nbr <=1 THEN status='完成'
                    ELSE status='等待'
                     END
       )

列的全称量词

查询全为1的行

SELECT *
  FROM ArrayTbl
 WHERE col1 = 1
   AND col2 = 1 
   · · ·
   AND col10 = 1

利用ALL谓词

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

至少有一个9

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

查询全是NULL的行

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

选出全为1的key

SELECT *
  FROM ArrayTbl2 a1
  WHERE NOT EXISTS(
  SELECT *
  FROM ArrayTbl2 a2
    WHERE a1.key=a2.key AND a2.val<>1
  )

然而这样不只选出了C还出现了A。
原因在于SQL是三值逻辑，而EXISTS是二值逻辑。对于EXISTS而言，没有返回值就是FALSE，返回值就是TRUE。
所以A也会返回。
正确的写法应该排除全为NULL的情况

SELECT *
  FROM ArrayTbl2 a1
  WHERE NOT EXISTS(
  SELECT *
  FROM ArrayTbl2 a2
    WHERE a1.key=a2.key AND （a2.val<>1 OR a2.val IS NULL)
  )

或者使用集合的思想
极值函数解决

SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING MAX(val) = 1
AND MIN(val) = 1;

SELECT key
FROM ArrayTbl2
GROUP BY key
HAVING COUNT(*)=SUM(CASE WEHN val=1 THEN 1 ELSE 0 END)