Correlated Subqueries 相关子查询

计算过程：

1.扫描外查询的第一条记录

2.扫描子查询，并将第一条记录的对应值传给子查询，由此计算出子查询的结果

3.根据子查询的结果，返回外查询的结果。

4.重复上述动作，开始扫描外查询的第二条记录，第三条记录，直至全部扫描完毕

--Correlated Subqueries 相关子查询
--当子查询引用了父语句中的表的列时，Oracle执行一个相关子查询。
--要把相关子查询的执行过程背下来
--查询雇员，条件是挣得钱比他所在部门的平均薪水要高
select last_name,salary,department_id
  from employees e
  where salary > (select avg(salary)
                    from employees
                    where department_id= e.department_id);
 
--查询至少换过两次工作的雇员
select last_name,salary
  from employees e
  where 2 <= (select count(*)
                 from job_history
                 where employee_id = e.employee_id);
--编写相关子查询，经常用到exists操作符
--该操作符用来判断子查询的结果集是否不为空，如果不为空，
--则返回true，否则返回false
--查询所有是经理的雇员
select employee_id,last_name,job_id
  from employees e
  where exists (select 'x'
                  from employees
                  where manager_id = e.employee_id);
--注意，以上子查询的select列表中的'x'是什么？
--它就是一个字面量x。因为exists操作符不关心子查询返回的列
--具体是什么（换句话说，它不使用子查询的返回值），它只关心
--子查询的结果集是否为空。因此，写上字面量，使得Oracle
--不需要计算返回的列值，可以提高一点点效率。
--通常写x还麻烦，直接写1就完了
select employee_id,last_name,job_id
  from employees e
  where exists (select 1
                  from employees
                  where manager_id = e.employee_id);
--查询所有的没有任何雇员的部门
select department_id,department_name
  from departments d
  where not exists (select 1
                      from employees
                      where department_id = d.department_id);
--相关更新：在update语句中子查询是相关子查询
create table empl6
  as select * from employees;
 
--给empl6表添加一个列
alter table empl6
  add (department_name varchar2(20));
 
--要求根据每个雇员的部门编号，将部门名称列的值填好
update empl6 e
  set department_name=(select department_name
                         from departments
                         where department_id= e.department_id);
--相关删除：在delete语句中子查询是相关子查询
--假设empl6表是在职员工表，emp_history表是已经离职的
--雇员表。要求从empl6表中删除所有已经的离职的雇员
delete from empl6 e
  where employee_id=(select empid
                       from emp_history
                       where empid=e.employee_id);