(Les17 Retrieving Data Using Subqueries)[20180103]

學習目標： -多列子查詢 -SQL語句中使用標量子查詢 -更新或刪除行使用關聯子查詢 -使用EXISTS和NOT EXISTS操作符 -使用WITH子句多列子查詢語法： Main query WHERE (<column_name>,<column_name>) IN Subquery; 列的比 ...

學習目標： -多列子查詢 -SQL語句中使用標量子查詢 -更新或刪除行使用關聯子查詢 -使用EXISTS和NOT EXISTS操作符 -使用WITH子句多列子查詢語法： Main query WHERE (<column_name>,<column_name>) IN Subquery; 列的比較 -非成對比較(兩兩比較) -成對比較

　　　　非成對比較
                SELECT <column>,[<column>,| <column>]
                FROM <table_name>
                WHERE <column> IN Subquery
                AND <column> IN Subquery;
        成對比較
                SELECT <column>,[<column>,|<column>]
                FROM <table_name>
                WHRE (<column>,<column>) IN
                    (SELECT <column>,<column> FROM <table_name>
                      WHERE clause);

標量子查詢 -標量子查詢表達式中的子查詢返回一個值 -標量子查詢表達式中可以含有decode和case函數 -標量子查詢中需排除GROUP BY -標量子查詢可以在UPDATESQL語句SET子句中和WHERE條件標量子查詢不能用於： -列的預設值和集群的散列表達式 -DML語句RETURNING條件中 -基於函數的索引 -GROUP BY子句、CHECK約束和WHEN子句 -CONNECT BY子句 -與查詢無關的語句中，如CREATE PROFILE概要文件

　　　　例子：
                SELECT <column>,<column>
                    (CASE 
                        WHEN <column> = (SELECT <column> 
                                                            FROM <table_name>
                                                            WHERE clause)
                         THEN 'expression' END) <column_alias>
                 FROM <table_name>;

關聯子查詢 -子查詢引用主查詢語句中的列值，Oracle執行相關子查詢。 -對於主查詢中的每一行，相關的子查詢都會執行一次。 -主查詢可以是SELECT/UPDATE或DELETE語句。

Nested Subqueries(嵌套子查詢)或Correlated Subqueries(關聯子查詢) -嵌套子查詢：子查詢首先運行並執行一次，返回結果給主查詢。 -關聯子查詢：關聯子查詢由外部的主查詢驅動的，所以主查詢中的每個列值子查詢均會執行一次。嵌套子查詢執行順序： -子查詢執行一次並且返回一個值 -主查詢使用子查詢返回的值執行一次關聯子查詢執行順序： -主查詢候選列值 -子查詢使用主查詢候選的列值運行並執行 -子查詢值返回給主查詢 -重覆以上過程，直到沒有候選列值

　　　　SELECT <columu1>,<column2>,....
                    FROM <table_name> outer
                    WHERE <column1> operator
                                    (SELECT <column1>,<column2>
                                       FROM <table_name>
                                       WHERE expr1 = outer.expr2);

註意：關聯子查詢中可以使用ANY和ALL操作符

關聯子查詢效能驗證與可替換方式：
16:32:16 SQL> col last_name format a30
16:32:16 SQL> set pagesize 1200
16:32:16 SQL> select last_name,salary,department_id
16:32:16   2  from employees outer
16:32:16   3  where salary >(select avg(salary)
16:32:16   4            from employees
16:32:16   5            where department_id=outer.department_id)
16:32:16   6            ;
LAST_NAME                          SALARY DEPARTMENT_ID
------------------------------ ---------- -------------
Hartstein                           12990            20
Raphaely                            10990            30
Weiss                                7990            50
Fripp                                8190            50
Kaufling                             7890            50
Vollman                              6490            50
Mourgos                              5790            50
Ladwig                               3590            50
Rajs                                 3490            50
Sarchand                             4190            50
Bull                                 4090            50
Chung                                3790            50
Dilly                                3590            50
Bell                                 3990            50
Everett                              3890            50

語句改寫：

col last_name format a30
set pagesize 1200
select last_name,salary,department_id
from employees outer
where salary >(select avg(salary)
        from employees
        where department_id=outer.department_id) 
minus        
select outer.last_name,outer.salary,outer.department_id
from employees outer,(select department_id,avg(salary) avg_salary from employees group by department_id) inner
where outer.department_id=inner.department_id
and outer.salary>inner.avg_salary;
 
對比兩種寫法：
 
16:45:11 SQL> set autotrace traceonly
16:45:21 SQL> alter system flush shared_pool;
已更改系統.
16:45:22 SQL> select last_name,salary,department_id
16:45:22   2  from employees outer
16:45:22   3  where salary >(select avg(salary)
16:45:22   4            from employees
16:45:22   5            where department_id=outer.department_id); --關聯子查詢
已選取 38 個資料列.
執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)
 
   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW OF 'VW_SQ_1' (VIEW) (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)
 
   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)
 
統計值
----------------------------------------------------------
        351  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         50  sorts (memory)
          0  sorts (disk)
         38  rows processed
 
