oracle_進階子查詢



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

⑦相關更新

UPDATE table1 alias1

SET    column = (SELECT expression

                 FROM   table2 alias2

                 WHERE  alias1.column =   

                        alias2.column);

使用相關子查詢依據一個表中的資料更新另一個表的資料

⑧相關删除

DELETE FROM table1 alias1

 WHERE  column operator

 (SELECT expression

   FROM   table2 alias2

   WHERE  alias1.column = alias2.column);

⑨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;