分類  >  資料庫 >

ORACLE學習筆記系列(六)Outer Join新舊語法對比分析

tags:    時間:2013-12-23 21:49:15
ORACLE學習筆記系列(6)Outer Join新舊語法對比分析

      從Oracle9i開始,支持ANSI/ISO SQL92標準,對OUTER JOINLEFT/RIGHT/FULL OUTER JOIN。而在9i之前,Oralce使用的是SQL86標準,Oracle使用了OUTRT JOIN 操作符『+』來實現外連接,但是這個『+』號的語法只支持LEFT/RIGHT OUTER JOIN,不支持FULL OUTER JOIN,要實現FULL OUTER JOIN可以通過UNION ALL實現。

 

--為了能夠通過實際數據進行驗證,也為後面的測試使用,在這裡創建abc 三張表。

SQL> DROP TABLE a;

表已刪除。

SQL> DROP TABLE b;

表已刪除。

SQL> DROP TABLE c;

表已刪除。

SQL> CREATE TABLE a AS

  2     SELECT LEVEL ID, 'x' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 4;

表已創建。

SQL> CREATE TABLE b AS

  2     SELECT LEVEL ID, 'x' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 2;

表已創建。

SQL> CREATE TABLE c AS

  2      SELECT LEVEL ID, 'y' || LEVEL NAME FROM DUAL CONNECT BY LEVEL <= 2;

表已創建。

SQL> UPDATE A SET NAME = NULL WHERE ID = 3;

已更新1行。

SQL> UPDATE B SET NAME = NULL WHERE ID = 2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> SELECT * FROM A;

        ID NAME

---------- -----------------------------------------

         1 x1

         2 x2

         3

         4 x4

已選擇4行。

SQL> SELECT * FROM B;

        ID NAME

---------- -----------------------------------------

         1 x1

         2

SQL> SELECT * FROM C;

        ID NAME

---------- -----------------------------------------

         1 y1

         2 y2

SQL>

 

 

Oracle9i開始新舊語法並存,新語法的優點如下:

 

1、新語法可讀性更強

 

   新語法的結構是:

SELECT  *  FROM TABLE/VIEW LEFT/RIGHT/FULL [OUTER] JOIN TABLE/VIEW...

ON JOIN_CONDITION [WHERE]...

 

其中ON是連接條件,WHERE是連接后的過濾條件,有LEFT/RIGHT/FULL標識連接類型,很容易理解。但是老語法如果連接條件複雜,會有一堆+號,有的+號還在表達式里,

比如:WHERE A.NAME=SUBSTR(B.NAME(+),0,4) AND B.ID(+)>20, 很不容易理解。

 

2、新語法支持OR條件連接

 

  老語法:SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+);    會報錯「ORA=01719:ORIN操作數中不允許外部聯接運算符(+)」,因為老語法的連接條件不能用OR組合。

 

  新語法:SELECT * FROM A LEFT JOIN B ON A.ID=B.ID OR A.NAME=B.NAME;執行正常,因為新語法支持。

 

SQL> SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+);

SELECT * FROM A,B WHERE A.ID=B.ID(+) OR A.NAME=B.NAME(+)

                                              *

1 行出現錯誤:

ORA-01719: OR IN 操作數中不允許外部聯接運算符 (+)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID=B.ID OR A.NAME=B.NAME;

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- --------------------

         1 x1                                                 1 x1

         2 x2                                                 2

         3

         4 x4

已選擇4行。

SQL>

 

3、新語法支持FULL OUTER JOIN

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME(+) = B.NAME(+);

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME(+) = B.NAME(+)

                                                                                    *

1 行出現錯誤:

ORA-01468: 一個謂詞只能引用一個外部聯接的表

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+);

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- -----------------------------------

         1 x1                                                 1 x1

         3

         2 x2

         4 x4

已選擇4行。

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ------------------------------------

         1 x1                                                 1 x1

         2 x2

         3

         4 x4

                                                              2

已選擇5行。

SQL>

 

從上面的結果可以看出老語法是不允許使用全外連接的,而新語法可以查詢出9條行數據。

 

4、其他優點待整理

 

 

OUTER JOIN 概念:

 

  OUTER JOIN INNER JOIN 不同,它有基表和從表的概念。

基表也就是參考表,此表的內容會在OUTER JOIN 里全部選中,然後基表根據JOIN的條件到從表中選出從表記錄,如果滿足條件則按從表實際內容選出,否則沒有找到則從表的對應行的所有列值全為NULL。當然OUTER JOIN可用於表也可以用於視圖。

 

