兩種連線的表達 ：left(right) join 和 (+)

稍微研究了一下 oracle 自己的join 和標準的join。主要表現在on ，where 關鍵字所起的作用不同，和連線本身的特性。
yang@ORACL> set autotrace on
yang@ORACL> select  *
  2  from a,b
  3  where a.id=b.id(+) and a.name like 'x%';

        ID NAME          ID NAME
---------- ----- ---------- -----
         1 x1             1 x1
         2 x2             2 x2
         4 x4
         3 x3
執行計劃
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     4 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID"(+))
   2 - filter("A"."NAME" LIKE 'x%')
由執行計劃可以看出先對a表進行過濾，讓後進行與b表的left join，
--
yang@ORACL> select * from a left join b
  2  on a.id=b.id and a.name like 'x%';

        ID NAME          ID NAME
---------- ----- ---------- -----
         1 x1             1 x1
         2 x2             2 x2
         3 x3
         4 x4
         1 y1
         2 y2
         3 y3
         4 y4

已選擇8行。

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     8 |   560 |    27   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER  |      |     8 |   560 |    27   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | A    |     8 |   280 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     1 |    35 |     3   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| B    |     1 |    35 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("A"."NAME" LIKE 'x%')
   5 - filter("A"."ID"="B"."ID")
-- 先連線後篩選 （由 Table A 驅動 NESTED LOOPS OUTER ，每一次迴圈篩選記錄
外連線中的on不需要過濾基表資料，過濾基表資料是在where裡做的，on只是連線條件，根據連線條件找匹配的從表資料，找不到匹配的從錶行，則置空。
yang@ORACL> select * from a left join b
  2  on a.id=b.id
  3  where a.name like 'x%';

        ID NAME          ID NAME
---------- ----- ---------- -----
         1 x1             1 x1
         2 x2             2 x2
         4 x4
         3 x3
執行計劃
----------------------------------------------------------
Plan hash value: 1365417139

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   280 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     4 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID"(+))
   2 - filter("A"."NAME" LIKE 'x%')

Note
-----
   - dynamic sampling used for this statement

yang@ORACL> select * from a left join b
  2  on a.name like 'x%'
  3  where a.id=b.id;
        ID NAME          ID NAME
---------- ----- ---------- -----
         1 x1             1 x1
         2 x2             2 x2

執行計劃
----------------------------------------------------------
Plan hash value: 652036164
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    70 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    70 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| A    |     1 |    35 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    70 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ID"="B"."ID")
   2 - filter("A"."NAME" LIKE 'x%')
需要說明的是：
 on關鍵字，的確是連線條件，它不能過濾基表。過濾不了的原因是on過濾掉的emp表結果，最後又被left outer join拿了回來。
 select e.ename,d.dname from emp e left outer join dept d on d.deptno=e.deptno and e.ename='SCOTT';
  虛擬碼
    for rec_e in ( select ename from emp e ) loop
        for rec_d in ( select dname from dept d where d.deptno = rec_e.deptno ) loop
             if rec_d != NULL and rec_e..ename='SCOTT' then
                show (rec_e.ename,rec_d.name);
             else
                show (rec_e.ename,null);   --on過濾掉的emp表結果，最後又被left outer join拿了回來。
             end if;
        end loop;
     end loop;
--虛擬碼部分內容參考了puber ccsnmoracle 的表述