Oracle+表連接方式+詳解

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

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

3、ve;ID NAMEdaveblbldaveSQL select * from bl;IDNAMEdavebl1.2.3用內(nèi)鏈接進(jìn)行查詢(xún)：SQL Select a.id,, from dave a inner join bl b on a.id=b.id; - 標(biāo)準(zhǔn)寫(xiě)法ID NAME NAMEdavebldavebldaveblbldaveSQL Select a.id,, from dave a join bl b on a.id=b.id;- 這里省略了 inner 關(guān)鍵字ID NAME NAME1 davedave2 blbl1 bldav

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

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

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

7、可以使用“（+）來(lái)表示。關(guān)于使用（+）的一些注意事項(xiàng)：（ + ）操作符只能出現(xiàn)在where子句中，并且不能與outer join語(yǔ)法同時(shí)使用。當(dāng)使用（+）操作符執(zhí)行外連接時(shí)，如果在where子句中包含有多個(gè)條件，則必須在所有條件中都包含（+ ）操 作符（ +）操作符只適用于列，而不能用在表達(dá)式上。( +)操作符不能與or和in操作符一起使用。(+)操作符只能用于實(shí)現(xiàn)左外連接和右外連接，而不能用于實(shí)現(xiàn)完全外連接。 在做實(shí)驗(yàn)之前，我們先將dave表和bl里加一些不同的數(shù)據(jù)。以方便測(cè)試。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是以左表的記錄為基礎(chǔ)的,示例中Dave可以看成左表,BL可以看成右表，它的結(jié)果集是Dave表中的數(shù)據(jù)， 在加上Dave表和BL表匹配的數(shù)據(jù)。換句話說(shuō)，左表(Dave)的記錄將會(huì)全部表示出來(lái),而右表(BL)只會(huì)顯示符合搜索條件 的記錄。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，因?yàn)闆](méi)有匹配到8 安慶-此處B表為null，因?yàn)闆](méi)有匹配到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安慶用(+)來(lái)實(shí)現(xiàn)，這個(gè)+

10、號(hào)可以這樣來(lái)理解：+表示補(bǔ)充，即哪個(gè)表有加號(hào)，這個(gè)表就是匹配表。所以加號(hào)寫(xiě)在右表， 左表就是全部顯示，故是左連接。SQL Select * from dave a,bl b where a.id=b.id(+);-注意： 用(+)就要用關(guān)鍵字 whereID NAMEID NAME1 bldavedavebldbasf-expressdmm8 安慶1 davedaveblblbig birdexc2.2右外連接(right outer join/ right join)和left join的結(jié)果剛好相反，是以右表(BL)為基礎(chǔ)的，顯示BL表的所以記錄，在加上Dave和BL匹配的結(jié)果。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行。用(+)來(lái)實(shí)現(xiàn)，這個(gè)+號(hào)可以這樣來(lái)理解：+表示補(bǔ)充，即哪個(gè)表有加號(hào)，這個(gè)表就是匹配表。所以加號(hào)寫(xiě)在左表， 右表就是全部顯示，故是右連接。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填充。全外連接不支持（+）這種寫(xiě)法。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語(yǔ)句中經(jīng)常要用的連接方式，使用自連接可以將自身表的一個(gè)鏡像當(dāng)作另一個(gè)表來(lái)對(duì)待， 從而能夠得到一些特殊的數(shù)據(jù)。示例：在oracle的scott的schema中有一個(gè)表是emp。在emp中的每一個(gè)員工都有自己的mgr（經(jīng)理），并且每一個(gè)經(jīng)理自 身也是公司的員工，自身也有自己的經(jīng)理。下面我們需要將每一個(gè)員工自己的名字和經(jīng)理的名字都找出來(lái)。這時(shí)候我們?cè)撛趺醋瞿兀?如果我們有兩張這樣的表分別教worker和mgr，那么我們就很好寫(xiě)SQL語(yǔ)句。Select wor

15、,M From worker,mgr Where worker.id = mgr.id;但現(xiàn)在我們只有一張emp表。所以我們可以采用自連接。自連接的本意就是將一張表看成多張表來(lái)做連接。我們可以這樣來(lái)寫(xiě)SQL語(yǔ)句：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行。各種連接的一個(gè)圖示:連匿型型有夕多屋(ttiata j 連接應(yīng)接匹配的行色含左場(chǎng)的咨附亍 .不管宜邊的寸中是三專(zhuān) 左無(wú)七匹球1行:以及右邊表中全SE匹配的 行包含右混的堅(jiān)明C不宵左選的表中易蘭三 在旨E-il匹配由行:以及左邊表券E匹配的包含左，右兩t洼玉全寄穴.K穹您一的 表中號(hào):蘭年左匹配住用等Ifi叱電冬件養(yǎng)匹配左，右麗個(gè)表U的例子騏led A.C1.BJC2 from A Join B on Ax3 = B.cB;國(guó)Mt A.