sql之20高階查詢

Advanced Subqueries[@more@]

sql之20高階查詢
Advanced Subqueries
--'巢狀子查詢與關聯子查詢的區別'
Nested Subquery Execution
:) The inner query executes first and finds a value.
:) The outer query executes once, using the value from the inner query.
Correlated Subquery Execution
:) Get a candidate row (fetched by the outer query).
:) Execute the inner query using the value of the candidate row.
:) Use the values resulting from the inner query to qualify or disqualify the candidate.
:) Repeat until no candidate row remains.
--'關聯子查詢的特點：The subquery references a column from a table in the parent query.'

--example1: Find all employees who earn more than the average salary in their department.
SQL> select last_name,salary,department_id
2 from employees outer
3 where salary> (select avg(salary)
4 from employees
5 where department_id=outer.department_id);

LAST_NAME SALARY DEPARTMENT_ID
---------- ---------- -------------
King 24000 90
Lorentz 42000 60
Mourgos 5800 50
Zlotkey 10500 80
Abel 11000 80
Hartstein 13000 20
Higgins 12000 110

另外一種解決方案：
SQL> select a.last_name,a.salary,a.department_id,b.salavg
2 from employees a,(select department_id,avg(salary) salavg
3 from employees
4 group by department_id) b
5 where a.department_id=b.department_id
6 and a.salary>b.salavg;

--example2: Display details of those employees who have switched jobs at least twice.
SQL> select e.employee_id,e.last_name,e.job_id
2 from employees e
3 where 2<= (select count(*) from job_history where employee_id=e.employee_id);

EMPLOYEE_ID LAST_NAME JOB_ID
----------- ---------- ----------
101 Kochhar AD_VP
176 Taylor SA_REP
200 Whalen AD_ASST

--example3:Find employees who have at least one person reporting to them.
SQL> select employee_id,last_name,job_id,department_id
2 from employees outer
3 where exists (select 'x'
4 from employees
5 where manager_id=outer.employee_id);

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ---------- ---------- -------------
100 King AD_PRES 90
101 Kochhar AD_VP 90
102 De Haan AD_VP 90
103 Hunold IT_PROG 60
124 Mourgos ST_MAN 50
149 Zlotkey SA_MAN 80
201 Hartstein MK_MAN 20
205 Higgins AC_MGR 110
-- 與上面等價的但是卻低效的SQL語句
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);

-- 例4 將部門名稱新增到employees表中
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);