外連接(OUTER JOIN)分為三種:

            左外連接(LEFT OUTER JOINLEFT JOIN):左邊的表是基表,右邊的表是從表;

            右外連接(RIGHT OUTER JOINRIGHT JOIN):右邊的表是基表,左邊的表是從表;

            全外連接(FULL OUTER JOINFULL JOIN):左邊的表和右邊的表互為基表和從表;

 

全外連接(FULL OUTER JOIN)比較特殊,可以簡單理解為先以A為基表,B為從表選出結果,然後以B為基表,A為從表選出結果,最後去除重複在兩個步驟中都出現的結果。

從老語法上看,只支持左外連接和右外連接,在WHERE條件里無+號的那邊表達式中的列所屬表為基表,有+號的表達式中的列所屬表為從表。

比如:WHERE A.ID=B.ID(+) 這時A表是基表,B表是從表。

 

下面通過實例來對比分析OUTER JOIN的新舊語法:

依據上面所建的表及數據,分析下面10組語句

 

1組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+);

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+);

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ---------- -----------------

         1 x1                                                 1 x1

         2 x2                                                 2

         4 x4

         3

執行計劃

----------------------------------------------------------

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"(+))

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id ;

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ---------- -----------

         1 x1                                                 1 x1

         2 x2                                                 2

         4 x4

         3

執行計劃

----------------------------------------------------------

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"(+))

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL>

 

說明:從查詢結果和執行計劃上看,第1組新舊語句是一樣的。

a.連接方式為左外連接:access("A"."ID"="B"."ID"(+))

b.執行計劃顯示為HASH JOIN OUTER

c.查詢結果為4行;

d.A表為基表,因此A表一側內容全部被選中,但是B表只完全匹配(1,'x1')和(2,null,對於A表的(3,null)(4,'x4')B表中沒有找到匹配記錄,因此這兩行的B表所有列都為NULL

 

2組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) AND A.NAME = B.NAME(+);

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id AND A.NAME = B.NAME;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) AND A.NAME = B.NAME(+);

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ---------- -------------------

         1 x1                                                 1 x1

         4 x4

         3

         2 x2

執行計劃

----------------------------------------------------------

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"(+) AND "A"."NAME"="B"."NAME"(+))

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.id = B.id AND A.NAME = B.NAME;

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ---------- --------------

         1 x1                                                 1 x1

         4 x4

         2 x2

         3

執行計劃

----------------------------------------------------------

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"."NAME"="B"."NAME"(+) AND "A"."ID"="B"."ID"(+))

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL>

 

說明:從查詢結果和執行計劃上看,第2組新舊語句是一樣的。

a.語句1的連接方式為左外連接:access("A"."ID"="B"."ID"(+) AND "A"."NAME"="B"."NAME"(+))

b.執行計劃顯示為HASH JOIN OUTER

c.查詢結果為4行;

d.A表為基表,因此A表一側內容全部被選中,但是B表只完全匹配(1,'x1',對於A表的(2,'x2'),(3,null)(4,'x4')B表中沒有找到匹配記錄,因此這3行的B表所有列都為NULL

 

3組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+);

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = B.ID OR A.NAME = B.NAME;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+);

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.id = B.id(+) OR A.NAME = B.NAME(+)

                                                                                                   *

1 行出現錯誤:

ORA-01719: OR IN 操作數中不允許外部聯接運算符 (+)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = B.ID OR A.NAME = B.NAME;

      A_ID A_NAME                                          B_ID B_NAME

---------- ----------------------------------------- ---------- -----------

         1 x1                                                 1 x1

         2 x2                                                 2

         3

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 2608930719

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     4 |   280 |    15   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER |      |     4 |   280 |    15   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | A    |     4 |   140 |     3   (0)| 00:00:01 |

|   3 |   VIEW              |      |     1 |    35 |     3   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| B    |     1 |    35 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter("A"."ID"="B"."ID" OR "A"."NAME"="B"."NAME")

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL>

 

說明:可看出老語法不支持OR條件連接。

a.語句2的連接方式為嵌套循環filter("A"."ID"="B"."ID" OR "A"."NAME"="B"."NAME")

b.執行計劃顯示為NESTED LOOPS OUTER

c.查詢結果為4行;

d.A表是基表,因此A表一側內容全部被選中,但是B表只完全匹配(1,'x1'(2,null),對於A表的 (3,null)(4,'x4')B表中沒有找到匹配記錄,因此這3行的B表所有列都為NULL

 

4組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.ID = '2';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.ID = '2';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.ID = '2' ;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = '2' AND A.NAME = B.NAME ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.ID = '2';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         2 x2

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    70 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     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"."NAME"="B"."NAME"(+))

   2 - filter("A"."ID"=2)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.ID = '2';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         2 x2

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    70 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     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"."NAME"="B"."NAME"(+))

   2 - filter("A"."ID"=2)

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.ID = '2' ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         4 x4

         2 x2

         3

         1 x1

執行計劃

----------------------------------------------------------

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"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE

              WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))

