第八章 使用子查询解决问题

认证目标：

• 定义子查询
• 描述子查询能够解决的问题的类型
• 列举子查询的类型
• 写单行和多行子查询

8.1 定义子查询

子查询是嵌套在select、insert、update或delete语句内或者其他子查询内的查询。

子查询为父查询返回一行或多行。

标量子查询(scalar subquery)：只返回一个值的查询，一行或一列。

查询中可以使用子查询的位置：

• • 列投影的select列表
  • from字句中
  • where字句中
  • having字句中

子查询又叫做内部查询(inner query)，含有内部查询的语句称为外部查询(outer query)。

• 练习8.1

子查询的类型：

(1) 写一个子查询，在列投影使用子查询，该查询会报告当前部门和员工数量：

select sysdate today,(
select count(*) from departments) department_number,
(select count(*) from employees) employee_number
from dual

  

(2) 写一个子查询，来确定所有是经理的员工：

思路：首先找出经理的MANAGER_ID是什么，然后再用子查询在employees表中寻找。

 

select * from employees
where employee_id in 
(select manager_id from employees)

 

   (3) 写一个子查询，来确定每个国家支付的最高薪水

 

select max(a.salary),c.country_id from employees a 
join departments b on(a.department_id=b.department_id) 
join locations c on(b.location_id=c.location_id)
group by country_id

 

 

 8.2 描述子查询能够解决的问题的类型

在许多情况下，需要将一个查询的结果作为另一个查询的输入。

• 将子查询的结果用于比较

  哪些员工的薪水低于平均薪水？

 

select * from employees where 
salary < (select avg(salary) sal from employees)

 

查询拥有一名或多名员工的部门：

 

select * from departments where department_id in
(select distinct department_id from employees)

 

 或者：

 

select department_name from departments  join employees
using(department_id) group by department_name;

 

【注意】：使用NOT IN会因为SQL处理NULL的方式带来问题，因此，通常不要使用NOT IN，除非您确定结果集中不包含NULL。

• 星形转换(Star Transformation)

Oracle内部有一个实例初始化参数STAR_TRANSFORMATION_ENABLED,如果设置它为true时，Oracle查询优化器会讲代码重写为星形查询。

• 生成执行select语句的表

可以在from字句中使用子查询，称为内联视图(inline views)

如果查询一个国家的员工的平均薪水：

 

select avg(salary) average,country_name from
(select * from employees a 
join departments b on a.department_id =b.department_id 
join locations c on b.location_id=c.location_id
join countries d on c.country_id=d.country_id
) group by country_name

 

 

• 生成投影值

在查询select中使用子查询。

最高薪水的员工，有最高的佣金率，那么需要支付的佣金是多少？

 

 

select
(select max(salary) from employees)
*(select max(commission_pct) from employees)
sal
from dual;

•  生成传递给DML语句的行

我想插入一条名称来自于departments的表的数据到regions表中。

 

 

insert into regions 
select 104,department_name from where manager_id=200

 

【注意】：

1. 不能再values字句中出现select，除非它是单个值
2. DML中的select子句的列投影名称可以与目标列不同，但是数据类型必须一一对应。
3. 已知的值，可以直接写在子句select中充当一列。

查询在英国的部门工作的员工：

 

select * from employees where department_id 
in (select department_id from departments where location_id 
in(select location_id from locations where country_id = 'UK'))

 

确定薪水高于平均值，且在IT部门工作的员工

 

select * from employees where salary > 
(select avg(salary) from employees) and department_id in
(select department_id from departments where department_name like '%IT%')

 

8.3 列举子查询的类型

子查询可以分为3类：

 

• 单行子查询
• 多行子查询
• 关联子查询

• 单行和多行子查询

单行子查询返回一行。

标量子查询返回一行一列。

多行子查询返回行集合。

可以在父查询的where和having子句中使用单行和多行子查询。

适用于单行子查询的比较运算符：=,>,>=,<,<=,<>

适用于多行子查询的比较运算符：IN,NOT IN,ANY,ALL

 

写一个查询，获得高于本部门平均工资的员工：

 

select * from employees a,
(select avg(salary) salary,department_id from employees group by department_id) b
where a.department_id = b.department_id and a.salary > b.salary;

 

写一个查询确定谁的工资比Mr.Tobias高：

 

select * from employees where salary > 
(select salary from employees where lower(last_name) = 'tobias')

 

ANY和ALL的使用：

运算符	含义
<ANY	小于最高
>ANY	大于最低
=ANY	等于IN
>ALL	大于最高
<ALL	小于最低

查询工资高于80部门的任何员工的员工：

select * from employees where salary > ALL
(select salary from employees where department_id = 80)