表連接之連接的類型

    顧明思義，表連接就是指多個(gè)表之間用連接條件連接在一起，使用連接的目標(biāo)sql的目的就是從多個(gè)表獲取存儲(chǔ)在這些表中的不同維度的數(shù)據(jù)。體現(xiàn)在sql語句上，含表連接的目標(biāo)sql的from部分會(huì)出現(xiàn)多個(gè)表，而這些sql的where條件部分則會(huì)定義具體的表連接條件。

    當(dāng)優(yōu)化器解析含表連接的目標(biāo)sql時(shí)，它除了會(huì)根據(jù)目標(biāo)sql的sql文本的寫法來決定表連接的類型之外，還必須決定如下三件事情才能得到最終的執(zhí)行計(jì)劃。

    （1）表連接順序

    不管目標(biāo)sql中有多少個(gè)表做連接，oracle在實(shí)際執(zhí)行該sql時(shí)都是只能先兩兩表連接，再依次執(zhí)行這樣的兩兩表連接過程，直到目標(biāo)sql中所有表都已連接完畢。所以從嚴(yán)格意義上來說，這里的表連接順序包含兩層含義：一層含義當(dāng)兩個(gè)表做表連接時(shí)，優(yōu)化器需要決定兩個(gè)表中誰是驅(qū)動(dòng)表，誰是被驅(qū)動(dòng)表；另外一層含義是當(dāng)多表（超過兩個(gè)以上的表）做表連接時(shí)，優(yōu)化器需要決定這些中誰和誰先做表連接，然后決定這個(gè)表連接結(jié)果所在的結(jié)果集合剩余的表的哪一個(gè)再做表連接，這個(gè)兩兩做表連接的過程會(huì)一直持續(xù)下去去，直到目標(biāo)sql中所有的表都已經(jīng)連接完為止。

    （2）表連接的方法

    在oracle數(shù)據(jù)庫中，兩表之間的表連接方法有合并聯(lián)結(jié)，嵌套循環(huán)聯(lián)結(jié)，哈希連接和笛卡兒聯(lián)結(jié)這四種，所以優(yōu)化器在解析含表連接的目標(biāo)sql時(shí)，都需要從上述四種方法中選擇一種，作為每一對表兩兩做表連接時(shí)所以需要采用的方法。

    （3）訪問單表的方法

    對于優(yōu)化器而言，僅決定表連接順序和連接方法是不夠的，這還不足以得到目標(biāo)sql的最終執(zhí)行計(jì)劃，因?yàn)閮?yōu)化器對目標(biāo)sql中個(gè)各個(gè)表兩兩做表連接時(shí)，還必須決定如何去獲取存儲(chǔ)在這些表里的不同維度的數(shù)據(jù)，即優(yōu)化器還要決定訪問單表的方法。比如訪問某個(gè)單表時(shí)，是采用全表掃描還是走索引，如果是索引，應(yīng)該采用什么樣的索引訪問方法等。

    表連接的類型

    通常情況下，我們可以認(rèn)為oralce數(shù)據(jù)庫中的表連接分為內(nèi)連接和外連接兩種類型，表連接的類型會(huì)直接決定表連接的結(jié)果，而目標(biāo)sql的sql文本的寫法又直接決定了表連接的類型。

    （1）內(nèi)連接

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

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

    從以上執(zhí)行結(jié)果中我們可以看出，內(nèi)連接的連接結(jié)果只包含了那些玩去滿足條件的記錄。

    標(biāo)準(zhǔn)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的標(biāo)準(zhǔn)sql而言，如果連接連接列同時(shí)又出現(xiàn)在查詢列中，則該連接列前不能帶上表名或表名的別名，否則oralce會(huì)拋出ORA-25154，以下是正確寫法：

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

      COL1 C CO

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

1 A A2

2 B B2

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

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中寫連接集合，但其壞處是增加了表連接的執(zhí)行結(jié)果出錯(cuò)的風(fēng)險(xiǎn)，因?yàn)閮蓚€(gè)表之間的同名列不一定在含義上完全相同（也許它們只是恰好同名而已，其含義是完全不同的，所以不應(yīng)該將它們作為連接列）而且即使含義相同，也不一定就需要他們做連接

    （2）外連接

    外連接是對內(nèi)連接的一種擴(kuò)展，它是指表連接的連接結(jié)果除了包含那些完全滿足連接條件的記錄之外還會(huì)包含驅(qū)動(dòng)表中所有不滿足該條件的連接的記錄。

    標(biāo)準(zhǔn)sql的外連接分為左連接，右連接和全連接這三種，它們在標(biāo)準(zhǔn)sql中所對應(yīng)的關(guān)鍵字分別是left outer join,right outer join和full outer join，都可以和join on或者join using連用。

    左連接語法：

    目標(biāo)表1 left outer join 目標(biāo)表2 on（連接條件）或

    目標(biāo)表1 left outer join 目標(biāo)表2 using（連接集合）

    “目標(biāo)表1 left outer join 目標(biāo)表2 on（連接條件）”的含義為目標(biāo)表1和目標(biāo)表2按括號(hào)中連接條件來做表連接，位于關(guān)鍵left outer join左邊的目標(biāo)表1會(huì)作為該表連接的驅(qū)動(dòng)表（關(guān)鍵字“l(fā)eft outer”即表明位置處于left就是outer table，outer table指驅(qū)動(dòng)表）。此時(shí)的連接除了包含目標(biāo)表1和目標(biāo)表2中所有滿足該條件的記錄外，還會(huì)包含驅(qū)動(dòng)表（目標(biāo)表1）中索引不滿足該連接條件的記錄，同時(shí)，驅(qū)動(dòng)表中所有不滿足該連接條件的記錄所對應(yīng)的被驅(qū)動(dòng)表（即目標(biāo)表2）中查詢列均以NULL值來填充。

    右連接語法：

    目標(biāo)表1 right outer join 目標(biāo)表2 on（連接條件）或

    目標(biāo)表1 right outer join 目標(biāo)表2 using（連接集合）

    全連接語法：

    目標(biāo)表1 full outer join 目標(biāo)表2 on（連接條件）或

    目標(biāo)表1 full outer join 目標(biāo)表2 using（連接集合）

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

    左連接實(shí)例：

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

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

    右連接實(shí)例：

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

    全連接實(shí)例：  

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

    上述實(shí)例sql中除了帶了連接條件外，并沒有帶其他的額外連接條件，如果目標(biāo)sql中除了表連接條件之外還帶了其他額外的限制條件，則目標(biāo)sql中表連接的類型和該額外條件在目標(biāo)sql的sql文本中出現(xiàn)位置都可能會(huì)對最終執(zhí)行計(jì)劃產(chǎn)生影響。    

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

    上述結(jié)果說明對于內(nèi)連接而言，除了表連接條件之外的額外限制條件在目標(biāo)sql的sql文本中所處的位置并不會(huì)影響該sql的實(shí)際執(zhí)行結(jié)果。

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所對應(yīng)的括號(hào)內(nèi)，這表示該限制條件會(huì)在表T1和T2做右連接之前就被應(yīng)用在表T1上，參與右連接的T1的數(shù)據(jù)是那些滿足條件t1.col1=1的記錄， 而例2的限制條件位于right outer join括號(hào)外，這表示該限制條件在T1和T2做完右連接之后，才會(huì)被應(yīng)用在表T1和T2的連接結(jié)果集上，參與右連接的是表T1中所有數(shù)據(jù)。