oracle_高级子查询

①子查询
子查询是嵌套在 SQL 语句中的另一个SELECT 语句
子查询 (内查询) 在主查询执行之前执行
主查询(外查询)使用子查询的结果

②多列子查询
主查询与子查询返回的多个列进行比较

where (column1,column2) in (子查询)

例：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id

多列子查询中的比较分为两种:
1)成对比较
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 (141,174))
AND employee_id NOT IN (141,174);

2)不成对比较
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);

③在 FROM 子句中使用子查询

例：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资

1)select last_name,department_id,salary,
(select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary
from employees e1
where salary >
(select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)

2)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;

④单列子查询表达式
单列子查询表达式是在一行中只返回一列的子查询
Oracle8i 只在下列情况下可以使用, 例如:
SELECT 语句 (FROM 和 WHERE 子句)
INSERT 语句中的VALUES列表中
Oracle9i中单列子查询表达式可在下列情况下使用:
DECODE 和 CASE
SELECT 中除 GROUP BY 子句以外的所有子句中

1)在 CASE 表达式中使用单列子查询
例：显式员工的employee_id,last_name和location。其中，
若员工department_id与location_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。

SELECT employee_id, last_name,
(CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada'
ELSE 'USA' END
) location
FROM employees;

2)在 ORDER BY 子句中使用单列子查询
例：查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);

⑤相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator (SELECT colum1, column2
FROM table2
WHERE expr1 = outer.expr2);

例：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >(SELECT AVG(salary)
FROM employees
WHERE department_id = outer.department_id)

例：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_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);
⑥EXISTS 操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
1.如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
2.如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找

例：查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);

例：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id
= d.department_id);