5.6子查询

Oracle 子查询

1、子查询语法

SELECT select_list
FROM table1
WHERE expr operator
(SELECT select_list
 FROM table1);

SELECT last_name
FROM employees
WHERE salary >
 (SELECT salary
 FROM employees
 WHERE last_name = 'Abel');

2、子查询注意点

单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN ， > ANY， > ALL 等

A.单行子查询

SELECT employee_id, last_name
FROM employees
WHERE salary =
 (SELECT MIN(salary)
 FROM employees);

B.多行子查询

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
 (SELECT salary
 FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

3、子查询进阶

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

SELECT a.last_name, a.salary,
 a.department_id, b.salavg
FROM employees a, (SELECT department_id,
 AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;

B.子查询中参考了外部主查询中的表

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id =
 outer.department_id) ;

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
 FROM job_history
 WHERE employee_id = e.employee_id);

C.使用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
WHERE employee_id IN (SELECT manager_id
                      FROM employees
                      WHERE manager_id IS NOT NULL);

D.使用Not Exists操作

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
 FROM employees
WHERE department_id
 = d.department_id);
SELECT department_id, department_name
 FROM departments
 WHERE department_id NOT IN (SELECT department_id
 FROM employees);

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

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

ALTER TABLE employees
ADD(department_name VARCHAR2(14));
UPDATE employees e
SET department_name =
 (SELECT department_name
 FROM departments d
 WHERE e.department_id = d.department_id);

F.在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));

I.使用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好处：
1）如果在后面多次使用则可以简化SQL ;
2）适当提高性能；