第十五单元：子查询进阶

非相关子查询当作一张表来用：

select e.last_name,e.salary,e.department_id,b.salavg
from employees e,(select department_id,avg(salary) salavg
                  from employees
                  group by department_id) b
where e.department_id = b.department
      and e.salary > b.salavg;

这段sql含义是查询出薪水大于其所在部门平均薪水的员工的相关信息。

---

相关子查询的概念：子查询中参考了外部主查询中的表。

select last_name,salary,department_id
from employees outer
where salary > (select avg(salary)
                from employees
                where department = outer.department_id);

含义是查询出薪水大于其所在部门平均薪水的员工的相关信息。

select e.employee_id,e.last_name,e.job_id
from employees e
where 2 <= (select count(*)
            from job_history
            where employee_id = e.employee_id);

含义是查询出离职次数大于两次的员工的相关信息。

---

使用exists操作：

select employee_id,last_name,job_id,department_id
from employees outer
where exists (select 'X'
              from employees
              where manager_id = outer.employee_id);

select employee_id,last_name,job_id,department_id
from employees outer
where employee_id in (select manager_id
                      from employees
                      where manager_id is not null);

这两个SQL结果一样，但执行性能是否一样呢？

---

使用 Not Exists操作：

select department_id,department_name
from departments d
where not exists (select 'X' 
                  from departments
                  where department_id = d.department_id);

select department_id,department_name
from departments d
where department_id not in (select department_id 
                            from departments);

查询出没有员工的部门。
这两个SQL结果一样吗，同样，请比较一下其执行性能

注意：Not In 里面只要有一个NULL ，就不成立了，这是很容易出错的地方； 正确的方法请在后面的子查询中加上where department_id is not null;

select department_id,department_name
from departments d
where department_id not in (select department_id 
                            from departments
                            where department_id is not null);

---

在Update 语句中使用相关子查询:

alter table employees
add (department_name varchar2(14));

update employees e
set e.department_name = (select d.department_name
                            from departments d
                            where e.department_id = d.department_id);

在DELETE 语句中使用相关子查询:

delete from job_history JH
where employee_id = (select employee_id
                     from employees E
                     where JH.employee_id = E.employee_id
                           and start_date = (select min(start_date)
                                             from job_history JH
                                             where JH.employee_id = E.employee_id)
                                                   and 5 > (select count(*)
                                                            from job_history JH
                                                            where JH.employee_id = E.employee_id
                                                            group by employee_id
                                                            having count(*) >= 4));

使用WITH语句：

WITH dept_costs AS
 (SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost AS
 (SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT *
  FROM dept_costs
 WHERE dept_total > (SELECT dept_avg FROM avg_cost)
 ORDER BY department_name;

使用WITH好处：

• 如果在后面多次使用则可以简化
• 适当提高性能