第八章 使用子查询解决问题
认证目标：
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_numberfrom dual
??
(2) 写一个子查询，来确定所有是经理的员工：
思路：首先找出经理的MANAGER_ID是什么，然后再用子查询在employees表中寻找。
?
select * from employeeswhere 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 employeesusing(department_id) group by department_name;
?
【注意】：使用NOT IN会因为SQL处理NULL的方式带来问题，因此，通常不要使用NOT IN，除非您确定结果集中不包含NULL。
星形转换(Star Transformation)Oracle内部有一个实例初始化参数STAR_TRANSFORMATION_ENABLED,如果设置它为true时，Oracle查询优化器会讲代码重写为星形查询。
可以在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_idjoin 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)salfrom 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) bwhere 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)