Exists的作用知多少

发现自己不知道的越来越多了,终究还是自己太菜了

Exists是什么

说到exists,先来讲讲什么是谓词把,在SQL的很多保留字中,有很多都被归为谓词一类,可以理解为,谓词就是返回值为真值。例如"=,<,>"以及"between and like ,in ,null, is not null",在写SQL语句的时候,我们几乎每天都在用谓词。就像上面说的,就是一种特殊的函数,返回值是真值。

谓词的概念说清了,下面来讲讲exists.

在平常的sql中,博主用到的exists其实并不多,很少用。就像之前博主写的关联子查询,几乎就没听说过,更别说用了。

下面通过一个例子来讲解一下

查询表中“不”存在的数据

Exists的作用知多少

 

找出没有参加会议的人

假设所有人都参加了会议 -- 将这个生成一个集合

没有参加会议的人 = 所有人参加会议 - 实际上参加会议的人

  1. 所有人都参加了会议
select dietinct M1.meeting,M2.person 
from Meetings M1,Meetings M2

Exists的作用知多少

 

2.实际上参加会议的人

select * from Meetings

Exists的作用知多少

 

3.没有参加会议的人

select distinct M1.meeting,M2.person 
from Meetings M1,Meetings M2 
where not exists
 (select * from Meetings M3 where M3.meeting = M1.meeting and M3.person = M2.person)

Exists的作用知多少

 

分析一下上面的SQL,在全部参加会议的表里面找到不存在参加会议的人。

可能会有观众不了解,为什么是not exists?如果去掉not呢

Exists的作用知多少

很明显的知道.

去掉 not之后就和原表的数据一模一样了。那么加上not 不就是

所有参加的人 - 实际参加的人 = 没有参加的人

可能光看一个例子并看不懂,下面再来一个例子大家就懂了。

下面是一张学习成绩表的数据

Exists的作用知多少

 

需求:找到所有科目都在50分以上的同学。

从上面的表中可以看到,所有科目分数都在50分以上的同学有3人。分别是 100,200,400

要找到所有科目都在50分以上的同学,换一个思路

所有科目的分数都没有在50一下

这样,我们就有了sql

select distinct student_id 
from TestScore T1
where not exists 
(select * from TestScore T2 where T1.student_id = T2.student_id and T2.score < 50 )

Exists的作用知多少

需求2:数学的分数要在80分以上,语文的分数在50分以上的同学

如果科目是数学,那么数学的分数要在80以上

如果科目是语文,那么语文的分数要在50以上

使用case when来解决这道题

select distinct student_id from TestScore T1
 where subject in ("数学","语文")
 and not exists 
( select * from TestScore T2
 where T1.student_id = T2.student_id 
and 
1 = case when subject = "数学" and score < 80 then 1 
    when subject = "语文" and score < 50 
    then 1 else 0 end)

解释一下上面的代码

首先我们要找到语文和数学这两门成绩

排除掉 语文少于50分的,数学少于80分的,剩下的就是我们要求的结果

先看一下结果

Exists的作用知多少

 

但是我们发现,结果并不是正确的,因为在元数据中,学号为400的同学,只有数学成绩,没有语文成绩,那么如何排除掉这个学号为400的同学呢?肯定不是 where student_id != 400 。因为我们的数据量小,所以我们通过对比元数据就可以看到,学号为400的同学没有语文成绩。

题目要求是数学和语文成绩。两门学科,我们可以通过学科数目来排除掉这个400

select distinct student_id 
from TestScore T1
where subject in ("数学","语文") 
and not exists
( select * from TestScore T2 where T1.student_id = T2.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

根据学号分组,这样整体的数据就是下面的格式

         学号         科目

        100         数学,语文

        200         数学,语文

        400         数学

然后通过Having来排除掉只有一条数据的400

真正的结果就应该是下面的样子

Exists的作用知多少

 

总结:

如果我们感觉exists比In好用,那就有点以偏概全了。

我们认为in好用,是因为我们只会用IN,就像上篇博文讲的,我们不知道关联子查询,不用关联子查询,就不知道关联子查询的好。

但是当我们学习了exists的用法之后,会感觉到exists的用法其实并不比in的作用小。

只有踏出舒适区,我们才知道自己以前有多渺小。

上一篇:SQL语言复杂查询 - EXISTS/NOT EXISTS子查询


下一篇:Airtest API精讲之断言