在众多的SQL语句中,not exists()算是难以理解的一种了。
第一次看到,你会觉得有点迷糊,但是似乎还能够理解。但是当你看到下面这样一个例子的时候就会谷歌百度之了。
有这样的两个关系:
STUDENTS表即学生表,里面的属性sid表示学生编号,sname表示学生姓名。
CHOICES表即选课表,里面有sid,cid和tid三个属性。sid是学生编号,cid是该学生所选的课程编号,tid是讲该课程教师编号。
下面要求的是:查询选修了所有课程的学生姓名。
使用两次not exists()的答案是:
select sname from STUDENTS where not exists
(select * from CHOICES as x where not exists
(select * from CHOICES as y wherey.sid=STUDENTS.sid and y.cid=x.cid));
首先看内层,select * from CHOICES as x where not exists (select * fro CHOICES as y where y.cid=x.cid);这句话是意思是查询在选课记录中,没有学生S选修课程X的记录。也就是存在课程X,学生S没有选的记录,列出来。
然后是整句话的理解:不存在上述情况,即选课记录中不存在学生S没有选的课程。那就是列出选了全部课程的学生。
还是费解吧?程序员就应该用代码来理解真正的执行过程:
R = NIL; // 初始设置结果集合为空集
open STUDENTS as C1; //准备扫描,设置游标C1
while not EOF(C1) do //依次扫描C1
begin
fetch C1 into x; //取一行STUDENTS记录
R1 = NIL; //初始化临时结果集,用于存放STUDENTS记录
open CHOICES as C2; //准备扫描,设置游标C2
while not EOF(c2) do //依次扫描C2
begin
fetch C2 into y; //取一行CHOICES记录
R2 = NIL; //初始化临时结果集,用于存放CHOICES记录
open CHOICES as C3; //准备扫描,设置游标C3
while not EOF(c3) do
begin
fetch C3 into z;
if (z.sid == STUDENTS.sid
and z.cid = y.cid
) then
begin //找到符合条件的记录
insert z into R2; //插入临时表
break; //对于(NOT) EXISTS, 找到一条即可
end;
end;
close C3;
if R2 is NIL then //第3层没找到过符合条件的?(NOT EXISTS)
begin
insert y into R1; //插入临时表
break; //找到一条即可
end;
end;
close C2;
if R1 is NIL then //第2层没找到过符合条件的?(NOT EXISTS)
begin
insert x into R; //插入最终结果集合
end;
end;
close C1;
return R; //得到结果
可以看到,这条SQL语句是从里层查找的,但每次要用外层的条件去匹配里层。只要找到一条符合条件的记录就退出。
如果你理解了就再来看一个例子吧:查询选修了教师号(tid)为1的教师 所开的所有课程的学生姓名。
由上述执行过程,我们应该把限定条件 tid=1 加在哪儿呢?如果是最内层,即
if (z.sid == STUDENTS.sid and z.cid = y.cid and z.tid='1' )
那么相应的SQL语句是:
select sname from STUDENTS where not exists
(select * from CHOICES as x where not exists
(select * from CHOICES as y where y.sid=STUDENTS.sid and y.cid=x.cid and y.tid=1));
这是不正确的,因为:
假如某节课不是这个老师教的,最内层循环一定找不到,这样满足R2 is NIL,就会跳出来,去找下一个学生了。但是这门课本来就不是这个老师教的,根本不应该拿来匹配。
所以我们应该把限定条件加在外层,先确定这门课是这个老师教的,然后才进行匹配。select sname from STUDENTS where not exists
(select * from CHOICES as x where x.tid=1 and not exists
(select * from CHOICES as y where y.sid=STUDENTS.sid and y.cid=x.cid));
这是实验的两张表。因为实在传不了资源,就以图片的形式呈现了,有兴趣的可以自己创建。
CHOICES表
STUDENTS表