16:45:23 SQL> alter system flush shared_pool;
已更改系統.
16:45:45 SQL> select outer.last_name,outer.salary,outer.department_id
16:45:45   2  from employees outer,(select department_id,avg(salary) avg_salary from employees grou
department_id) inner
16:45:45   3  where outer.department_id=inner.department_id
16:45:45   4  and outer.salary>inner.avg_salary; --join連接查詢
已選取 38 個資料列.
 
執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)
 
   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)
 
   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)
 
統計值
----------------------------------------------------------
        191  recursive calls
          0  db block gets
        257  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
         38  rows processed
 
16:45:46 SQL>
 
JOIN連接查詢比關聯子查詢性能上都有所提高。

EXISTS/NO EXISTS操作符 -EXISTS運算符經常與相關的子查詢一起使用，測試子查詢檢索的值的結果集中是否存在主查詢檢索的值。 -如果子查詢返回至少一行，則該運算符返回TRUE。如果該值不存在，則返回FALSE。 -NOT EXISTS測試主查詢檢索的值是否是子查詢檢索的值的結果集的一部分。

 SELECT <column1>,<colum2>,...
        FROM <table_name> outer
        WHERE [EXISTS | NOT EXISTS] (SELECT <column1>,<column2>,....
                                                            FROM <table_name>
                                                            WHERE <column1> = outer.<column1>);

關聯UPDATE

UPDATE <table_name> alias1
            SET <column> = (SELECT expression
                                        FROM <table_name> alias2 
                                        WHERE alias1.column = alias2.column);

註意：關聯UPDATE如果主查詢選定值在子查詢中未找到，SET欄位值將被更新成NULL值。

　　　　 create table ORA_01407_T0(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T0 values(1,'T01');
        insert into ORA_01407_T0 values(2,'T02');
        insert into ORA_01407_T0 values(3,'T03');
        commit;
 
        create table ORA_01407_T1(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T1 values(3,'T1_T03');
        insert into ORA_01407_T1 values(4,'T1_T04'); 
        commit;
 
        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
        
        16:47:15 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id)
                            *
        ERROR 在行 1:
        ORA-01407: 無法將 ("HR"."ORA_01407_T0"."NAME") 更新為 NULL
 
        將NAME NOT NULL約束去掉
        17:12:55 SQL> ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NULL);
        已更改表格.
        17:12:58 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a
        已更新 3 個資料列.
        17:13:05 SQL> select * from ORA_01407_T0;
            ID NAME
        ---------- --------------------
             1 ->可以看到關聯查詢中不匹配的記錄name欄被賦值為NULL。
             2  
             3 T1_T03

解決方案：
  使用MERGE函數進行處理，匹配記錄UPDATE
   truncate table ORA_01407_T0;
    ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NOT NULL);
    insert into ORA_01407_T0 values(1,'T01');
    insert into ORA_01407_T0 values(2,'T02');
    insert into ORA_01407_T0 values(3,'T03');
    commit;
 
    merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    when matched then update
    set a.name=b.name
      ;
 
    17:20:34 SQL> merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    17:21:48   2  when matched then update
    17:21:48   3  set a.name=b.name
    17:21:48   4  ;
    合併 1 個資料列.
    17:21:49 SQL> select * from ORA_01407_T0;
        ID NAME
    ---------- --------------------
         1 T01
         2 T02
         3 T1_T03

　關聯DELETE

 10:51:14 SQL> select * from ora_01407_t0
        10:51:41   2  ;
             ID NAME
        ---------- --------------------
         1 T01
         2 T02
         3 T1_T03
10:51:42 SQL> select * from ora_01407_t1;
        ID NAME
---------- --------------------
         3 T1_T03
         4 T1_T04
10:51:46 SQL> delete
10:53:05   2
10:53:07 SQL>
10:53:07 SQL>
10:53:07 SQL> delete from ora_01407_t0 t1
10:53:10   2  where id=(select id from ora_01407_t1 t2
10:53:10   3      where t2.id=t1.id);
已刪除 1 個資料列.
10:53:11 SQL> select * from ora_01407_t0;
        ID NAME
---------- --------------------
         1 T01
         2 T02

WITH子句 -使用WITH子句可以將多個相同的SELECT語句塊組成一個複雜的查詢 -WITH子句中查詢塊返回值會保存在用戶預設的臨時表空間 -使用WITH子句可提高性能 WITH子句好處 -查詢語句塊便於閱讀 -減少解析，查詢塊多次出現只會評估一次 -多數情況下，可提高查詢性能　　例子：查詢部門薪水大於部門平均薪水的部門

col department_name format a20
with
  dept_costs as (
    select d.department_name,sum(salary) as dept_total
    from employees e join departments d
    on 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;
 
    DEPARTMENT_NAME      DEPT_TOTAL
    -------------------- ----------
    Sales                    304160
    Shipping                 155950

學習總結： 1.多行/多列子查詢(成對/非成對比較) 2.關聯子查詢(主查詢->子查詢->返回值->主查詢)和標量子查詢(返回一個列值)、關聯UPDATE/DELETE/SELECT 3.EXISTS和NOT EXISTS操作符 4.使用WITH子句