SQL子查询和嵌套查询

什么是嵌套查询
嵌套查询是指在一个外层查询中包含有另一个内层查询，即一个SQL查询语句块可以嵌套在另一个查询块的WHERE子句中。

其中外层查询称为父查询、主查询。

内层查询也称为子查询、从查询。

什么是子查询
子查询是SELECT语句内的另外一条SELECT语句，而且常常被称为内查询或是内SELECT语句。

SELECT、INSERT、UPDATE或DELETE命令中允许是一个表达式的地方都可以包含子查询，子查询甚至可以包含在另外一个子查询中。

1．语法
子查询的句法如下：
（SELECT [ALL | DISTINCT]<select item list>
FROM <table list>
[WHERE<search condition>]
[GROUP BY <group item list>
[HAVING <group by search conditoon>]]）
2．语法规则
（1）子查询的SELECT查询总使用圆括号括起来。

（2）不能包括COMPUTE或FOR BROWSE子句。

（3）如果同时指定TOP子句，则可能只包括ORDER BY 子句。

（4）子查询最多可以嵌套32层，个别查询可能会不支持32层嵌套。

（5）任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值。

（6）如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含在输出中。

3．语法格式
（1）WHERE 查询表达式[NOT] IN（子查询）。

（2）WHERE 查询表达式比较运算符[ ANY | ALL ]（子查询）。

（3）WHERE [NOT] EXISTS（子查询）。

简单嵌套查询
嵌套查询内层子查询通常作为搜索条件的一部分呈现在WHERE或HA VING子句中。

例如，把一个表达式的值和一个由子查询生成的一个值相比较，这个测试类似于简单比较测试。

子查询比较测试用到的运算符是：=、<>、<、>、<=、>=。

子查询比较测试把一个表达式的值和由子查询的产生的一个值进行比较，返回比较结果为TRUE的记录。

下面在“student”表中，查询“课程成绩”>96分的同学信息。

在查询分析器中输入的SQL语句如下：
use student
select *
from student
where 学号=( select 学号from grade where 课程成绩>96 )
实现的过程如图1所示。

图1 成绩大于90分的同学信息
子查询“select 学号from grade where 课程成绩>96”的查询结果是“B003”，即是一个值。

子查询过程如下：
（1）从“grade”表中查询出“课程成绩”>96的学生学号为“B003”。

（2）从学生表中查询出“学号”是“B003”的学生信息。

带IN的嵌套查询
带IN的嵌套查询语法格式为：WHERE 查询表达式IN（子查询）。

一些嵌套内层的子查询会产生一个值，也有一些子查询会返回一列值，即子查询不能返回带几行和几列数据的表。

原因在于子查询的结果必须适合外层查询的语句。

当子查询产生一系列值时，适合用带IN的嵌套查询。

把查询表达式单个数据和由子查询产生的一系列的数值相比较，如果数值匹配一系列值中的一个，则返回TRUE。

下面在“student”和“grade”表中，查询参加考试的同学信息。

在查询分析器中输入的SQL语句如下：
use student
select *
from student
where 学号in (select 学号from grade )
实现的过程如图1所示。

图1 参加考试的同学信息
子查询“select 学号from grade”的结果如图2所示。

图2 子查询结果
子查询生成“grade”表中学号列的数值，WHERE子句检查主查询记录中的值是否与子查询结果中的数值匹配，匹配返回TRUE值。

由于主查询记录的“B006”的学号值与子查询结果的数值不匹配，所以查询结果不显示学号为“B006”的记录信息。

带IN的内层嵌套还可以是多个值的列表。

例如，查询“年龄”是“22、21、24”的学生信息。

SQL语句如下：
use student
select *
from student
where 年龄in(21,22,24)
带NOT IN的嵌套查询
NOT IN的嵌套查询语法格式：WHERE 查询表达式NOT IN（子查询）
NOT IN和IN的查询过程相类似。

在“course”和“grade”表中，查询没有考试的课程信息。

SQL语句如下：
use student
select *
from course
where 课程代号not in
(select 课程代号from grade where 课程代号is not null )
实现的过程如图1所示。

图1 没有考试的课程信息
查询过程是用主查询中“课程代号”的值与子查询结果中的值比较，不匹配返回真值。

由于主查询中的“k05”和“k06”的课程代号值与子查询的结果的数据不匹配，返回真值。

所以查询结果显示课程代号为“k05”和“k06”的课程信息。

子查询存在NULL值时，避免使用NOT IN。