Note

-----

   - dynamic sampling used for this statement (level=2)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.ID = '2' AND A.NAME = B.NAME ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         4 x4

         2 x2

         3

         1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE

              WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))

   3 - filter("B"."NAME"(+) IS NOT NULL)

SQL>

 

說明:

1條語句和第2條語句結果是一樣的,屬於先篩選后連接

(先 filter("A"."ID"=2) access("A"."NAME"="B"."NAME"(+))

3條語句和第4條語句結果是一樣的,

3條語句但是卻只有連接條件沒有篩選條件,(由 Table A 驅動 HASH JOIN OUTER 每一次循環按條件 access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE               WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END )) 與表B連接。

4條語句,屬於先篩選后連接

(先 filter("B"."NAME"(+) IS NOT NULL) 這個條件在查語句中並沒有,是執行計劃時添加上的,后 access("A"."NAME"="B"."NAME"(+) AND "A"."ID"=TO_NUMBER(CASE               WHEN "B"."NAME"(+) IS NOT NULL THEN '2' ELSE '2' END ))

 

5組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.NAME = 'x1';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND A.NAME = 'x1';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| A    |     1 |     6 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   2 - filter("A"."NAME"='x1')

   3 - filter("B"."NAME"(+)='x1')

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| A    |     1 |     6 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   2 - filter("A"."NAME"='x1')

   3 - filter("B"."NAME"(+)='x1')

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

         3

         4 x4

         2 x2

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+) AND "A"."NAME"=CASE  WHEN

              ("B"."NAME"(+) IS NOT NULL) THEN 'x1' ELSE 'x1' END )

   3 - filter("B"."NAME"(+) IS NOT NULL)

SQL>

 

說明:5組語句與第4組語句是類似的。

 

6組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2';

未選定行

執行計劃

----------------------------------------------------------

Plan hash value: 4090908061

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A    |     3 |    18 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("B"."NAME" IS NOT NULL AND "B"."ID"=2)

   3 - filter("A"."NAME" IS NOT NULL)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2';

未選定行

執行計劃

----------------------------------------------------------

Plan hash value: 4090908061

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A    |     3 |    18 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("B"."NAME" IS NOT NULL AND "B"."ID"=2)

   3 - filter("A"."NAME" IS NOT NULL)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         3

         1 x1

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   3 - filter("B"."NAME"(+) IS NOT NULL AND "B"."ID"(+)=2)

SQL>

 

說明:

1條語句和第2條語句查詢結果是一樣的,屬於先篩選后連接,但連接方式已經就成了HASH JOIN 並且兩個表連接的列都不能為NULL,因此沒有查詢出匹配的結果數據。

3條語句連接方式依然是 HASH JOIN OUTER

 

7組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME = 'x1';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME = 'x1';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME = 'x1' ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME = 'x1';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 652036164

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| A    |     1 |     6 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("A"."NAME"='x1')

   3 - filter("B"."NAME"='x1')

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME = 'x1';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 652036164

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| A    |     1 |     6 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("A"."NAME"='x1')

   3 - filter("B"."NAME"='x1')

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME = 'x1' ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

         3

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   3 - filter("B"."NAME"(+)='x1')

SQL>

 

說明:第7組語句與第6組語句是類似的。

通過第6組語句與第7組語句,可以看出當從表B中存在限制特定條件的列時,兩個表的連接方式就不再是外連接

 

 

8組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2' AND B.NAME IS NULL;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2' AND B.NAME IS NULL;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' AND B.NAME IS NULL;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.ID = '2' AND B.N

AME IS NULL;

未選定行

執行計劃

----------------------------------------------------------

Plan hash value: 4090908061

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A    |     3 |    18 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("B"."NAME" IS NULL AND "B"."ID"=2)

   3 - filter("A"."NAME" IS NOT NULL)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.ID = '2' AN

D B.NAME IS NULL;

未選定行

執行計劃

----------------------------------------------------------

Plan hash value: 4090908061

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    11 |     7  (15)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A    |     3 |    18 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME")

   2 - filter("B"."NAME" IS NULL AND "B"."ID"=2)

   3 - filter("A"."NAME" IS NOT NULL)

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.ID = '2' AND

B.NAME IS NULL;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         3

         1 x1

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   3 - filter("B"."NAME"(+) IS NULL AND "B"."ID"(+)=2)

SQL>

 

說明:

