sql中in、exists和not exists的用法_and not exists

---

前言

在做数据库实验涉及相关子查询题目中使用exists 和not exists 遇到的问题，还有exists和in 什么时候能替换的问题

---

一、exists和not exists

相关子查询是相对于简单查询和连接查询是属于复杂的查询，子查询的查询条件依赖父查询（依赖于共同属性，就是外码）。这类查询针对于exists和not exists 的嵌套查询。
存在量词exists大量使用在相关子查询中，sql语言不支持全称量词，所以我们用not exists 实现。在带有exists运算符的嵌套查询中，子查询只返回结果，不返回数据，如果有结果则父查询的where子句为真，否则为假。使用格式如下：

[not]exists (<子查询>)
1

二、实验例题

1.查询至少选修了学生为140005的学生所选修的全部课程的学生学号的姓名

代码如下（示例）：

select sname                             
from student
where not exists(select *   --选出学号为“140005”的学生选择的课程           
                 from SC as SC1
				 where sno='140005'and not exists (select *     --选出学号为“140005”的学生选择的课程    
				                                   from SC as SC2
								                   where SC2.cno=SC1.cno and SC2.sno= student.sno))
1
2
3
4
5
6
7

因为不能使用全称量词，所以问题转化为：查询没有一门课（被学号“140005”的学生选择的课）没有被该生选择的学生的姓名。
嵌套两个查询，父查询第一个select就是从student表里找出sname，第二个select从sc1表里选出学号为“140005”的学生选择的课程，第三个select从是从sc2表中选出课程被该生选择的记录.
在第三个select的where子句中的SC2.sno=student.sno这句就是相关子查询的一个标志，我是这样觉得，子查询和父查询依赖于共同属性（SC2和student的共同属性sno）。

2.查询全是男同学选修的课程号

代码如下（示例）：

GO                  
select distinct cno    --方法一
from SC
where cno not in (select cno
                  from student, SC 
				  where  sc.sno = student.sno and ssex = '女' )
GO
select  distinct cno    --方法二
from SC a
where not exists (select *
                  from student,SC b
				  where  b.sno=student.sno and ssex='女'and a.cno=b.cno)
GO
select distinct cno    --方法三（就是方法二的复杂化）
from SC a
where not exists (select *
                  from student
				  where ssex='女' and  exists(select *
				                              from SC b
							           		 where student.sno=b.sno and a.cno=b.cno))

GO
select cno             --方法四 不使用distinct
from Course 
where cno in (select cno
              from SC a
			  where cno not in (select cno 
			                    from student,SC b
								where ssex = '女' and b.sno = student.sno))

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

问题转化：没有一个女生选该课程 ,使用了四种方法，方法一使用IN运算符，方法二使用not exists运算符，最后一种是从Course表中查询，没有重复元组，不使用distinct。关于IN和exists的使用在下面说明。

三、in和exists

1.两种方式实现

in：in运算符是将内表和外表连接起来，先查询内表，再把内表结果与外表匹配，即等值连接，使用in时，where子句中可以指定多个值，即查询字段是否在这多个值中。
exists：exists运算符是检查子查询表中是否有结果，只返回true，false，null，所以查询目标列用“*”表示，不需要指明列名。
题目：
查询选修了“c1”课程但没有选修“c2”课程的学生学号
使用in方式实现

select sno as 学号
from  Course A
where A.cno = 'C1' and  sno not in (select  sno
                                    from  Course B
                                    where  cno ='C2' )
1
2
3
4
5

以上查询使用了in语句,in()只执行一次,它查出Course B表中的所有id字段并缓存起来之后,检查Course A表的sno 是否与B表中的sno相等,如果相等则将Course A表的记录加入结果集中,直到遍历完Course A表的所有记录.

使用exists方式实现

select sno as 学号
from  Course A
where A.cno = 'C1' and  not exists (select * 
                                    from Course B
                                    where A.sno=B.sno  and  cno ='C2' )
1
2
3
4
5

子查询的查询条件依赖父查询，就有A.sno=B.sno,通过子查询（为一个完整的语句）返回的数据是否为null，如果不为null，就会将当前的数据加入结果集，因此我们select * from Course A的时候，我们是从第一条数据开始执行的，每次执行都会去执行exists的子查询.

2.in和exists区别

in（）的使用

select * 
from A
where id in(select id
            from B)
1
2
3
4

以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
exists（）的使用

select a.*
from A a
where exists(select *
             from B b 
             where a.id=b.id)
1
2
3
4
5

以上查询使用了exists语句,比in（）在where子句后面多了一句a.id=b.id，子查询依赖于父查询的共同属性，exists()会执行A.length次,它并不缓存exists()结果集,看结果集中是否有记录,如果有则返回true,没有则返回false.当B表比A表数据大时适合使用exists(),因为它没有遍历操作,只需要再执行一次查询就行
如:A表有100条记录,B表有100000000条记录,那么exists()还是执行100次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.

3.结论

in()适合B表比A表数据小的情况
exists()适合B表比A表数据大的情况
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.