MySQL中的EXISTS用法

EXISTS

语法：

SELECT 字段 FROM table WHERE EXISTS (subquery); 

参数：

subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）

示例：

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id); 

EXISTS执行顺序：

1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A

2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id

3、如果子查询有返回结果，则EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

NOT EXISTS

语法：

SELECT 字段 FROM table WHERE NOT EXISTS (subquery); 

参数：

subquery是一个受限的SELECT语句（不允许有COMPUTE子句和INTO关键字）

示例：

SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id); 

NOT EXISTS执行顺序：

1、首先执行一次外部查询，并缓存结果集，如 SELECT * FROM A

2、遍历外部查询结果集的每一行记录R，代入子查询中作为条件进行查询，如 SELECT 1 FROM B WHERE B.id = A.id

3、如果子查询没有返回结果（与EXISTS相反），则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行，否则不能作为结果

IN

IN常用于where表达式中，其作用是查询某个范围内的数据。

示例：

select * from where field in (value1,value2,value3,…) 

NOT IN

当 IN 前面加上 NOT 运算符时，表示与 IN 相反的意思，即不在这些列表项内的选择

示例：

select * from where field not in (value1,value2,value3,…) 

实例

假设现在有三张表：

student：学生表，其中有字段sno为学号，sname为学生姓名

course：课程表，其中有字段cno为课程号，cname为课程名称

student_course_relation：选课表，记录学生选择了哪些课程，其中有字段sno为学号，cno为课程号

下面通过几个示例来说明一下EXISTS和NOT EXISTS的用法，及其与IN和NOT IN的区别

1、在子查询中使用NULL，仍然返回结果集

下面三种情况返回数据相同，都会返回student表的所有数据：

select * from student; 
select * from student where exists (select 1); 
select * from student where exists (select null); 

2、EXISTS子查询返回的是一个布尔值true或false

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回布尔值true或false，EXISTS指定一个子查询，检测行的存在。

EXISTS只在乎子查询中是否有记录，与具体的结果集无关，所以下面示例中，子查询中的select sno也可以换成select cno或者select 1，查询出的结果集是一样的。

查询所有选修了课程号为3的学生：

select * from student a 
where exists (select sno from student_course_relation b where b.cno=3 and b.sno=a.sno); 

select * from student a 
where exists (select cno from student_course_relation b where b.cno=3 and b.sno=a.sno); 

select * from student a 
where exists (select 1 from student_course_relation b where b.cno=3 and b.sno=a.sno); 

3、比较使用EXISTS和IN的查询

示例，查询所有选修了课程号为1的学生名单

使用exists：

select * from student 
where exists (select sno from student_course_relation where cno=1 and student_course_relation.sno=student.sno) 

使用in：

select * from student 
where sno in (select sno from student_course_relation where cno=1) 

以上两个sql查询，返回相同的结果。

EXISTS查询：先执行一次外部查询，然后为外部查询返回的每一行执行一次子查询，如果外部查询返回100行记录，sql就将执行101次查询。

IN查询：先查询子查询，然后把子查询的结果放到外部查询中进行查询。IN语句在mysql中没有参数个数的限制，但是mysql中sql语句有长度大小限制，整段最大为4M。IN引导的子查询只能返回一个字段。

当子查询的表大的时候，使用EXISTS可以有效减少总的循环次数来提升速度，当外查询的表大的时候，使用IN可以有效减少对外查询表循环遍历来提升速度，显然，外表大而子表小时，IN的效率更高，而外表小，子表大时，EXISTS的效率更高，若两表差不多大，则差不多。

4、比较使用NOT EXISTS和NOT IN的区别

示例，查询没有选修课程号为1的学生名单

使用NOT EXISTS：

select * from student a 
where not exists (select sno from student_course_relation b where cno=1 and a.sno=b.sno) 

使用NOT IN：

select * from student a 
where sno not in (select sno from student_course_relation b where cno=1) 

NOT EXISTS：先执行一次外部查询，然后为外部查询返回的每一行记录R执行一次子查询，如果子查询没有返回记录，则NOT EXISTS子句返回TRUE，这一行R可作为外部查询的结果行。

NOT IN：外部查询在表中查询每条记录，符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，也就是说为了证明找不到，需要查询全部记录才能证明，NOT IN不会用到索引。

5、在插入记录前，检查这条记录是否存在，只有当记录不存在时才执行插入操作

示例，若学号为3的学生没有选课程号为2的课程，则选择此课程

insert into student_course_relation(sno, cno) 
select '3' as sno, '2' as cno from student_course_relation 
where not exists (select sno from student_course_relation where sno=3 and cno=2) limit 1 

此Sql适用场景：虽然业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引，但是有些老业务可能已经写入了重复数据，且重复数据不能删除，这样的话，就不能建立唯一索引，后续的数据却又要求两个字段的组合唯一，可以使用以上sql语句解决这个问题。

6、查询出选修了全部课程的学生姓名

思路1：首先我们需要知道一共有几门课程，然后扫描student_course_relation表，统计出选修了所有课程的学号，最后在student表中根据学号打出学生姓名。

select sname from student where sno in ( 
    select sno from student_course_relation 
    group by sno    -- 根据sno分组，统计每个学生选修了几门课，如果等于course的总数，就是我们要找的sno 
    having(count(sno)) = (select count(*) from course) 
) 

思路2：

首先我们来查询学号为3的学生没有选修的课程

select cname from course 
where not exists    -- 找不到的记录，提交course 
    (select cno from student_course_relation where student_course_relation.cno=course.cno and sno='3') 

如果我们对所有的学号进行循环，这道题的题目可以转化为：查询 没有 没有选修课的学生姓名，即选修了全部课程的学生姓名

select sname from student 
where not exists (    -- 查询 没有 没有选修的课程 的学生，即选修了全部课程的学生 
    select cname from course    -- 查询某学生没有选修的课程 
    where not exists 
        (select cno from student_course_relation  
        where student_course_relation.cno=course.cno and student_course_relation.sno=student.sno) 
) 

7、查询没有选择所有课程的学生

即存在这样的一个学生，他至少有一门课没有选

select * from student a 
where exists ( 
    select * from course b 
    where not exists (select * from student_course_relation c where c.sno=a.sno and c.cno=b.cno)) 

注意：EXISTS或NOT EXISTS写法需要注意子查询中的条件语句一般需要带上外查询的表做关联，不然子查询的条件可能会一直为真，或者一直为假，外查询的表进行循环匹配的时候，要么全部都查询出来，要么一条也没有。

8、查询至少选修了学生3选修的全部课程的学生名单

这个题目可以转化为：不存在这样的课程x，学生3选修了x而学生m没有选

select * from student m where sno!=3 and not exists ( 
    select sno from student_course_relation x where sno=3 and not exists ( 
        select cno from student_course_relation y where y.cno=x.cno and y.sno=m.sno 
    ) 
) 

9、查询一门课也没有选的学生

即不存在这样的一个学生，他至少选修了一门课程

select * from student a 
where not exists ( 
    select * from course b 
    where exists (select cno from student_course_relation c where c.cno=b.cno and c.sno=a.sno)) 

10、查询至少选修了一门课程的学生

select * from student a  
where exists ( 
    select cno from course b  
    where exists (select sno from student_course_relation c where c.cno=b.cno and c.sno=a.sno)) 

11、选出每门课程中成绩最高的学生

select * from student_course_relation a 
where not exists (select * from student_course_relation b where b.cno=a.cno and b.score > a.score) 

另一种实现方式：

select * from student_course_relation a 
where score=(select max(score) from student_course_relation b where b.cno=a.cno) 

更多内容，请扫码关注公众号：