带有 EXISTS 操作符的子查询不返回任何数据，只产生逻辑真值 ‘true’ 或逻辑假值 ‘false’。带有 EXISTS 操作符的子查询都是相关子查询。
相关子查询：子查询的条件依赖父查询。
EXISTS：如果内层查询结果非空，则外层 WHERE 子句返回真值，输出外层查询结果。
NOT EXISTS：如果内层查询结果为空，则外层 WHERE 子句返回真值，输出外层查询结果。
样例表
create table Student(Sno ) primary key, --学号 Sname ) unique, --姓名);create table SC(sno ), --学号 cno ), --课程号 primary key (sno,cno), foreign key (sno) references student(sno));,'CS');,'CS');,'MA');,'IS'); ');');');');');
/*查询至少有一门课没选的学生姓名*/SELECT snameFROM StudentWHERE EXISTS (SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));-- 外层要有查询结果 中层查询结果要为非空 内层查询结果要为空 = 至少有一门课没选/*查询所有课都没选的学生姓名*/SELECT snameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));--外层要有查询结果 中层查询结果要为空 内层查询结果要为空 = 所有的课都没选/*查询至少选了一门课的学生姓名*/SELECT snameFROM StudentWHERE EXISTS (SELECT * FROM Course WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));--外层要有查询结果 中层查询结果要为非空 内层查询结果要为非空 = 选了至少一门课/*查询选了所有课的学生姓名*/SELECT snameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno));--外层要有查询结果 中层查询结果要为空 内层查询结果要为非空 = 选了所有的课/*用 GROUP BY */SELECT snameFROM Student LEFT JOIN SC ON Student.Sno=SC.snoGROUP BY SnameHAVING COUNT(cno)<= (SELECT COUNT(cno) ;SELECT snameFROM Student LEFT JOIN SC ON Student.Sno=SC.snoGROUP BY Sname;SELECT snameFROM Student LEFT JOIN SC ON Student.Sno=SC.snoGROUP BY Sname;SELECT snameFROM Student LEFT JOIN SC ON Student.Sno=SC.snoGROUP BY SnameHAVING COUNT(cno)= (SELECT COUNT(cno) FROM Course);
/*查询至少选修了学生 201215122 选修的全部课程*/SELECT DISTINCT snoFROM SC xWHERE NOT EXISTS (SELECT * FROM SC Y AND NOT EXISTS(SELECT * FROM SC Z WHERE Z.sno=X.sno AND Z.cno=Y.cno));--外层要有查询结果 中层查询结果要为空 内层查询结果要为非空 = 至少选修了201215122 选修的全部课程 SELECT cnoINTO copyFROM SC; --复制 201215122 选的课号到 copy 表SELECT DISTINCT snoFROM copy LEFT OUTER JOIN SC ON SC.cno=copy.cno --以 copy 表为全部行GROUP BY snoHAVING COUNT(SC.cno)= (SELECT COUNT(cno) --排序只选了 copy 表中的一门或几门课的 sno FROM copy);
