Oracle+表連接方式+詳解

1、Oracle表之間的連接分為三種：內連接（自然連接）外連接（1）左外連接（左邊的表不加限制）（2）右外連接（右邊的表不加限制）（3）全外連接（左右兩表都不加限制）自連接（同一張表內的連接）SQL的標準語法：select table1.column,table2.column from tablel inner I left I right I full join table2 on tablel.columnl = table2.column2;inner join 表示內連接；left join表示左外連接；right join表示右夕卜連接；full join表示完全外連接；on子句用于指

2、定連接條件。注意：如果使用from子句指定內、外連接，則必須要使用on子句指定連接條件如果使用（+ ）操作符指定外連接，則必須使用where子句指定連接條件一.內連接（I nner Join/Join）1.1 Inner JoinInner join邏輯運算符返回滿足第一個（頂端）輸入與第二個（底端）輸入聯(lián)接的每一行。這個和用select查詢多 表是一樣的效果，所以內連接用的很少。還有一點要說明的就是Join默認就是innerjoin。所以我們在寫內連接的時候可以省略inner這個關鍵字。1.2下面舉例來說明內連接：1.2.1先創(chuàng)建2張測試表并插入數(shù)據(jù)：SQL select * from da

3、ve;ID NAMEdaveblbldaveSQL select * from bl;IDNAMEdavebl1.2.3用內鏈接進行查詢：SQL Select a.id,, from dave a inner join bl b on a.id=b.id; - 標準寫法ID NAME NAMEdavebldavebldaveblbldaveSQL Select a.id,, from dave a join bl b on a.id=b.id;- 這里省略了 inner 關鍵字ID NAME NAME1 davedave2 blbl1 bldav

4、e2 daveblSQL Select a.id,, from dave a,bl b where a.id=b.id; - select 多表查詢ID NAME NAME1 davedave2 blbl1 bldave2 davebl從這三個SQL的結果我們也可以看出，他們的作用是一樣的。1.3 自然連接（Natural join）自然連接是在兩張表中尋找那些數(shù)據(jù)類型和列名都相同的字段，然后自動地將他們連接起來，并返回所有符合條 件按的結果。先看一下自然連接的例子：SQL Select id,name from dave a natural join bl b;ID

5、NAMEdavebl這里我們并沒有指定連接的條件，實際上oracle為我們自作主張的將，dave表中的id和name字段與bl表中的id 和name字段進行了連接。也就是實際上相當于SQL Select dave.id, From dave join bl on dave.id = bl.id and =;ID NAMEdavebl因此，我們也可以將自然連接理解為內連接的一種。有關自然連接的一些注意事項：（1）.如果做自然連接的兩個表的有多個字段都滿足有相同名稱個類型，那么他們會被作為自然連接的條件。（2）.如果自然連接的兩個表僅是字段名稱相同，但

6、數(shù)據(jù)類型不同，那么將會返回一個錯誤。二.外連接（Outer Join）outer join則會返回每個滿足第一個（頂端）輸入與第二個（底端）輸入的聯(lián)接的行。它還返回任何在第二個輸入 中沒有匹配行的第一個輸入中的行。外連接分為三種：左外連接，右外連接，全外連接。對應SQL： LEFT/RIGHT/FULL OUTER JOIN。通常我們省略outer這個關鍵字。寫成：LEFT/RIGHT/FULL JOIN。在左外連接和右外連接時都會以一張表為基表，該表的內容會全部顯示，然后加上兩張表匹配的內容。如果基表 的數(shù)據(jù)在另一張表沒有記錄。那么在相關聯(lián)的結果集行中列顯示為空值（NULL）。對于外連接，也

7、可以使用“（+）來表示。關于使用（+）的一些注意事項：（ + ）操作符只能出現(xiàn)在where子句中，并且不能與outer join語法同時使用。當使用（+）操作符執(zhí)行外連接時，如果在where子句中包含有多個條件，則必須在所有條件中都包含（+ ）操 作符（ +）操作符只適用于列，而不能用在表達式上。( +)操作符不能與or和in操作符一起使用。(+)操作符只能用于實現(xiàn)左外連接和右外連接，而不能用于實現(xiàn)完全外連接。 在做實驗之前，我們先將dave表和bl里加一些不同的數(shù)據(jù)。以方便測試。SQL select * from bl;ID NAMEdaveblbig birdexc9懷寧SQL selec

8、t * from dave;ID NAME8安慶dave TOC o 1-5 h z blbldavedbasf-expressdmm2.1 左外連接(Left outer join/ left join)left join是以左表的記錄為基礎的,示例中Dave可以看成左表,BL可以看成右表，它的結果集是Dave表中的數(shù)據(jù)， 在加上Dave表和BL表匹配的數(shù)據(jù)。換句話說，左表(Dave)的記錄將會全部表示出來,而右表(BL)只會顯示符合搜索條件 的記錄。BL表記錄不足的地方均為NULL.示例：SQL select * from dave a left join bl b on a.id = b

