詳解Oracle多種表連接方式
1. 內(nèi)連接(自然連接)
2. 外連接
（1）左外連接 (左邊的表不加限制)
（2）右外連接(右邊的表不加限制)
（3）全外連接(左右兩表都不加限制)
3. 自連接（同一張表內(nèi)的連接）
SQL的標(biāo)準(zhǔn)語法：
- select table1.column,table2.column
- from table1 [inner | left | right | full ] join table2 on table1.column1 = table2.column2;
inner join 表示內(nèi)連接；
left join表示左外連接；
right join表示右外連接；
full join表示完全外連接；
on子句 用于指定連接條件。
注意：
如果使用from子句指定內(nèi)、外連接，則必須要使用on子句指定連接條件；
如果使用（+）操作符指定外連接，則必須使用where子句指定連接條件。
一． 內(nèi)連接（Inner Join/Join）
1.1 Inner Join
Inner join邏輯運(yùn)算符返回滿足***個(gè)（頂端）輸入與第二個(gè)（底端）輸入聯(lián)接的每一行。這個(gè)和用select查詢多表是一樣的效果，所以內(nèi)連接用的很少。
還有一點(diǎn)要說明的就是Join 默認(rèn)就是inner join。 所以我們在寫內(nèi)連接的時(shí)候可以省略inner 這個(gè)關(guān)鍵字。
1.2 下面舉例來說明內(nèi)連接：
1.2.1 先創(chuàng)建2張測試表并插入數(shù)據(jù)：
- SQL> select * from dave;
- ID NAME
- ---------- ----------
- 1 dave
- 2 bl
- 1 bl
- 2 dave
- SQL> select * from bl;
- ID NAME
- ---------- ----------
- 1 dave
- 2 bl
1.2.3 用內(nèi)鏈接進(jìn)行查詢：
- SQL> Select a.id,a.name,b.name from dave a inner join bl b on a.id=b.id; -- 標(biāo)準(zhǔn)寫法
- ID NAME NAME
- ---------- ---------- ----------
- 1 dave dave
- 2 bl bl
- 1 bl dave
- 2 dave bl
- SQL> Select a.id,a.name,b.name from dave a join bl b on a.id=b.id; -- 這里省略了inner 關(guān)鍵字
- ID NAME NAME
- ---------- ---------- ----------
- 1 dave dave
- 2 bl bl
- 1 bl dave
- 2 dave bl
- SQL> Select a.id,a.name,b.name from dave a,bl b where a.id=b.id; -- select 多表查詢
- ID NAME NAME
- ---------- ---------- ----------
- 1 dave dave
- 2 bl bl
- 1 bl dave
- 2 dave bl
從這三個(gè)SQL 的結(jié)果我們也可以看出，他們的作用是一樣的。
1.3 自然連接(Natural join)
自然連接是在兩張表中尋找那些數(shù)據(jù)類型和列名都相同的字段，然后自動地將他們連接起來，并返回所有符合條件按的結(jié)果。
先看一下自然連接的例子:
- SQL> Select id,name from dave a natural join bl b;
- ID NAME
- ---------- ----------
- 1 dave
- 2 bl
這里我們并沒有指定連接的條件，實(shí)際上oracle為我們自作主張的將，dave表中的id和name字段與bl表中的id和name字段進(jìn)行了連接。也就是實(shí)際上相當(dāng)于
- SQL> Select dave.id,bl.name
- From dave join bl on dave.id = bl.id and dave.name=bl.name;
- ID NAME
- ---------- ----------
- 1 dave
- 2 bl
因此，我們也可以將自然連接理解為內(nèi)連接的一種。
有關(guān)自然連接的一些注意事項(xiàng)：
（1）.如果做自然連接的兩個(gè)表的有多個(gè)字段都滿足有相同名稱和類型，那么他們會被作為自然連接的條件。
（2）.如果自然連接的兩個(gè)表僅是字段名稱相同，但數(shù)據(jù)類型不同，那么將會返回一個(gè)錯(cuò)誤。
二． 外連接(Outer Join)
outer join則會返回每個(gè)滿足***個(gè)（頂端）輸入與第二個(gè)（底端）輸入的聯(lián)接的行。它還返回任何在第二個(gè)輸入中沒有匹配行的***個(gè)輸入中的行。外連接分為三種： 左外連接，右外連接，全外連接。 對應(yīng)SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我們省略outer 這個(gè)關(guān)鍵字。 寫成：LEFT/RIGHT/FULL JOIN。
在左外連接和右外連接時(shí)都會以一張表為基表，該表的內(nèi)容會全部顯示，然后加上兩張表匹配的內(nèi)容。 如果基表的數(shù)據(jù)在另一張表沒有記錄。 那么在相關(guān)聯(lián)的結(jié)果集行中列顯示為空值（NULL）。
對于外連接， 也可以使用“(+) ”來表示。 關(guān)于使用（+）的一些注意事項(xiàng)：
1.（+）操作符只能出現(xiàn)在where子句中，并且不能與outer join語法同時(shí)使用。
2. 當(dāng)使用（+）操作符執(zhí)行外連接時(shí)，如果在where子句中包含有多個(gè)條件，則必須在所有條件中都包含（+）操作符
3.（+）操作符只適用于列，而不能用在表達(dá)式上。
4.（+）操作符不能與or和in操作符一起使用。
5.（+）操作符只能用于實(shí)現(xiàn)左外連接和右外連接，而不能用于實(shí)現(xiàn)完全外連接。
在做實(shí)驗(yàn)之前，我們先將dave表和bl里加一些不同的數(shù)據(jù)。 以方便測試。
- SQL> select * from bl;
- ID NAME
- ---------- ----------
- 1 dave
- 2 bl
- 3 big bird
- 4 exc
- 9 懷寧
- SQL> select * from dave;
- ID NAME
- ---------- ----------
- 8 安慶
- 1 dave
- 2 bl
- 1 bl
- 2 dave
- 3 dba
- 4 sf-express
- 5 dmm
2.1 左外連接（Left outer join/ left join）
left join是以左表的記錄為基礎(chǔ)的,示例中Dave可以看成左表,BL可以看成右表,它的結(jié)果集是Dave表中的數(shù)據(jù)，在加上Dave表和BL表匹配的數(shù)據(jù)。換句話說,左表(Dave)的記錄將會全部表示出來,而右表(BL)只會顯示符合搜索條件的記錄。BL表記錄不足的地方均為NULL.
示例：
- SQL> select * from dave a left join bl b on a.id = b.id;
- ID NAME ID NAME
- --------- ---------- ---------- ----------
- 1 bl 1 dave
- 1 dave 1 dave
- 2 dave 2 bl
- 2 bl 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 5 dmm -- 此處B表為null，因?yàn)闆]有匹配到
- 8 安慶 -- 此處B表為null，因?yàn)闆]有匹配到
- SQL> select * from dave a left outer join bl b on a.id = b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 1 bl 1 dave
- 1 dave 1 dave
- 2 dave 2 bl
- 2 bl 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 5 dmm
- 8 安慶
用（+）來實(shí)現(xiàn)， 這個(gè)+號可以這樣來理解： + 表示補(bǔ)充，即哪個(gè)表有加號，這個(gè)表就是匹配表。所以加號寫在右表，左表就是全部顯示，故是左連接。
- SQL> Select * from dave a,bl b where a.id=b.id(+); -- 注意： 用（+） 就要用關(guān)鍵字where
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 1 bl 1 dave
- 1 dave 1 dave
- 2 dave 2 bl
- 2 bl 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 5 dmm
- 8 安慶
2.2 右外連接（right outer join/ right join）
和left join的結(jié)果剛好相反,是以右表(BL)為基礎(chǔ)的, 顯示BL表的所以記錄，在加上Dave和BL 匹配的結(jié)果。 Dave表不足的地方用NULL填充.
示例：
- SQL> select * from dave a right join bl b on a.id = b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 1 dave 1 dave
- 2 bl 2 bl
- 1 bl 1 dave
- 2 dave 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 9 懷寧 --此處左表不足用Null 填充
已選擇7行。
- SQL> select * from dave a right outer join bl b on a.id = b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 1 dave 1 dave
- 2 bl 2 bl
- 1 bl 1 dave
- 2 dave 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 9 懷寧 --此處左表不足用Null 填充
已選擇7行。
用（+）來實(shí)現(xiàn)， 這個(gè)+號可以這樣來理解： + 表示補(bǔ)充，即哪個(gè)表有加號，這個(gè)表就是匹配表。所以加號寫在左表，右表就是全部顯示，故是右連接。
- SQL> Select * from dave a,bl b where a.id(+)=b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 1 dave 1 dave
- 2 bl 2 bl
- 1 bl 1 dave
- 2 dave 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 9 懷寧
2.3 全外連接（full outer join/ full join）
左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null 填充。 全外連接不支持（+）這種寫法。
示例：
- SQL> select * from dave a full join bl b on a.id = b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 8 安慶
- 1 dave 1 dave
- 2 bl 2 bl
- 1 bl 1 dave
- 2 dave 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 5 dmm
- 9 懷寧
已選擇9行。
- SQL> select * from dave a full outer join bl b on a.id = b.id;
- ID NAME ID NAME
- ---------- ---------- ---------- ----------
- 8 安慶
- 1 dave 1 dave
- 2 bl 2 bl
- 1 bl 1 dave
- 2 dave 2 bl
- 3 dba 3 big bird
- 4 sf-express 4 exc
- 5 dmm
- 9 懷寧
已選擇9行。
三． 自連接
自連接(self join)是SQL語句中經(jīng)常要用的連接方式，使用自連接可以將自身表的一個(gè)鏡像當(dāng)作另一個(gè)表來對待，從而能夠得到一些特殊的數(shù)據(jù)。
示例：
在oracle的scott的schema中有一個(gè)表是emp。在emp中的每一個(gè)員工都有自己的mgr(經(jīng)理)，并且每一個(gè)經(jīng)理自身也是公司的員工，自身也有自己的經(jīng)理。
下面我們需要將每一個(gè)員工自己的名字和經(jīng)理的名字都找出來。這時(shí)候我們該怎么做呢？
如果我們有兩張這樣的表分別教worker和mgr，那么我們就很好寫SQL語句。
Select worker.name,
Mgr.name
From worker,mgr
Where worker.id = mgr.id;
但現(xiàn)在我們只有一張emp表。所以我們可以采用自連接。自連接的本意就是將一張表看成多張表來做連接。我們可以這樣來寫SQL語句:
- SQL> select work.ename worker,mgr.ename manager from scott.emp work, scott.emp mgr
- 2 where work.mgr = mgr.empno(+)
- 3 order by work.ename;
- WORKER MANAGER
- ---------- ----------
- ADAMS SCOTT
- ALLEN BLAKE
- BLAKE KING
- CLARK KING
- FORD JONES
- JAMES BLAKE
- JONES KING
- KING --此處右表不足用Null 填充
- MARTIN BLAKE
- MILLER CLARK
- SCOTT JONES
- SMITH FORD
- TURNER BLAKE
- WARD BLAKE
已選擇14行。
【編輯推薦】
