认证目标:
- 定义子查询
- 描述子查询能够解决的问题的类型
- 列举子查询的类型
- 写单行和多行子查询
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
【注意】:
- 不能再values字句中出现select,除非它是单个值
- DML中的select子句的列投影名称可以与目标列不同,但是数据类型必须一一对应。
- 已知的值,可以直接写在子句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)