Oracle中含EXISTS查询语句分析

奥尔覅几22

　　刚学数据库，就拿课本《数据库系统概论(第四版)》中的例题说事吧。
　　先看下面三个表：
　　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 ! 这回恍然大悟，不就是一个简单投影嘛!
　　exists是相关子查询，特别要注意连接的问题，看以下语句(可以跳过)：
　　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的课程吗？当然不是!
　　细心一看才知道，原来子查询返回的元组与父查询表的元组根本就没有包含关系!这当然就没有可比性了，虽然子查询(select * from Course where Cno=3)返回结果不为空，但由于未和外层查询连接形成笛卡尔积，这时where条件好比是在判断两个不同性质的集合是否存在从属关系一样，回答当然是NO!
　　难道这样我们就把问题解决了吗？上面只是针对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 (  ,  ,  );
　　哇，这不就是
　　select CNO,CNAME from Course where  not in (  ,  ,  );
　　吗？
　　好了，问题解决，可以吃饭去了~~
　　想一想，查询所有学生都未选修的课程，SQL语句该怎样写呢？