Oracle資料庫進階子查詢

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;

Oracle資料庫進階查詢