1條語句和第2條語句結果是一樣的,屬於先篩選后連接,連接方式就為HASH JOIN因為篩選條件中從表B的連接列條件為NULL filter("B"."NAME" IS NULL AND "B"."ID"=2)

所以基表A的連接列不能為NULL  filter("A"."NAME" IS NOT NULL)

3條語句屬於先篩選后連接,連接方式就為HASH JOIN OUTER,因此能查詢出基表A4條信息。

 

9組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME IS NULL;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME IS NULL;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME IS NULL;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A, B WHERE A.NAME = B.NAME(+) AND B.NAME IS NULL;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         3

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 2020188187

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     2 |    22 |     7  (15)| 00:00:01 |

|*  1 |  FILTER             |      |       |       |            |          |

|*  2 |   HASH JOIN OUTER   |      |     2 |    22 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| B    |     2 |    10 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"."NAME" IS NULL)

   2 - access("A"."NAME"="B"."NAME"(+))

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME WHERE B.NAME IS NUL

L;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         3

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 2020188187

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     2 |    22 |     7  (15)| 00:00:01 |

|*  1 |  FILTER             |      |       |       |            |          |

|*  2 |   HASH JOIN OUTER   |      |     2 |    22 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| B    |     2 |    10 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("B"."NAME" IS NULL)

   2 - access("A"."NAME"="B"."NAME"(+))

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A LEFT JOIN B ON A.NAME = B.NAME AND B.NAME IS NULL;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         3

         1 x1

         2 x2

         4 x4

執行計劃

----------------------------------------------------------

Plan hash value: 1365417139

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     4 |    44 |     7  (15)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     4 |    44 |     7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| A    |     4 |    24 |     3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| B    |     1 |     5 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."NAME"="B"."NAME"(+))

   3 - filter("B"."NAME"(+) IS NULL)

SQL>

 

說明:

1條語句和第2條語句結果是一樣的,屬於先連接后篩選,連接方式就為HASH JOIN OUTER(先access("A"."NAME"="B"."NAME"(+)) filter("B"."NAME" IS NULL)

3條語句,屬於先篩選后連接,連接方式就為HASH JOIN OUTER

(先filter("B"."NAME"(+) IS NULL) access("A"."NAME"="B"."NAME"(+))

 

 

10組語句:

 

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';

SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;

 

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

         2 x2

         3

         4 x4

                                         2

執行計劃

----------------------------------------------------------

Plan hash value: 4118491158

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     4 |   280 |     7  (15)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 |     4 |   280 |     7  (15)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     4 |    44 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | B        |     2 |    10 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | A        |     4 |    24 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."NAME"="B"."NAME")

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME WHERE A.NAME = 'x1';

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

執行計劃

----------------------------------------------------------

Plan hash value: 4118491158

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     4 |   280 |     7  (15)| 00:00:01 |

|*  1 |  VIEW                 | VW_FOJ_0 |     4 |   280 |     7  (15)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     4 |    44 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | B        |     2 |    10 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | A        |     4 |    24 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("A"."NAME"='x1')

   2 - access("A"."NAME"="B"."NAME")

SQL> SELECT A.ID A_ID, A.NAME A_NAME, B.ID B_ID, B.NAME B_NAME FROM A FULL JOIN B ON A.NAME = B.NAME AND A.NAME = 'x1' ;

      A_ID A_NAME                     B_ID B_NAME

---------- -------------------- ---------- --------------------

         1 x1                            1 x1

         2 x2

         3

         4 x4

                                         2

執行計劃

----------------------------------------------------------

Plan hash value: 4118491158

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     4 |   280 |     7  (15)| 00:00:01 |

|   1 |  VIEW                 | VW_FOJ_0 |     4 |   280 |     7  (15)| 00:00:01 |

|*  2 |   HASH JOIN FULL OUTER|          |     4 |    44 |     7  (15)| 00:00:01 |

|   3 |    TABLE ACCESS FULL  | B        |     2 |    10 |     3   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL  | A        |     4 |    24 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."NAME"="B"."NAME")

       filter("A"."NAME"='x1')

SQL>

 

說明:

10組語句都是新語法語句,連接方式都是全外連接 HASH JOIN FULL OUTER

 

小結:

右外連接(RIGHT OUTER JOINRIGHT JOIN)與左外連接(LEFT OUTER JOINLEFT JOIN) 是類似的,在此不再具體分析。

對於OUTER JOIN 新舊語法大部分都是相同的,但是新語法對條件的限制更加靈活,可讀性也更強。不管是新語法還是舊語法,連接的列或者限制條件的列不一樣,都有可能導致整個查詢的連接方式和查詢結果的改變,所以在實際應用中都需要認真謹慎核對。

推薦閱讀文章

Bookmark the permalink ,來源:互聯網