带有 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 sname
FROM Student
WHERE EXISTS (SELECT *
FROM Course
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
-- 外层要有查询结果 中层查询结果要为非空 内层查询结果要为空 = 至少有一门课没选
/*查询所有课都没选的学生姓名*/
SELECT sname
FROM Student
WHERE NOT EXISTS (SELECT *
FROM Course
WHERE EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
--外层要有查询结果 中层查询结果要为空 内层查询结果要为空 = 所有的课都没选
/*查询至少选了一门课的学生姓名*/
SELECT sname
FROM Student
WHERE EXISTS (SELECT *
FROM Course
WHERE EXISTS (SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
--外层要有查询结果 中层查询结果要为非空 内层查询结果要为非空 = 选了至少一门课
/*查询选了所有课的学生姓名*/
SELECT sname
FROM Student
WHERE NOT EXISTS (SELECT *
FROM Course
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=Course.Cno));
--外层要有查询结果 中层查询结果要为空 内层查询结果要为非空 = 选了所有的课
/*用 GROUP BY */
SELECT sname
FROM Student LEFT JOIN SC ON Student.Sno=SC.sno
GROUP BY Sname
HAVING COUNT(cno)<= (SELECT COUNT(cno)
;
SELECT sname
FROM Student LEFT JOIN SC ON Student.Sno=SC.sno
GROUP BY Sname
;
SELECT sname
FROM Student LEFT JOIN SC ON Student.Sno=SC.sno
GROUP BY Sname
;
SELECT sname
FROM Student LEFT JOIN SC ON Student.Sno=SC.sno
GROUP BY Sname
HAVING COUNT(cno)= (SELECT COUNT(cno)
FROM Course);
/*查询至少选修了学生 201215122 选修的全部课程*/
SELECT DISTINCT sno
FROM SC x
WHERE 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 cno
INTO copy
FROM SC
; --复制 201215122 选的课号到 copy 表
SELECT DISTINCT sno
FROM copy LEFT OUTER JOIN SC ON SC.cno=copy.cno --以 copy 表为全部行
GROUP BY sno
HAVING COUNT(SC.cno)= (SELECT COUNT(cno) --排序只选了 copy 表中的一门或几门课的 sno
FROM copy);