因为当子查询的结果包括了NULL值的列表时，把NULL值当成一个未知数据，不会存在查询值不在列表中的记录。

下面在“student”和“grade”表中，查询没参加考试的同学。

程序运行的结果如图2所示。

图2 没参加考试的同学信息
SQL语句如下：
use student
select *
from student
where 学号not in
(select 学号from grade
)
由于子查询的结果包括了NULL值，所以最终的查询结果只有空值。

正确查询的SQL 语句如下：
use student
select *
from student
where 学号not in
(select 学号from grade where 学号is not null)
实现的过程如图3所示。

图3 正确的没参加考试的同学信息
综上所述，当嵌套子查询中存NULL值，避免使用NOT IN。

带SOME的嵌套查询
SQL支持3种定量比较谓词：SOME、ANY和ALL。

它们都是判断是否任何或全部返回值都满足搜索要求的。

其中SOME和ANY谓词是存在量的，只注重是否有返回值满足搜索要求。

这两种谓词含义相同，可以替换使用。

下面在“student”表中，查询“年龄”小于平均年龄的所有学生信息。

在查询分析器中输入的SQL语句如下：
select *
from student
where 年龄<some
(select avg(年龄) from student)
实现的过程如图1所示。

图1 用SOME查询“年龄”小于平均年龄的学生
SOME是把每一行指定的列值与子查询的结果进行比较，如果哪行的比较结果为真，满足条件就返回该行。

本实例的嵌套的子查询结果就是一个数值，然后“student”学生表的每一条记录的年龄列值与之相比较，满足就返回行。

带ANY的嵌套查询
ANY属于SQL支持的3种定量谓词之一。

且和SOME完全等价，即能用SOME的地方完全可以使用ANY。

使用ANY解决上述示例。

在查询分析器中输入的SQL语句如下：
use student
select *
from student
where 年龄<any
(select avg(年龄) from student)
实现的过程如图1所示。

图1 用ANY查询“年龄”小于平均年龄的学生
SQL中定量谓词不支持反操作，也就是说，不能在ANY或者SOME前加NOT关键字。

但可以用“<>”号表示否定。

下面在“student”表中，查询“年龄”不等于平均年龄的所有学生信息。

在查询分析器中输入的SQL语句如下：
select *
from student
where 年龄<>any
(select avg(年龄) from student)
实现的过程如图2所示。

图2 年龄不等于平均年龄的学生
带ALL的嵌套查询
ALL谓词的使用方法和ANY或者SOME谓词一样，也是把列值与子查询结果进行比较，但是它不要求任意结果值的列值为真，而是要求所有列的查询结果都为真，否则就不返回行。

下面查询除了这些“课程代号”的课程信息，这些“课程代号”是指相应的“课程成绩”大于90分的“课程代号”。

在查询分析器中输入的SQL语句如下：
use student
select *
from course
where 课程代号<> all
(select 课程代号from grade where 课程成绩>90)
实现的过程如图1所示。

图1 带ALL的嵌套查询
SQL的这几个定量谓词，都可以使用6种比较运算符中的任何运算符。

带EXISTS的嵌套查询
EXISTS谓词只注重子查询是否返回行。

如果子查询返回一个或多个行，谓词返回为真值，否则为假。

EXISTS搜索条件并不真正地使用子查询的结果。

它仅仅测试子查询是否产生任何结果。

用带in的嵌套查询也可以用带EXISTS的嵌套查询改写。

下面把用带IN的嵌套查询写的例子改为用EXISTS书写的嵌套查询。

在查询分析器中输入的SQL语句如下：
use student
select *
from student
where exists
(select 学号from grade where student.学号=grade.学号)
实现的过程如图1所示。

图1 查询参加考试的学生信息
EXISTS谓词子查询中的SELECT子句中可使用任何列名，也可使用任意多个列，这种谓词只注重是否返回行，而不注重行的内容。

用户可以规定任何列名或者只使用一个星号。

例如上述例子的SQL语句和下面的SQL语句完全等价。

use student
select *
from student
where exists
(select * from grade where student.学号=grade.学号)
NOT EXISTS的作用与EXISTS正相反。

如果子查询没有返回行，则满足NOT EXISTS 中的WHERE子句。

下面查询没参加考试的同学信息。

在查询分析器中输入的SQL语句如下：
use student
select *
from student
where not exists
( select * from grade where student.学号=grade.学号)
实现的过程如图2所示。

图2 查询没参加考试的同学信息。