oracle表連接的方法和類型有哪些

這篇文章主要講解了“oracle表連接的方法和類型有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“oracle表連接的方法和類型有哪些”吧！

一、表連接就是在多個表之間用連接條件連接在一起，當優化器在解析帶有表連接的sql時，除了會根據sql的寫法來決定表連接的類型，還要確定下面三種情況才能指定出執行計劃

1、表連接順序，不管幾個表的連接，sql在執行時，只能進行兩表的連接，然后根據連接結果再進行下一個兩表的連接，直至所有表都連接完成，連接的順序有兩層含義，一是兩表連接時決定好誰是outer table (驅動表） 誰是inner table(被驅動表），二是在多表連接的情況下，先連接哪兩張表

2、表連接方法，有四種，排序合并連接，嵌套循環連接，哈希連接，笛卡爾連接，優化器在解析sql時要決定采用哪種連接

3、訪問單表的方法 優化器在表表連接時，還要決定如何去讀取單表中的數據，比如用全表掃還是走索引，走索引的話該如何走索引，等等

二、表連接的類型

類型分為內鏈接和外連接，類型決定了表連接的結果，sql的寫法直接決定了類型

1、內鏈接:連接結果只包含那些完全滿足連接條件的記錄，只要sql里沒寫外連接的關鍵字，那就是內鏈接,內鏈接的三種寫法，其中一是oracle專用的

SELECT T1.COL1,T1.COL2,T2.COL3 FROM T1,T2 WHERE T1.COL2=T2.COL2;

SELECT T1.COL1,T1.COL2,T2.COL3   FROM T1 JOIN T2 ON(T1.COL2=T2.COL2);

 SELECT T1.COL1, COL2,T2.COL3   FROM T1 JOIN T2 USING(COL2);

標準sql中還有一種natural join ,表示只用兩個表所有的同名列一起作為連接列，不推薦使用，雖然會省去寫連接列，但是會增加sql報錯的風險

2、外連接，連接結果除了包含那些完全滿足連接條件的記錄，還包含所有驅動表中不滿足連接條件的記錄，外連接分三種，左連接（left outer join),右連接（right outer join），全連接（full outer join)

左連接，左邊為驅動表

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+);

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1 left outer join T2 on (t1.col2=t2.col2);

SELECT T1.COL1, COL2,T2.COL3   FROM T1 left outer join T2 USING(COL2);

右連接，右邊為驅動表

SELECT T1.COL1,t2.COL2,T2.COL3   FROM T1,t2 where t1.col2(+)=t2.col2;

SELECT T1.COL1, COL2,T2.COL3   FROM T1 right outer join T2 USING(COL2);

SELECT T1.COL1,t2.COL2,T2.COL3  from t1 right join t2 on(t1.col2=t2.col2);

全連接，相當于左連接 union 右連接

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 full join t2 on(t1.col2=t2.col2);

3、當連接除了連接條件外，還有其他的條件時

內鏈接下面倆個語句結果相同

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 join  t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 join  t2 on(t1.col2=t2.col2) where t1.col1=1;

外連接就會發生不同，所以在外連接中除了連接限制條件外，其他的限制條件所處的文本位置 ，會影響最后的結果

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 left outer join  t2 on(t1.col2=t2.col2 and t1.col1=1);

SELECT T1.COL1,t1.COL2,T2.COL3  from t1 left join  t2 on(t1.col2=t2.col2) where t1.col1=1;

上面兩條標準sql 語句在oracle中用（+）來表示時，分別可以寫成

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1(+)=1;

SELECT T1.COL1,t1.COL2,T2.COL3   FROM T1,t2 where t1.col2=t2.col2(+) and t1.col1=1;

t1.col2(+)=1即可表示這個條件也在連接限制條件內。

上述第一條語句的執行計劃會使用哈希外連接（hash join outer),而第二條語句會使用哈希連接（hash join),即它實際上時用等價的內鏈接來執行的

內鏈接適用的natural join 同樣可以用于外連接

三、表連接的方法

1、排序合并連接（sort merge join)

兩個表各自排序后合并得到結果集

執行效率不如哈希連接，但是比哈希連接適用更大的范圍，因為哈希連接通常只用于等值連接條件，排序合并連接可以使用不同的連接條件，比如 < <= > => 等等。

并不適合在OLAP，當然如果可以避免排序，也可以用在OLAP，比如在兩個表各自的連接列上存在索引

