SQL进阶-9-谓词exists使用

SQL进阶-9-EXISTS谓词的使用

支撑SQL和关系数据库的两个重要理论基础：

数学领域的集合论
现代逻辑学标准体系的谓词逻辑(predicate logic)

本文中重点介绍的是谓词exists的用法

extists谓词不仅可以将多行数据作为整体来表达高级的条件，还可以在使用关联子查询时表现出良好的性能。

什么是谓词

SQL保留字中有很多的谓词，比如：

<、>、=等比较谓词
Between、 like、in、is、null等

谓词就是函数；谓词是一种特殊的函数，返回的真值，结果都是true、false、unknown（一般的谓词逻辑中没有unknown，但是SQL采用的是三值逻辑，因此具有三种值）

exists属于二阶谓词，is、between等属于一阶谓词

谓词逻辑提供谓词是为了判断命题的真假

exists的参数

参数不是单一的某个值：参数是行记录的集合

select id
from Foo f
where exists(select *    -- 将B中全部行记录作为参数
             from Bar B
              where F.id=B.id);

Exists的子查询中，select子句的列表中可以有3种写法：

-- 1-通配符
select *

-- 2-常量
select "任意内容"

-- 3-常量
select col

全称量化和存在量化

所有的x都满足条件P：全称量词

存在满足条件P的x：存在量词，exists实现的是存在量词

SQL中没有实现全称量词的函数或者谓词

但是全称量词和存在量词二者之前可以相互转换

所有的x都满足条件P：不存在不满足条件P的x
存在满足条件P的x：并非所有的x都不满足条件P

SQL中实现全称量化，需要将所有的行都满足条件P转成不存在不满足条件P的行

案例1-查询没有参会人员

需求

从Meeting表中找出没有参会的人员

SQL实现

假设所有人都参加了全部的会议，生成了一个集合，再用该集合减去现有的数据即可。生成全部的会议的集合使用交叉联结：

1 2	select distinct M1.meeting, M2.person from Meetings M1 cross join Meeting M2; -- 结果如下表

使用存在量化求解：

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   -- 不存在M3与M1和M2相同的数据：即用全部集合减去现有的表中的数据
  and M2.person = M3.person
);

使用差集求解：

select distinct M1.meeting, M2.person
from Meetings M1 cross join Meetings M2
except  -- 差集排除  not exists具备了差集的功能
select meeting, person
from Meetings;

笔记：肯定=双重否定

exists实现全称量化

记住一点：所有的行都满足X等价于不满足X的行一行都不存在

需求1-指定分数以上

学生成绩表score，从中找出每门成绩都在50以上的学生，答案是100、200、400

SQL实现

-- 所有科目都在50分以上   等价于  没有一个科目不满足50分

select distinct student_id
from Score S1
where not exists(  -- 1. 不存在满足以下条件的行
  select *
  from Score S2
  where S1.student_id = S2.student_id
  and S2.score < 50);   -- 小于50分的科目

需求2-查询分数

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

SQL实现

select distinct student_id
from Score S1
where subject in ('数学','语文')   -- 只考虑两门科目
and not exists (
  select *
  from Score S2
  where S1.student_id = S2.student_id
  and 1 = case when subject = '数学' and score < 80 then 1   -- 数学小于80则赋值1
  						when subject = '语文' and score < 50 then 1  -- 语文小于50则赋值1
  			else 0 end);

加上条件：如何排除400的同学，因为他只有一门存在分数。使用having条件进行过滤

select (distinct) student_id   -- 通过student_id进行了聚合，可以不用distinct
from Score S1
where subject in ('数学','语文')   -- 只考虑两门科目
and not exists (
  select *
  from Score S2
  where S1.student_id = S2.student_id
  and 1 = case when subject = '数学' and score < 80 then 1   -- 数学小于80则赋值1
  						when subject = '语文' and score < 50 then 1  -- 语文小于50则赋值1
  			else 0 end)
group by student_id
having count(*) = 2;   -- 必须两门都有分数才行

需求3-查询全是1的行

从下面的表中找出全部是1的行

SQL实现

-- 不推荐

select *
from Tablename
where col1 = 1
and col2 = 1
...
and col10 = 1;   -- 当列属性多的时候这个方法不适用

-- 推荐
select *
from Tablename
where 1 = all(col1, col2, col3,...,col10);

-- 查询至少存在一个9的行记录：any
select *
from Tablename
where 9 = any (col1, col2, col3,...,col10);
-- where 9 in (col1, col2, col3,...,col10);

如果要查询至少存在一个NULL的行记录：

coalesce函数表示参数至少存在一个满足条件

1
2
3

select *
from Tablename
where  coalesce(col1, col2, col3,...,col10) is null;

小结

SQL谓词指的是返回值为真值的函数
EXISTS与其他谓词不同，接受的参数是集合；可以看做是一种高阶函数
SQL中没有实现全称量词相当的谓词，但是可以通过not exists来代替