SQL 子查询可以分为相关子查询 与 非相关子查询。
假设Books表如下:
类编号 图书名 出版社 价格
--------------------------------------------------------
2 c#高级应用 圣通出版 23.00
2 Jsp开发应用 机械出版社 45.00
3 高等数学 济南出版社 25.00
3 疯狂英语 清华大学出版社 32.00
一、嵌套子查询的执行不依赖与外部的查询。
执行过程:
(1)执行子查询,其结果不被显示,而是传递给外部查询,作为外部查询的条件使用,子查询中不能有order by分组语句。
(2)执行外部查询,并显示整个结果。
嵌套子查询一般可以分为:返回单值的子查询 和 返回一个列表的子查询 ,
下面举例说明:
1.返回单值: --查询所有价格高于平均价格的图书名,作者,出版社和价格。 SElECT 图书名,作者,出版社,价格 FROM Books WHERE 价格 > ( SELECT AVG(价格) FROM Books ) GO
2.返回值列表--查询所有借阅图书的读者信息
SElECT * FROM Readers WHERE 读者编号 IN ( SELECT 读者编号 FROM [Borrow History] ) GO
二、相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表
执行过程:
(1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
(2)执行内层查询,得到子查询操作的值。
(3)外查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。
下面举例说明:
查询Booka表中大于该类图书价格平均值的图书信息
SElECT 图书名,出版社,类编号,价格
SELECT FROM Books As a WHERE 价格 > ( SELECT AVG(价格) FROM Books AS b WHERE a.类编号=b.类编号 ) GO 与前面介绍过的子查询不同,相关子查询无法独立于外部查询而得到解决。该子查询需要一个“类编号”的值。而这个值是个变量,随SQLSever检索Books表中的不同行而改变。
下面详细说明该查询执行过程: 先将Books表中的第一条记录的“类编号”的值“2”代入子查询中,子查询变为: SELECT AVG(价格) FROM Books AS b WHERE b.类编号=2 子查询的结果为该类图书的平均价格,所以外部查询变为: SElECT 图书名,出版社,类编号,价格 FROM Books As a WHERE 价格 > 34 如果WHERE条件为True,则第一条结果包括在结果集中,则否不包括。对Books表中的所有行运行相同的过程,最后形成的结果集及最后返回结果。
在嵌套中使用exists关键字[存在]
例:1:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select * from 嵌套work a where exists (select * from 嵌套部门 b where a.姓名=b.姓名 and a.职工号=b.职工号)
2:在work表检索出在部门表没有的职工
select * from work where not exists (select * from 部门 where 部门.部门编号=work.部门编号)
能否改成:select * from work where exists (select * from 部门 where 部门.部门编号<>work.部门编号) 是不能的,
在列清单中使用select
例:1:在work1表和部门表中检索出所有部门的部门名称和基本工资总和
select 部门名称,(select sum(基本工资) from work1 b where a.部门编号=b.部门编号) from 部门 a
2:检索各部门的职工人数
select 部门编号,部门名称,(select count(职工号) from work1 a where a.部门编号=b.部门编号) as 人数 from 部门 b
3:在商品表和销售表中查询每一职工的姓名,所属部门,销售总量
select 姓名,所属部门,(select sum(销售量) from 商品销售 a where a.职工号=b.职工号) as 销售总量 from 嵌套部门 b
说明:都是相关子查询的特殊情况,外层的查询是内层查询的条件如:a.职工号=b.职工号,内层条件成立则得到的些行记录是并入外层查询的最终结果,否则不记录入最后结果
-------------------------------------------------------------------------------------------------------------------------------------------------------------
3:相关子查询(多值子查询)
1>非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。
2>相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。
查询中再查询,通常是以一个查询作为条件来供另一个查询使用
例:有work表和部门表
A:检索出在部门表中登记的所有部门的职工基本资料
select * from work where 部门编号 in [not in](select 部门编号 from dbo.部门)
B:检索出在work表中每一个部门的最高基本工资的职工资料
select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)
说明:由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.
相当于:select * from work,(select 部门名称,max(基本工资) as 基本工资 from work group by 部门名称 as t) where work.基本工资=t.基本工资 and work.部门名称=t.部门名称
C:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select * from 嵌套work where 职工号 in (select 职工号 from 嵌套部门) and 姓名 in (select 姓名 from 嵌套部门) [察看结果,分析原因] (错误,因为这两个in不是一对一
改:select * from 嵌套work a,嵌套部门 b where a.职工号=b.职工号 and a.姓名=b.姓名
改:select * from 嵌套work where 职工号=(select 职工号 from 嵌套部门) and 姓名=(select 姓名 from 嵌套部门) [行吗?为什么,分析原因?] 不能后面的select得到的结果不是一个值而又跟在=后必然出错
在嵌套中使用exists关键字[存在]
例:1:用嵌套work表和嵌套部门表,在嵌套work表中检索出姓名和职工号都在嵌套部门存在的职工资料
select * from 嵌套work a where exists (select * from 嵌套部门 b where a.姓名=b.姓名 and a.职工号=b.职工号)
2:在work表检索出在部门表没有的职工
select * from work where not exists (select * from 部门 where 部门.部门编号=work.部门编号)
能否改成:select * from work where exists (select * from 部门 where 部门.部门编号<>work.部门编号) 是不能的,
在列清单中使用select
例:1:在work1表和部门表中检索出所有部门的部门名称和基本工资总和
select 部门名称,(select sum(基本工资) from work1 b where a.部门编号=b.部门编号) from 部门 a
2:检索各部门的职工人数
select 部门编号,部门名称,(select count(职工号) from work1 a where a.部门编号=b.部门编号) as 人数 from 部门 b
3:在商品表和销售表中查询每一职工的姓名,所属部门,销售总量
select 姓名,所属部门,(select sum(销售量) from 商品销售 a where a.职工号=b.职工号) as 销售总量 from 嵌套部门 b
说明:都是相关子查询的特殊情况,外层的查询是内层查询的条件如:a.职工号=b.职工号,内层条件成立则得到的些行记录是并入外层查询的最终结果,否则不记录入最后结果
许多查询都可以通过执行一次子查询并将得到的值代入外部查询的 WHERE 子句中进行计算。在包括相关子查询(也称为重复子查询)的查询中,子查询依靠外部查询获得值。这意味着子查询是重复执行的,为外部查询可能选择的每一行均执行一次。