表連接之連接的類型

    顧明思義，表連接就是指多個表之間用連接條件連接在一起，使用連接的目標sql的目的就是從多個表獲取存儲在這些表中的不同維度的數據。體現在sql語句上，含表連接的目標sql的from部分會出現多個表，而這些sql的where條件部分則會定義具體的表連接條件。

    當優化器解析含表連接的目標sql時，它除了會根據目標sql的sql文本的寫法來決定表連接的類型之外，還必須決定如下三件事情才能得到最終的執行計劃。

    （1）表連接順序

    不管目標sql中有多少個表做連接，oracle在實際執行該sql時都是只能先兩兩表連接，再依次執行這樣的兩兩表連接過程，直到目標sql中所有表都已連接完畢。所以從嚴格意義上來說，這里的表連接順序包含兩層含義：一層含義當兩個表做表連接時，優化器需要決定兩個表中誰是驅動表，誰是被驅動表；另外一層含義是當多表（超過兩個以上的表）做表連接時，優化器需要決定這些中誰和誰先做表連接，然后決定這個表連接結果所在的結果集合剩余的表的哪一個再做表連接，這個兩兩做表連接的過程會一直持續下去去，直到目標sql中所有的表都已經連接完為止。

    （2）表連接的方法

    在oracle數據庫中，兩表之間的表連接方法有合并聯結，嵌套循環聯結，哈希連接和笛卡兒聯結這四種，所以優化器在解析含表連接的目標sql時，都需要從上述四種方法中選擇一種，作為每一對表兩兩做表連接時所以需要采用的方法。

    （3）訪問單表的方法

    對于優化器而言，僅決定表連接順序和連接方法是不夠的，這還不足以得到目標sql的最終執行計劃，因為優化器對目標sql中個各個表兩兩做表連接時，還必須決定如何去獲取存儲在這些表里的不同維度的數據，即優化器還要決定訪問單表的方法。比如訪問某個單表時，是采用全表掃描還是走索引，如果是索引，應該采用什么樣的索引訪問方法等。

    表連接的類型

    通常情況下，我們可以認為oralce數據庫中的表連接分為內連接和外連接兩種類型，表連接的類型會直接決定表連接的結果，而目標sql的sql文本的寫法又直接決定了表連接的類型。

    （1）內連接

    內連接是指表連接的連接結果只包含那些完全滿足條件的記錄嗎。對于包含表連接的目標sql而言，只要其where條件中沒有寫那些標準sql中定義或oracle中自定義的表示外連接的關鍵字（比如標準sql中的left outer join，right outer join,full outer join,或者oracle中自定義用來表示外連接的關鍵（+），則該sql的連接類型就是內連接

SQL> create table t1(col1 number,col2 varchar2(1));

Table created.

SQL> SQL> create table t2(col2 varchar2(1),col3 varchar2(2));

Table created.

SQL> insert into t1 values(1,'A');

1 row created.

SQL> insert into t1 values(2,'B');

1 row created.

SQL> insert into t1 values(3,'C');

1 row created.

SQL> insert into t2 values('A','A2');

1 row created.

SQL> insert into t2 values('B','B2');

1 row created.

