Oracle中含EXISTS查询语句分析

时间：2010-10-20 来源：fish1207

      刚学数据库，就拿课本《数据库系统概论(第四版)》中的例题说事吧。
先看下面三个表：
Student表：
学号姓名    性别年龄系别
SNO     SNAME               SSEX     SAGE SDEPT
--------- ------------------------ -------------- --- --------
200215121 李勇                 男         20 CS
200215122 刘晨                 女         19 CS
200215123 王敏                 女         18 MA
200215124 徐明                 男         17 EN
200215125 张立                 男         19 IS
200215126 李鹏                 男         20 EN
200215127 王飞                 男         19 IS

Course表：
课号课程名      先修课   学分
CNO CNAME                           CPNO    CCREDIT
---- ---------------------------------------- ---- ----------
2    数学                                     2
6    数据处理                              2
7    PASCAL语言                        6             4
5    数据结构                              7               4
1    数据库                                   5               4
3    信息系统                             1               4
4    操作系统                               7               4

SC表(学生选课表)：
学号课程号  分数
SNO       CNO       GRADE
--------- ---- ----------
200215121 1            92
200215121 2            85
200215121 3            88
200215122 2            90
200215122 3            80
200215127 2            99
200215125 3            49

下面进入正题：

一、查询学号为200215121的学生选修的所有课程:
至少有三种方法：
(1)
SQL> select SC.CNO,CNAME from Course,SC where
Sno=200215121 and SC.Cno=Course.Cno;

(2)
SQL> select CNO,CNAME from Course where Cno in
(select Cno from SC
where Sno=200215121 and Cno=Course.Cno);

(3)
SQL> select CNO,CNAME from Course where exists
(select * from SC
where Sno=200215121 and Cno=Course.Cno);

经验证，以上四种查询返回结果都是：

CNO CNAME
---- ----------------------------------------
1    数据库
2    数学
3    信息系统

第一、二个查询还好理解，第三个就有些抽象了，exists是什么意思呢？

看下面的问题：

查询学号为200215121的学生没有选修的所有课程

SQL语句为

SQL> select CNO,CNAME from Course where not exists
2 (select * from SC
3 where Sno=200215121
4 and Cno=Course.Cno);

结果：

CNO CNAME
---- -----------------------------
6    数据处理
5    数据结构
4    操作系统
7    PASCAL语言

经过多次查询，发现以下规律：
1) 当CNO为6、5、4、7时子查询结果都为空。
2) 当CNO为6、5、4、7时查询结果都为全集。
3) 当CNO不为6、5、4、7时查询结果都为空，但子查询结果都不为空。

可以看到，Course元组与SC元组并无直接关联，那么外层查询与内层查询是如何
联系起来的呢？通过以上规律发现，只有当子查询返回空时，外层查询(即整个查询)
才会返回非空值，但为什么总是返回全集呢？我们不防这样理解：内层查询返回空可
看作“假”，即false，因为EXISTS量词只关心子查询是否有返回值，当有返回值时，EXISTS
返回真，那么not exists (true) 返回假，所以not exists (false) 结果就是真，这样一来，
not exists (false) <=> true，整个查询语句等价于：select CNO,CNAME from Course where
true ! 这回恍然大悟，不就是一个简单投影嘛!

为了验证以上问题，执行以下语句(可以跳过)：

SQL> select Sname from Student where not exists
2 (select * from Course where Cno=9999);

SNAME
--------------------
李勇
刘晨
王敏
徐明
张立
李鹏
王飞

已选择7行。

有有认为上面语句的含义是：查询未选修课程号为9999的课程的学生姓名，因为根本就没有该课程，
当然所有的学生都进结果集。
看似理所当然吧，呵呵，如何你这样想，那就大错特错了！

为了对比一下，请再看下面的查询：
SQL> select Sname from Student where not exists
2 (select * from Course where Cno=3);

未选定行。

可以验证，只要Cno值存在，返回结果总为空。

如果按上面的理解，该语句的含义是：查询未选修课程号为3的课程的学生姓名。
但是每个学生都选了Cno为3的课程吗？当然不是!
所以not exists的意思是说，只有当子查询结果为空时，外层查询才会返回非空，它并不
关心内查询的细节，很抽象吧，没办法，只能这样理解了～，这也能成为上面“返回全集”
问题的一个佐证吧！

返回全集的迷解开了，那么子查询非空时整个查询为空也就顺理成章了。

难道这样我们就把问题解决了吗？上面只是针对Cno的某个具体值而探讨，这还不能
解释为什么整个循环完成后返回的却不是全集，看来真的是遇到难题了，想了好久，后来
从书上看到说“所有带有IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的
子查询等价替换”，于是发现，not exists XXX 不就是 not in XXX 吗？感觉像是管中窥豹，
这样细微一变，让我突然对整个语句的内部执行过程有了点想法：既然子查询为空时返回
全集，当然要找子查询不为空的元组了！于是慢慢思考，认为应该是这样：外层查询每次
取Course元组的一个Cno给内层查询，内层查询执行查询语句，若结果不为空（即学号为
200215121的学生选修了该Cno对应的课程），则放入内层查询结果集中，注意这时并不执行
外层查询，而是继续取下一个Course元组的一个Cno给内层查询，仍然执行内层查询，直到
遍历所有Course元组后，内层查询结束。此时内层查询的结果集里不是一个元组，而是三个!
它恰恰包含了学号为200215121的学生选修了的所有课程...哈哈，真相大白了...整个语句至此
变为
select CNO,CNAME from Course where not exists ( <1,数据库> , <2,信息系统> , <3,数学> );
哇，这不就是
select CNO,CNAME from Course where <CNO,CNAME> not in ( <1,数据库> , <2,信息系统> , <3,数学> );
吗？
好了，问题解决，可以吃饭去了~~

想一想，查询所有学生都未选修的课程，SQL语句该怎样写呢？