sql中的无关子查询与相关子查询和集合查询详细举例说明

无关子查询

比较子查询：

例：查询与“小仙女”在同一个专业学习的学生信息

SELECT *  FROM student
WHERE specialty =
     (SELECT specialty  FROM  student WHERE sname='小仙女')

子查询不能用order by排序

例：查询C001号课的考试成绩比“小仙女”高的学生的学号和姓名

SELECT student.sno,sname 
FROM student,sc
WHERE student.sno = sc.sno and cno='C001' 
         and score>
          (SELECT score 
            FROM sc 
            WHERE cno='C001' and
           sno=(SELECT sno FROM student WHERE sname='小仙女')-- 找出学号
       )-- 找出成绩

ALL 、ANY(SOME )和IN子查询

运算符	含义
ALL	如果一系列的比较都为 TRUE，那么就为 TRUE。
ANY	如果一系列的比较中任何一个为 TRUE，那么就为 TRUE。
SOME	如果在一系列比较中，有些为 TRUE，那么就为 TRUE。
IN	如果操作数等于表达式列表中的一个，那么就为 TRUE。

例：查询选修了“C001”号课程的学生姓名和所在专业

SELECT sname,specialty FROM student
WHERE sno IN
   (SELECT sno FROM sc  WHERE cno=’C001’)
或
SELECT sname,specialty FROM student
WHERE sno = any
   (SELECT sno FROM sc  WHERE cno=’C001’)
或
SELECT sname,specialty FROM student
WHERE sno = some
   (SELECT sno FROM sc  WHERE cno=’C001’)

子查询结果作为临时表

例：查询有2门以上课程的成绩在80分以上的学生的学号、姓名、年级和专业

SELECT s.sno,sname,grade,specialty FROM student s,
      (SELECT sno FROM SC where score >80 GROUP BY sno having COUNT(*)>2) ss
WHERE s.sno=ss.sno 

• 这题用相关子查询的方法同样可解

SELECT sno,sname,grade,specialty FROM student s
   WHERE (SELECT COUNT(*) FROM sc WHERE sc.sno=s.sno and score >80)>2

相关子查询

比较子查询

查询成绩比该课的平均成绩低的学生成绩信息

SELECT * FROM  sc a  
WHERE score< ( SELECT avg(score)  FROM sc b WHERE a.cno=b.cno)

注意聚合函数必须搭配select语句使用不能单独使用

EXISTS子查询

• EXISTS代表存在量词Э，它允许高效地检查指定查询是否产生某些行。带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值TRUE或逻辑假值FALSE。

例：查询所有选修了C004号课程的学生姓名

SELECT sname 
FROM student
WHERE EXISTS(SELECT * FROM sc WHERE sno=student.sno 
AND cno='C004')

• 注意：EXISTS的子查询只返回真值或假值, 给出列名无实际意义，故一般用xing号。

查询没有选修C004号课程的学生姓名

SELECT Sname  FROM Student
WHERE NOT EXISTS
         (SELECT *   FROM SC
          WHERE Sno = Student.Sno AND Cno='C004')

注意所有方法不唯一

• 若子查询的查询结果非空，则EXISTS为真，否则为假。
• NOT EXISTS 查询结果非空则为假，空则为真。

查询选修了全部课程的学生姓名

• 选修了全部课程 ⟺ 没有一门课是他（她）不选的

select name
FROM Student
WHERE NOT EXISTS (SELECT *
                  FROM Course 
				  WHERE NOT EXISTS (SELECT * 
	                                FROM SC
	                                WHERE SNO=Student.SNO 
	                                AND CNO=Course.CNO)
                  )

在相关子查询中一层相当于一层for循环会把每一层的每一个元素都遍历一遍
[[sql查询基本知识#sql语句执行顺序|sql语句执行顺序]]

集合查询

并查询

Select语句 
            UNION [ ALL ] 
Select语句 

例：查询选修了课程C001或选修了课程C004的学生的学号：

其它方法：
SELECT distinct sno FROM sc
WHERE cno='C001' or cno='C004'
或
SELECT distinct sno FROM sc 
WHERE cno in ('C001','C004')

SELECT sno FROM sc WHERE cno='C001'
UNION
SELECT sno FROM sc WHERE cno='C004'

交查询

查询选修了课程C001又选修了课程C004的学生的学号

SELECT sno FROM sc WHERE cno='C001'
INTERSECT
SELECT sno FROM sc WHERE cno='C004'

其它实现方法：
SELECT a.sno 
FROM sc a,sc b
WHERE a.sno=b.sno and a.cno='C001' and b.cno='C004'
或
SELECT sno 
FROM sc 
WHERE cno='C001' and sno in 
( 
   SELECT sno FROM sc WHERE cno='C004'
)

差查询

查询选修了课程C001但没有选修课程C004的学生的学号

SELECT sno FROM sc WHERE cno='C001'
EXCEPT
SELECT sno FROM sc WHERE cno='C004'

其他方法：
SELECT sno FROM sc 
WHERE cno='C001' and sno not in 
( 
  SELECT sno FROM sc WHERE cno='C004'
)