例题：
先来讲讲not in 和not exists的区别，再开始今天的例题（和in，exists相反）
not in内外表做笛卡尔积，然后按照条件查询，没有用到索引
not exists是对外表进行循环，每次循环再对内表进行查询，先查的外表的内容，不是子查询的内容，并且依然用到了表上的索引
两者最大的区别是：not in只能返回一个字段
如：
select week6s.SNO 一门课程没选的学生学号 from week6s where week6s.SNO not in( select week6sc.SNO from week6sc group by week6sc.SNO)
select week6s.SNO 一门课程都没选修的学生学号 from week6s where not exists( select * from week6sc where week6s.SNO=week6sc.SNO )
not in能表示的not exists也能表示（其实所有带in，比较运算符，any，all的子查询都能用exists表示），并且注意观察，两者引用的方式也不同，返回的结果是：
再着重讲一下两者的引用过程
not in相当于笛卡尔积的运算过程，找到符合not in前的一个字段的条件，不能是name，id not in
exists子查询的返回结果只返回真值或假值，所以给出列明没有实际的意义
对于exists，内层循环结果非空，外层where返回真值
对于not exists，内层循环结果非空，外层where返回假值
select * from week6sc where week6s.SNO=week6sc.SNO
内层循环返回结果非空，所以not exists 为false，这里的记录在exists会被记录下来
补充：‘=’和‘in’
当确切知道内层循环返回单值时，可用比较运算符（‘>’'<''=''!='）
select sno,sname,sdept from student where(select sdept from student where sname='刘')=sdept
在sql server中这条语句不会报错，但是sql标准要求子查询一定要跟在比较符之后，所以这条语句是错的。
1）检索至少选修一门课程的学生学号
select week6s.SNO 至少选修一门课程的学生学号 from week6s,week6sc group by week6s.SNO,week6sc.SNO having week6s.SNO=week6sc.SNO
2）检索一门课程都没选修的学生学号
select week6s.SNO 一门课程没选的学生学号 from week6s where week6s.SNO not in( select week6sc.SNO from week6sc group by week6sc.SNO)
3）检索至少选修两门课程的学生学号
1. select week6sc.SNO 至少选修两门课程的学生学号 2. from week6sc 3. group by week6sc.SNO 4. having count(week6sc.SNO)>1
这里也可以检索所有选了课的学生-选了小于两门课的学生，思路相同只是写起来比较繁琐
4）检索恰好选修一门课程的学生学号
select week6sc.SNO 选修了一门课程的学生学号 from week6s,week6sc group by week6s.SNO,week6sc.SNO having week6s.SNO=week6sc.SNO and week6sc.SNO not in( select week6sc.SNO from week6sc group by week6sc.SNO having count(week6sc.SNO)>1 )
5）检索至少选修三门课程的学生学号
select week6sc.SNO 至少选修三门课程的学生学号 from week6sc group by week6sc.SNO having count(week6sc.SNO)>2
6）检索全部课程都选的学生学号（不存在一门课，成绩表中找不到他这门课的成绩记录）
select week6s.SNO 选修了全部课程的学生学号 from week6s where not exists( select * from week6c where not exists( select * from week6sc where week6c.CNO=week6sc.CNO and week6sc.SNO=week6s.SNO ) )
7）检索选了S2所选全部课程的学生学号（同理，不存在一门课S2选了，而学生x没选）
select distinct sc1.SNO 选了S2所选的全部课程的学生学号 from week6sc sc1 where not exists( select * from week6sc sc2 where sc2.SNO='S2' and not exists( select * from week6sc sc3 where sc3.SNO=sc1.SNO and sc3.CNO=sc2.CNO ) )
法2：
select distinct SNO from week6sc where CNO>=all( select CNO from week6sc where SNO='s2' )