嚴格意義上排序合并連接并不存在驅動表的概念

2、嵌套循環連接（nested loops join)

兩個表連接時依靠兩層嵌套循環（外層循環和內層循環）來得到結果集

步驟

a、優化器根據規則決定驅動表和被驅動表，驅動表用于外層，被驅動表用于內層

b、根據謂語條件訪問驅動表，得到結果集1

c、遍歷結果集1同時遍歷被驅動表，即先取出結果集1中的一條記錄，然后根據這條記錄遍歷被驅動表，找出相匹配的記錄，然后再取出結果集1中的第二條記錄，繼續遍歷被驅動表，直到結果集1所有記錄取完，即結果集1中有多少條記錄，就會遍歷循環多少次被驅動表，就會有多少次內層循環

嵌套循環連接的關鍵點在于結果集1的數據量，數據量少效率就會高，同時這種連接有一個其他連接沒有的優點：可以實現快速響應。即可以快速返回已經連接過，且滿足連接條件的記錄，而不用等所有的連接全部完成

oracle 11g中加入了向量I/O（vector I/O)提高嵌套循環的性能

3、哈希連接（hash join)

兩個表連接時通過哈希運算來得到結果集

_hash_join_enabled 參數 默認為true ，啟用hash_join

alter session set "_hash_join_enabled"=true;

alter session set "_hash_join_enabled"=false;

use_hash hint 優先級比這個參數高

Si & Bj   Sn&Bn(代表磁盤中的hash partition)

hash的優缺點和適用場景

a、hash不一定排序，或者說大部分都不會排序

b、驅動表的連接列可選擇性（selectivity)盡可能好,因為這會影響hash bucket中的記錄數

c、只適用于CBO，且只適用于等值連接

d、適合小表和大表做連接且結果集較大的情況，小表連接列可選擇性非常好的情況下，哈希連接的執行時間相當于大表做全表掃描的時間

e、兩個表連接時，做完hash后，hash table能完全在內存（PGA）中的話，哈希連接的執行效率會非常高

4、笛卡爾連接（cross join)

兩個表在做連接時沒有任何連接條件的表連接方法，實際上是一種特殊的合并連接，并不排序（MERGE JOIN CARTESIAN），T1結果集為m,T2結果集為n,則笛卡爾連接后的記錄數為M*N

 select t1.col1,t2.col3 from t1,t2;

標準sql : select t1.col1,t2.col3 from t1 cross join t2;

笛卡爾連接一般是不好的，往往是因為漏寫了連接條件或者用了ordered hint，而sql文本上相鄰的倆個表有沒有直接的關聯條件造成的，還有可能是統計信息不準確造成。除非是刻意的，比如為了避免多次的大表全表掃描才會使用這種連接。

四、反連接（anti join)

結果集1，結果集2，滿足t1.col2=t2.col2的都會被去除，只返回不滿足連接條件的值

select * from t1 where col2 not in( select col2 from t2);

select * from t1 where col2 <> all(select col2 from t2);

select * from t1 where not exists(select 1 from t2 where col2=t1.col2);

當t1,t2沒有null值時 ，上面三條語句結果相同

當有null時，結果會不一樣

 not in 和 <> all 對null敏感，當他們后面的子查詢或常量集合有null，則整個sql的執行結果就是null

not exists對null不敏感，不影響執行結果

五、半連接（semi join)

t1,t2連接時，驅動表t1,被驅動表t2,即使t2中滿足連接條件t1.col2=t2.col2有多條記錄，也只會返回第一條記錄，即半連接時特殊的內連接，實際具有去重的作用。當子查詢展開時，oracle 通常會把where 后的條件 =any,exist,in等查詢轉換為對應的半連接。

select * from t1 where col2 in(select col2 from t2);

 select * from t1 where col2= any(select col2 from t2);

select * from t1 where exists(select col2 from t2 where col2=t1.col2);    

六、星形連接（star join)

通常用于數據倉庫，既不是連接類型，也不是連接方法，他是一個事實表（fact table)與多個維度表（dimension table)之間的連接，基本上事實表的外鍵列對應各維度表的主鍵列，事實表是張大表，后面章節會詳細描述這個連接

感謝各位的閱讀，以上就是“oracle表連接的方法和類型有哪些”的內容了，經過本文的學習后，相信大家對oracle表連接的方法和類型有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！