SQL语句之[NOT] EXISTS 详解

使用的是sql server 2008

这是创建的三个表 s , c , sc的语句

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
use school;
create table s
(
xh char(4) primary key ,
xm char(8) not null ,
zy char(20) ,
)
create table c
(
kch char(2) primary key ,
kcm char(20) not null
)
create table sc
(
xh char(4) ,
kch char(2) ,
cj smallint ,
primary key(xh , kch) ,
foreign key (xh) references s(xh) ,
foreign key (kch) references c(kch)
)

以下是所创建表所插入的数据
C表

C 表

S表

S 表

SC表

S 表

Question: 请选出选择全部课程的学号和姓名

自然,我们会想到用Exists子查询来做了。

这是百度中对EXISTS的解释

EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False

EXISTS 指定一个子查询,检测 行 的存在。

以下为标准答案

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
select xh as 学号 , xm as 姓名
from s
where not exists
(
select *
from c
where not exists
(
select *
from sc
where sc.xh = s.xh and sc.kch = c.kch
)
)

结果:

结果

但是,要如何理解呢?

我是这样理解的:

通过c语言的循环遍历来理解

将每个表看成是一个数组,那么现在又三个数组s , c , sc , 假设i用来遍历s表 , j用来遍历c表 , k用来遍历sc表,那么,要实现这样的查询,需要这样:

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
for(int i = 0 ; i < s.length ; ++i)
{
for(int j = 0 ; j < c.length ; ++j)
{
for(int k = 0 ; k < sc.length ; ++i)
{
if(s[i].xh == sc[k].xh && c[j].kch == sc[k].kch)
continue;
}
}
cout<<"学号:"<<s[i].xh<<"\n"<<"姓名:"<<s[i].xm;
}

再来看看sql语句

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
select xh as 学号 , xm as 姓名
from s
1.where not exists
(
select *
from c
2.where not exists
(
select *
from sc
where sc.xh = s.xh and sc.kch = c.kch
)
)

如上标示,过程是:
先从s表中选出一条记录,然后将xh传入1层子查询,进入该子查询后,再从c表中选出一个课程记录,将kch传入2层子查询,这时候在`sc表中查找学号与传进来的xh相同并且其对应的课程号与传入的`kch相同:

假设查找到一条对应的记录,这时候应该返回上层查询,由于使用的是NOT EXISTS。所以返回的是false,这时不满足条件,继续从c表中取出下个课程信息,传入2层子查询,进行刚刚的步骤。假设现在查找完毕,即在sc表中没有与之对应的记录后返回true,这时显然2层子查询结束,然后再到1层子查询,容易得知,只有当1层子查询中将c表中的所有记录遍历后,才会返回true,这样就保证了题中所述“全部课程” , 当然如果没有将c表中的记录全部遍历一遍,那么将返回的是false,这样,子查询失败就意味着不会再屏幕上显示刚刚所查询的记录了

示例:

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
select xh as 学号 , xm as 姓名
from s
where not exists
(
select *
from c
where not exists
(
i. select *
ii. from sc
iii. where sc.xh = s.xh and sc.kch = c.kch
)
)

解释:
由第1句可知,从s表中选出一条记录,这就是 1002

然后进入第一层子查询,从c表中选出一条记录01

然后进入第二层子查询,在sc表中选择sc.xh =1002sc.kch=01,通过查找sc表发现存在该记录,这时向上层返回false,意思是查询失败,继续下一个

这时再从c表中选出 记录 02 , 传入2层子查询,这时在sc表中寻找sc.xh =1002sc.kch=02 ,发现存在,返回false ,继续下一个,这时再从c表中选出记录 03 , 传入2层子查询,这时在# sc #表中寻找sc.xh =1002sc.kch=03 ,发现存在,返回false

这时继续返回1层子查询,但是现在发现c表已经遍历完了,这时会由1层子查询向上级返回true,表明查询成功,结束并显示1002

继续
再从s表选出一条记录,为1001

然后进入第一层子查询,从c表中选出一条记录01

然后进入第二层子查询,在sc表中选择sc.xh =1001sc.kch=01,显然sc表中不存在这样的记录,返回true,这时2层子查询结束,然后由一层子查询返回false,表示c表没有被完全遍历,1层子查询失败,1001不显示在结果中

这就是exists的工作方式

热评文章