SQL> insert into t2 values('D','D2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

      COL1 C

---------- -

1 A

2 B

3 C

SQL> select * from t2;

C CO

- --

A A2

B B2

D D2

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

      COL1 C CO

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

1 A A2

2 B B2

    從以上執行結果中我們可以看出，內連接的連接結果只包含了那些玩去滿足條件的記錄。

    標準sql的寫法如下：   

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 using(col2);

select t1.col1,t1.col2,t2.col3 from t1 join t2 using(col2)

               *

ERROR at line 1:

ORA-25154: column part of USING clause cannot have qualifier

    這里需要注意的是，對于使用join using的標準sql而言，如果連接連接列同時又出現在查詢列中，則該連接列前不能帶上表名或表名的別名，否則oralce會拋出ORA-25154，以下是正確寫法：

SQL> select t1.col1,col2,t2.col3 from t1 join t2 using(col2);

      COL1 C CO

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

1 A A2

2 B B2

    使用標準sql來表示表連接，那么有一種特殊的jion using，我們稱之為NATURAL JOIN，NATURAL JOIN是一種特殊的JOIN USING，其含義是使用NATURAL JOIN的表連接的連接列是表連接的兩個表所有的同名列。

SQL> select t1.col1,col2,t2.col3 from t1 natural join t2;

      COL1 C CO

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

1 A A2

2 B B2

    使用NATURAL JOIN的好處是無須在JOIN USING中寫連接集合，但其壞處是增加了表連接的執行結果出錯的風險，因為兩個表之間的同名列不一定在含義上完全相同（也許它們只是恰好同名而已，其含義是完全不同的，所以不應該將它們作為連接列）而且即使含義相同，也不一定就需要他們做連接

    （2）外連接

    外連接是對內連接的一種擴展，它是指表連接的連接結果除了包含那些完全滿足連接條件的記錄之外還會包含驅動表中所有不滿足該條件的連接的記錄。

    標準sql的外連接分為左連接，右連接和全連接這三種，它們在標準sql中所對應的關鍵字分別是left outer join,right outer join和full outer join，都可以和join on或者join using連用。

    左連接語法：

    目標表1 left outer join 目標表2 on（連接條件）或

    目標表1 left outer join 目標表2 using（連接集合）

    “目標表1 left outer join 目標表2 on（連接條件）”的含義為目標表1和目標表2按括號中連接條件來做表連接，位于關鍵left outer join左邊的目標表1會作為該表連接的驅動表（關鍵字“left outer”即表明位置處于left就是outer table，outer table指驅動表）。此時的連接除了包含目標表1和目標表2中所有滿足該條件的記錄外，還會包含驅動表（目標表1）中索引不滿足該連接條件的記錄，同時，驅動表中所有不滿足該連接條件的記錄所對應的被驅動表（即目標表2）中查詢列均以NULL值來填充。

    右連接語法：

    目標表1 right outer join 目標表2 on（連接條件）或

    目標表1 right outer join 目標表2 using（連接集合）

    全連接語法：

    目標表1 full outer join 目標表2 on（連接條件）或

    目標表1 full outer join 目標表2 using（連接集合）

    可以把全連接理解成先做左連接，再右連接，最后對左右連接的連接結果做一個union操作。

    左連接實例：

SQL> select t1.col1,t1.col2,t2.col3 from t1 left outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

3 C

    oracle自定義寫法：

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2(+);

      COL1 C CO

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

1 A A2

2 B B2

3 C

關鍵字（+）出現在表T2的連接列col2后面，這就表示T2會以NULL值來填充那些不滿足連接條件的t1.col2=t2.col2 并位于T2中的查詢列（col3）。

    右連接實例：

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

    D2

    oracle自定義寫法：

SQL> select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2(+)=t2.col2;

      COL1 C CO

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

1 A A2

2 B B2

    D2

    全連接實例：  

SQL> select t1.col1,t1.col2,t2.col3 from t1 full outer join t2 on(t1.col2=t2.col2);

      COL1 C CO

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

1 A A2

2 B B2

    D2

3 C

    上述實例sql中除了帶了連接條件外，并沒有帶其他的額外連接條件，如果目標sql中除了表連接條件之外還帶了其他額外的限制條件，則目標sql中表連接的類型和該額外條件在目標sql的sql文本中出現位置都可能會對最終執行計劃產生影響。    

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2 and t1.col1=1);

      COL1 C CO

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

1 A A2

SQL> select t1.col1,t1.col2,t2.col3 from t1 join t2 on(t1.col2=t2.col2) where t1.col1=1;

      COL1 C CO

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

1 A A2

    上述結果說明對于內連接而言，除了表連接條件之外的額外限制條件在目標sql的sql文本中所處的位置并不會影響該sql的實際執行結果。

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2 and t1.col1=1);

      COL1 C CO

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

1 A A2

    D2

    B2

SQL> select t1.col1,t1.col2,t2.col3 from t1 right outer join t2 on(t1.col2=t2.col2) where t1.col1=1;

      COL1 C CO

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

1 A A2

    對于例1，它的限制條件在sql文本中位于right outer join所對應的括號內，這表示該限制條件會在表T1和T2做右連接之前就被應用在表T1上，參與右連接的T1的數據是那些滿足條件t1.col1=1的記錄， 而例2的限制條件位于right outer join括號外，這表示該限制條件在T1和T2做完右連接之后，才會被應用在表T1和T2的連接結果集上，參與右連接的是表T1中所有數據。