子查询

目录

前言

一、无关子查询

1、比较子查询

2、SOME、ANY、ALL 和 IN子查询

3、子查询结果作为主查询的查询对象

二、相关子查询

---

前言

        SELECT 语句可以嵌套在其它语句中，这些语句包括 SELECT、INSERT、UPDATE 及DELETE 等。这些嵌套的 SELECT语句被称为子查询。

        当一个查询依赖于另外一个查询结果时，可以使用子查询，一般为查询条件不确定的情况。

一、无关子查询

        无关子查询的执行不依赖于外部查询。无关子查询在外部查询之前执行，然后将返回数据供外部查询使用，无关子查询中不包含对外部查询的任何引用。

1、比较子查询

        使用子查询进行比较测试时，通过等于（=）、不等于（<>）、小于（<）、大于（>）、小于或等于（<=）及大于或等于（>=）等比较运算符，将一个表达式的值于子查询返回的单值进行比较。

--  示例：查询与沈艳在同一个专业学习的学生的学号、姓名和专业。

select  sno, sname, specialty

from student

where  specialty = (select  specialty  from  student  where  sname = '沈艳')

--  使用自连接法

select  li.sno, li.sname, li.specialty

from student   li

inner  join  student  rl

on  li.specialty = rl.specialty  and  rl.sname= '沈艳'

注：子查询的 SELECT 语句不能使用 ORDER  BY 子句，ORDER  BY子句只能对最终查询结果进行排序。

--  示例：查询 C001号课的考试成绩比郑丽高的学生的学号和姓名。

select  student.sno, student.sname

from student

inner  join  sc

on  student.sno = sc.sno  and  sc.cno = 'C001'

        and  sc.score > (select score from sc where  cno = 'C001'  and 

                sno = (select  sno  from  student  where  sname = '郑丽'))

2、SOME、ANY、ALL 和 IN子查询

       SOME、ANY、ALL是一种逻辑运算符，作用是将子查询返回的单列值的集合与查询的单个值作比较。SOME、ANY、ALL前面需跟比较运算符（>，<，>=，<=，=，<>）。这里只有当子查询中的值为单值 或者与子查询中的值完全相等时，才可以使用 = 号，否则就不能使用 = 号。

        ALL 和 ANY操作符的常见用法是结合一个比较操作符对一个数据列子查询的结果进行测试。它们测试比较值是否与子查询所返回的全部或一部分值匹配。如果，比较值小于或等于子查询所返回的每一个值，<= ALL 将是 TRUE；只要比较小于或等于子查询所返回的任何一个值，<= ANY将是 TRUE。SOME 是 ANY的一个同义词。

--  示例：查询计算机专业年龄最大的学生的学号和姓名。

select sno, sname

from student

where  specialty = '计算机'  and

        birthday <= all (select  birthday from student  where  specialty = '计算机' )

--  查询与任何一个通信工程专业学生同龄的学生的信息。

select  *  from  student

where  year(birthday) = any

        (select  year(birthday)  from student  where  specialty = '通信工程')

        实际上，IN 和 NOT  IN 操作符是 ' = ANY ' 和 ' <> ALL ' 的简写。也就是说，IN操作符的含义是 ' 等于子查询所返回的某个数据行 '，NOT  IN 操作符的含义是 ' 不等于子查询所返回的任何数据行 ' 。

3、子查询结果作为主查询的查询对象

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

select  sname, specialty  from  student

where  sno  in  (select  sno from  sc  where  cno = 'C001')

--  示例：查询两个以上学生平均成绩超过80分的班级（用年级和专业表示）。

select  grade, specialty  from  student  li,

( select  sno  from  sc  group  by  sno  having  avg(score) >= 80 )  rl

where  li.sno = rl.sno

group  by  grade,specialty

having  count(*) >= 2

        注：当需要由多张表联合查询，且其中存在表需先经过筛选处理后才能进行关联查询的，就可以使用子查询结果作为主查询的查询对象，先把表进行筛选处理后将筛选结果集作主查询对象进行查询。

SOME、ANY、ALL的详细介绍请参考推文

二、相关子查询

        在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的 WHERE 子句中引用了外部查询的表。

        相关子查询的执行过程于无关子查询完全不同，无关子查询中子查询只执行一次，而相关子查询中的子查询需要重复多次执行。

        相关子查询的执行过程：

1. 子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传递给子查询。
2. 如果子查询的任何行与其匹配，外部查询就返回结果行。
3. 再回到第一步，直到处理完外部表的每一行。

1、比较子查询

--  示例：查询成绩比该课的平均成绩低的学生的学号、课程号 和成绩。

select  sno, cno, score  from  sc  li

where  score < ( select  avg(score)  from  sc  rl   where   li.cno= rl.cno )

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

select   li.sno, sname, grade, specialty  from  student  li

where  (select  count(*)   from  sc  rl  where  rl.sno = li.sno  and  score >= 80) >=2

2、带有 EXISTS 的子查询（存在性测试）

        使用子查询进行判断是否存在时，通过逻辑运算符 EXISTS 或 NOT  EXISTS，检查子查询所返回的结果集是否有数据行存在。使用 EXISTS 时，如果子查询结果集中满足有一行或多行，则返回 TRUE；否则返回 FALSE。NOT  EXISTS 是 EXISTS 的结果取反。

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

select  sname  from  student

where   exists (select  *  from  sc  where  sno = student.sno  and  cno = 'C004')

        由 EXISTS 引出的子查询，其目标属性字段表达式一般用 * 表示，因为 EXISTS 的子查询只返回布尔值，对给出的列名无实际意义。

        若内层子查询结果为非空，则外层的 WHERE 子句的条件为真（TRUE），否则为假（FALSE）。

使用子查询时的注意点：

• 子查询需要使用括号 ( ) 括起来。
• 子查询可以嵌套。
• 子查询的 SELECT 语句中不能使用 image、text 和 ntext数据类型。
• 子查询返回的结果的数据类型不想匹配外围查询 WHERE语句的数据的类型。
• 子查询中不能使用 ORDER  BY子句。