Oracle-表連接配接類型和表連接配接方式

表連接配接類型：

Oracle - 表連接配接的幾種類型：

• 内連接配接(自然連接配接)
• 外連接配接
  • 左外連接配接 (左邊的表不加限制)
  • 右外連接配接(右邊的表不加限制)
  • 全外連接配接(左右兩表都不加限制)
• 自連接配接（同一張表内的連接配接）

SQL的标準文法：

select table1.column,table2.column
from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;

inner join 表示内連接配接；
left join表示左外連接配接；
right join表示右外連接配接；
full join表示完全外連接配接；
on子句 用于指定連接配接條件。

注意：
如果使用from子句指定内、外連接配接，則必須要使用on子句指定連接配接條件；
如果使用（+）操作符指定外連接配接，則必須使用where子句指定連接配接條件。
           

示意圖如下：

表連接配接方式：指執行計劃裡面的表連接配接方式

Oracle - 表連接配接的幾種方式：

• SORT MERGE JOIN（排序-合并連接配接）
• NESTED LOOPS（嵌套循環）
• HASH JOIN（哈希連接配接）
• CARTESIAN PRODUCT（笛卡爾積）

連接配接說明：

1. Oracle一次隻能連接配接兩個表。不管查詢中有多少個表，Oracle 在連接配接中一次僅能操作兩張表。
2. 當執行多個表的連接配接時，優化器從一個表開始，将它與另一個表連接配接；然後将中間結果與下一個表連接配接，以此類推，直到處理完所有表為止。

舉例：

注：這裡将首先存取的表稱作 row source 1，将之後參與連接配接的表稱作 row source 2；

（1） SORT MERGE JOIN（排序-合并連接配接）：

假設有查詢：select a.name, b.name from table_A a join table_B b on (a.id = b.id)

内部連接配接過程：

a) 生成 row source 1 需要的資料，按照連接配接操作關聯列（如示例中的a.id）對這些資料進行排序

b) 生成 row source 2 需要的資料，按照與 a) 中對應的連接配接操作關聯列（b.id）對資料進行排序

c) 兩邊已排序的行放在一起執行合并操作（對兩邊的資料集進行掃描并判斷是否連接配接）

延伸：

如果示例中的連接配接操作關聯列 a.id，b.id 之前就已經被排過序了的話，連接配接速度便可大大提高，因為排序是很費時間和資源的操作，尤其對于有大量資料的表。

故可以考慮在 a.id，b.id 上建立索引讓其能預先排好序。不過遺憾的是，由于傳回的結果集中包括所有字段，是以通常的執行計劃中，即使連接配接列存在索引，也不會進入到執行計劃中，除非進行一些特定列處理（如僅僅隻查詢有索引的列等）。

排序-合并連接配接的表無驅動順序，誰在前面都可以；

排序-合并連接配接适用的連接配接條件有： < <= = > >= ，不适用的連接配接條件有： <> like

（2） NESTED LOOPS（嵌套循環）：

内部連接配接過程：

a) 取出 row source 1 的 row 1（第一行資料），周遊 row source 2 的所有行并檢查是否有比對的，取出比對的行放入結果集中

b) 取出 row source 1 的 row 2（第二行資料），周遊 row source 2 的所有行并檢查是否有比對的，取出比對的行放入結果集中

c) ……

若 row source 1 （即驅動表）中傳回了 N 行資料，則 row source 2 也相應的會被全表周遊 N 次。

因為 row source 1 的每一行都會去比對 row source 2 的所有行，是以當 row source 1 傳回的行數盡可能少并且能高效通路 row source 2（如建立适當的索引）時，效率較高。

延伸：

嵌套循環的表有驅動順序，注意選擇合适的驅動表。

嵌套循環連接配接有一個其他連接配接方式沒有的好處是：可以先傳回已經連接配接的行，而不必等所有的連接配接操作處理完才傳回資料，這樣可以實作快速響應。

應盡可能使用限制條件（Where過濾條件）使驅動表（row source 1）傳回的行數盡可能少，同時在比對表（row source 2）的連接配接操作關聯列上建立唯一索引（UNIQUE INDEX）或是選擇性較好的非唯一索引，此時嵌套循環連接配接的執行效率會變得很高。若驅動表傳回的行數較多，即使比對表連接配接操作關聯列上存在索引，連接配接效率也不會很高。

（3）HASH JOIN（哈希連接配接） :

哈希連接配接隻适用于等值連接配接（即連接配接條件為 = ）

HASH JOIN對兩個表做連接配接時并不一定是都進行全表掃描，其并不限制表通路方式；

内部連接配接過程簡述：

a) 取出 row source 1（驅動表，在HASH JOIN中又稱為Build Table） 的資料集，然後将其建構成記憶體中的一個 Hash Table（Hash函數的Hash KEY就是連接配接操作關聯列），建立Hash位圖（bitmap）。

b) 取出 row source 2（比對表）的資料集，對其中的每一條資料的連接配接操作關聯列使用相同的Hash函數并找到對應的 a) 裡的資料在 Hash Table 中的位置，在該位置上檢查能否找到比對的資料。

補充：

還想進一步加深學習的同學可以點選下列連結，寫的算比較詳細的了。

1. Oracle表三種連接配接方式(sql優化)
2. 看懂Oracle執行計劃