SQL語言基礎(子查詢)

子查詢語法：

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 資料庫像對待內聯檢視或臨時表一樣對待被引用的子查詢名稱，從而起到一定的最佳化作用