9、.id;ID NAMEID NAME-1 bl1 dave1 dave1 dave2 dave2 bl2 bl2 bl3 dba3 big bird4 sf-express4 exc5 dmm-此處B表為null，因為沒有匹配到8 安慶-此處B表為null，因為沒有匹配到SQL select * from dave a left outer join bl b on a.id = b.id;ID NAMEID NAME1 bl1 dave1 dave1 dave2 dave2 bl2 bl2 bl3 dba3 big bird4 sf-express4 excdmm8安慶用(+)來實現(xiàn)，這個+

10、號可以這樣來理解：+表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在右表， 左表就是全部顯示，故是左連接。SQL Select * from dave a,bl b where a.id=b.id(+);-注意： 用(+)就要用關鍵字 whereID NAMEID NAME1 bldavedavebldbasf-expressdmm8 安慶1 davedaveblblbig birdexc2.2右外連接(right outer join/ right join)和left join的結果剛好相反，是以右表(BL)為基礎的，顯示BL表的所以記錄，在加上Dave和BL匹配的結果。Dave

11、表不足的地方用NULL填充.示例：SQL select * from dave a right join bl b on a.id = b.id;ID NAMEID NAMEdaveblbldavedbasf-expressdavebldaveblbig bird4 exc9懷寧-此處左表不足用Null填充已選擇7行。SQL select * from dave a right outer join bl b on a.id = b.id;ID NAMEID NAME1 dave1 dave2 bl2 bl1 bl1 dave2 dave2 bl3 dba3 big bird4 sf-expr

12、ess4 exc9懷寧-此處左表不足用Null填充已選擇7行。用(+)來實現(xiàn)，這個+號可以這樣來理解：+表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在左表， 右表就是全部顯示，故是右連接。SQL Select * from dave a,bl b where a.id(+)=b.id;ID NAMEID NAME1 dave1 dave2 bl2 bl1 bl1 dave2 dave2 bl3 dba3 big bird4 sf-express4 exc9懷寧2.3 全外連接(full outer join/ full join)示例：左表和右表都不做限制，所有的記錄都顯示，兩表不足

13、的地方用null填充。全外連接不支持（+）這種寫法。SQL select * from dave a full join bl b on a.id = b.id;ID NAMEID NAME8安慶1 dave1 dave2 bl2 bl1 bl1 dave2 dave2 bl3 dba3 big bird4 sf-express4 exc5 dmm9懷寧已選擇9行。SQL select * from dave a full outer join bl b on a.id = b.id;ID NAMEID NAME8安慶1 dave1 dave2 bl2 bl1 bl1 dave2 dave2

14、bl3 dba3 big bird4 sf-express4 exc5 dmm9懷寧已選擇9行。三.自連接自連接（self join ）是SQL語句中經(jīng)常要用的連接方式，使用自連接可以將自身表的一個鏡像當作另一個表來對待， 從而能夠得到一些特殊的數(shù)據(jù)。示例：在oracle的scott的schema中有一個表是emp。在emp中的每一個員工都有自己的mgr（經(jīng)理），并且每一個經(jīng)理自 身也是公司的員工，自身也有自己的經(jīng)理。下面我們需要將每一個員工自己的名字和經(jīng)理的名字都找出來。這時候我們該怎么做呢？ 如果我們有兩張這樣的表分別教worker和mgr，那么我們就很好寫SQL語句。Select wor

15、,M 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 mgrwhere work.mgr = mgr.empnoorder by work.ename;WORKERMANAGER-ADAMSSCOTTALLENBLAKEBLAKEKINGCLARKKING

16、FORDJONESJAMESBLAKEJONESKINGMARTINBLAKEMILLERCLARKSCOTTJONESSMITHFORDWORKERMANAGERTURNERBLAKEWARDBLAKE已選擇13行。各種連接的一個圖示:連匿型型有夕多屋(ttiata j 連接應接匹配的行色含左場的咨附亍 .不管宜邊的寸中是三專 左無七匹球1行:以及右邊表中全SE匹配的 行包含右混的堅明C不宵左選的表中易蘭三 在旨E-il匹配由行:以及左邊表券E匹配的包含左，右兩t洼玉全寄穴.K穹您一的 表中號:蘭年左匹配住用等Ifi叱電冬件養(yǎng)匹配左，右麗個表U的例子騏led A.C1.BJC2 from A Join B on Ax3 = B.cB;國Mt A.