子查询语法：
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
子查询又称内部查询，而包含子查询的语句称之外部查询（又称主查询）。
所有的子查询可以分为两类，即相关子查询和非相关子查询
1> 非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后
将值传递给外部查询。值传递给外部查询。
2> 相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执
行一次。
1-1 单行子查询
只返回一行。
使用单行比较操作符。
SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
在子查询中使用组函数
SELECT last_name, job_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
1-2 多行子查询
返回多行。
使用多行比较操作符
使用 ANY 操作符
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';
使用 ALL 操作符
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
1-3 使用 EXISTS 操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找
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 department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id);
1-4 多列子查询
成对比较：
SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (178,174)) AND employee_id NOT IN (178,174);
不成对比较：
SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141);
1-5 单列子查询表达式
单列子查询表达式是在一行中只返回一列的子查询，可应用于以下子句
SELECT 语句 (FROM 和 WHERE 子句)
INSERT 语句中的VALUES 列表中
DECODE 和 CASE
SELECT 中除 GROUP BY 子句以外的所有子句中
------------------------------------------------------------------------------------------
在 CASE 表达式中使用单列子查询：
SELECT employee_id, last_name, (CASE WHEN department_id = THEN 'Canada' ELSE 'USA' END) location FROM employees;
在 ORDER BY 子句中使用单列子查询
SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);
在 FROM 子句中使用子查询
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;
1-6 WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中
使用 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 AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句的很多地方引用这个子查询。Oracle 数据库像对待内联视图或临时表一样对待被引用的子查询名称，从而起到一定的优化作用