SQL內(nèi)連接與外連接說課講解

1、SQL 內(nèi) 連 接 與 外 連 接1. 概述1、內(nèi)聯(lián)接(典型的聯(lián)接運(yùn)算，使用像=或 <> 之類的比較運(yùn)算符)。包括相 等聯(lián)接和自然聯(lián)接內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。例如，檢 索students和courses表中學(xué)生標(biāo)識號相同的所有行。2、外聯(lián)接。外聯(lián)接可以是左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接。在FROM子句中指定外聯(lián)接時(shí)，可以由下列幾組關(guān)鍵字中的一組指定：1) LEFT JOIN或 LEFT OUTER JOIN左向外聯(lián)接的結(jié)果集包括LEFT OUTEF子句中指定的左表的 所有行，而不僅僅 是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在

2、相關(guān)聯(lián)的結(jié) 果集行中右表的所有選擇列表列均為空值。2) RIGHT JOIN 或 RIGHT OUTER JOIN右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。如果右表的某行 在左表中沒有匹配行，則將為左表返回空值。3) FULL JOIN 或 FULL OUTER JOIN完整外部聯(lián)接返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒有匹配行 時(shí)，則另一個(gè)表的選擇列表列包含空值。如果表之間有匹配行，則整個(gè)結(jié)果集 行包含基表的數(shù)據(jù)值。例子:a 表 id n ame b 表 id job pare nt_id1 張 3 1 23 12李四2 34 23王武3 34 4a.id 同parent

3、_id 存在關(guān)系1）內(nèi)連接select a.*,b.* from a inner join b on a.id=b.parent_id結(jié)果是1 張 3 1 23 12李四2 34 22）左連接select a.*,b.* from a left join b on a.id=b.pare nt_id結(jié)果是1 張 3 1 23 12李四2 34 23王武null3）右連接select a. *,b .* from a right join b on a. id=b .parent_id結(jié)果是1 張 3 1 23 12李四2 34 2n ull 3 34 44）完全連接（不做過多概述）select

4、 a.*,b.* from a full joi n b on a.id=b.pare nt_id結(jié)果是1 張 3 1 23 12李四2 34 2null3 34 43王武null2. 內(nèi)連接（INNER JOIN ）內(nèi)連接（INNER JOIN :有兩種，顯式的和隱式的，返回連接表中符合連接條 件和查詢條件的數(shù)據(jù)行。（所謂的鏈接表就是數(shù)據(jù)庫在做查詢形成的中間 表）。例如：下面的語句3和語句4的結(jié)果是相同的。語句1:隱式的內(nèi)連接，沒有INNER JOIN,形成的中間表為兩個(gè)表的笛卡爾 積。SELECT O.I D,O.ORDER_NUMBER,C .I D,C.NAMEFROM CUSTOME

5、RS C,ORDERS OWHERE C.I D=O.CUSTOMER_ID;語句2:顯示的內(nèi)連接，一般稱為內(nèi)連接，有 INNER JOIN,形成的中間表為兩 個(gè)表經(jīng)過ON條件過濾后的笛卡爾積。SELECT O.I D,O.ORDER_NUMBER,C .I D,C.NAMEFROM CUSTOMERS C INNER JOIN ORDERS O ON C.I D=O.CUSTOMER_ID;3. 外連接（OUTER JOIN ）:外連不但返回符合連接和查詢條件的數(shù)據(jù)行，還返回不符合條件的一些行。外連接分三類：左外連接（LEFT OUTER JOIN、右外連接（RIGHT OUTER JOIN

6、 和全外連接（FULL OUTER JOIN。三者的共同點(diǎn)是都返回符合連接條件和查詢條件（即：內(nèi)連接）的數(shù)據(jù)行。不 同點(diǎn)如下：左外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。右外連接還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行，并且還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接實(shí)際是上左外連接和右外連接的數(shù)學(xué)合集（去掉重復(fù)），即“全外=左外UNION右外”。說明：左表就是在“（LEFT OUTER JOIN ”關(guān)鍵字左邊的表。右表當(dāng)然就是右邊的了。在三種類型的外連接中，OUTEF關(guān)鍵字是可省略的。下面舉例說明：

7、語句3:左外連接（LEFT OUTER JOINSELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.I D=O.CUSTOMER_ID;語句4:右外連接（RIGHT OUTER JOINSELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.I D=O.CUSTOMER_ID;注意：WHER條件放

8、在ON后面查詢的結(jié)果是不一樣的。例如：語句5： WHER條件獨(dú)立。SELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.I D=O.CUSTOMER_IDWHERE O.ORDER_NUMBERv>'MIKE_ORDER001'語句6:將語句5中的WHER條件放到ON后面。SELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O LEFT OUT

9、ER JOIN CUSTOMSIRNCCD=O.CUSTOMER_ID AND O.ORDER_NUMBERv>'MIKE_ORDEROO1'從語句5和語句6查詢的結(jié)果來看，顯然是不相同的，語句 6顯示的結(jié)果是難 以理解的。因此，推薦在寫連接查詢的時(shí)候， ON后面只跟連接條件，而對中間 表限制的條件都寫到 WHER子句中。語句7:全外連接（FULL OUTER JOIN。SELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.

10、I D=O.CUSTOMER_ID;注意：MySQ是不支持全外的連接的，這里給出的寫法適合Oracle和DB2但是可以通過左外和右外求合集來獲取全外連接的查詢結(jié)果。語句&左外和右外的合集，實(shí)際上查詢結(jié)果和語句7是相同的。SELECT O.I D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .I D,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.I D=O.CUSTOMER_IDUNIONSELECT O.l D,O.ORDER_NUMBER,O.CUSTOMER_ID,C .1 D,C.NAMEFROM OR

11、DERS O RIGHT OUTER JOIN CUSTOMERS C ON C.l D=O.CUSTOMER_ID;4.SQL查詢的基本原理：兩種情況介紹第一、單表查詢：根據(jù) WHER條件過濾表中的記錄，形成中間表（這個(gè)中間表 對用戶是不可見的）；然后根據(jù) SELECT勺選擇列選擇相應(yīng)的列進(jìn)行返回最終結(jié) 果。第二、兩表連接查詢：對兩表求積（笛卡爾積）并用 ON條件和連接連接類型進(jìn) 行過濾形成中間表；然后根據(jù) WHER條件過濾中間表的記錄，并根據(jù) SELECT旨 定的列返回查詢結(jié)果。第三、多表連接查詢：先對第一個(gè)和第二個(gè)表按照兩表連接做查詢，然后用查詢結(jié)果和第三個(gè)表做連接查詢，以此類推，直到所

12、有的表都連接上為止，最終形成一個(gè)中間的結(jié)果表，然后根據(jù) WHER條件過濾中間表的記錄，并根據(jù) SELECT指定的列返回查詢結(jié)果。理解SQL查詢的過程是進(jìn)行SQL優(yōu)化的理論依據(jù)。ON后面的條件（ON條件）和 WHER條件的區(qū)別：ON條件：是過濾兩個(gè)鏈接表笛卡爾積形成中間表的約束條件。WHER條件：在有ON條件的SELECTS句中是過濾中間表的約束條件。在沒有 ON的單表查詢中，是限制物理表或者中間查詢結(jié)果返回記錄的約束。在兩表或多表連接中是限制連接形成最終中間表的返回結(jié)果的約束。從這里可以看出，將 WHER條件移入ON后面是不恰當(dāng)?shù)?。推薦的做法是：ON只進(jìn)行連接操作，WHER只過濾中間表的記錄。

13、總結(jié)連接查詢是SQL查詢的核心，連接查詢的連接類型選擇依據(jù)實(shí)際需求。如果選擇不當(dāng)，非但不能提高查詢效率，反而會帶來一些邏輯錯(cuò)誤或者性能低下。下面總結(jié)一下兩表連接查詢選擇方式的依據(jù)：1、查兩表關(guān)聯(lián)列相等的數(shù)據(jù)用內(nèi)連接。2、Col_L是Col_R的子集時(shí)用右外連接。3、Col_R是Col_L的子集時(shí)用左外連接。4、Col_R和Col_L彼此有交集但彼此互不為子集時(shí)候用全外。多個(gè)表查詢的時(shí)候，這些不同的連接類型可以寫到一塊。例如：SELECT T1.C1,T2.CX,T3.CYFROM TAB1 T1INNER JOIN TAB2 T2 ON （T1.C仁T2.C2）INNER JOIN TAB3

14、T3 ON （T1.C1=T2.C3）LEFT OUTER JOIN TAB4 ON（T2.C2=T3.C3）;WHERE T1.X >T3.Y;上面這個(gè)SQL查詢是多表連接的一個(gè)示范。5操作符下的左右連接對于外連接，也可以使用“（+）來表示。關(guān)于使用（+）的一些注意事項(xiàng)：1. （+）操作符只能出現(xiàn)在where子句中，并且不能與outer join語法同時(shí)使 用。2. 當(dāng)使用（+）操作符執(zhí)行外連接時(shí)，如果在 where子句中包含有多個(gè)條 件，則必須在所有條件中都包含（+）操作符3. （ + ）操作符只適用于列，而不能用在表達(dá)式上。4. （+）操作符不能與or和in操作符一起使用。5. （

15、+）操作符只能用于實(shí)現(xiàn)左外連接和右外連接左連接用（+）來實(shí)現(xiàn)，這個(gè)+號可以這樣來理解：+表示補(bǔ)充，即哪個(gè)表有加 號，這個(gè)表就是匹配表。所以加號寫在右表，左表就是全部顯示，故是左連 接。SQL> Select * from dave a,bl b where a.id=b.id（+）;-注意： 用（+） 就要用 關(guān)鍵字where右連接用（+）來實(shí)現(xiàn)，這個(gè)+號可以這樣來理解：+表示補(bǔ)充，即哪個(gè)表有加 號，這個(gè)表就是匹配表。所以加號寫在左表，右表就是全部顯示，故是右連 接。SQL> Select * from dave a,bl b where a.id（+）=b.id;6.0n與whe

16、re的區(qū)別數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時(shí)，都會生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回給用戶。在使用left jion時(shí)，on和where條件的區(qū)別如下：1、 on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。2、where 條件是在臨時(shí)表生成好后，再對臨時(shí)表進(jìn)行過濾的條件。這時(shí)已經(jīng)沒有l(wèi)eft join 的含義（必須返回左邊表的記錄）了，條件 不為真的就全部過濾掉。假設(shè)有兩張表：表 1: tab2 id size1 102 203 30表 2： tab2 size name10 AAA20 BBB20 CCC兩條SQL:1、select *

17、 form tabl left join tab2 on (tabl.size = tab2.size) wheretab2. name= ' AAA2、select * form tabl left join tab2 on (tabl.size = tab2.sizeand = ' AAA第一條SQL的過程：1、中間表on條件：tabl.size = tab2.size tabl.id tabl.size tab2.size tab2 .n ame1 10 10 AAA2 20 20 BBB2 20 20 CCC3 30 (null) (n ull)II2

18、、再對中間表過濾where條件：tab2. name= ' AAAtabl.id tabl.size tab2.size tab2 .n ame1 10 10 AAA第二條SQL的過程:1、中間表 on條件:'AAAtab1.size = tab2.size and tab2 .n ame= （條件不為真也會返回左表中的記錄 ）tab1.id tab1.size tab2.size tab2. name1 10 10 AAA2 20 (n ull) (n ull)3 30 (null) (n ull)其實(shí)以上結(jié)果的關(guān)鍵原因就是left join,right join,full

19、join的特殊性，不管on上的條件是否為真都會返回left或right表中的記錄，full則具有l(wèi)eft和right的特性的并集。而inner jion沒這個(gè)特殊性，則條件放在 on中和where中，返回 的結(jié)果集是相同的。心得：外鏈接匹配，內(nèi)連接過濾，外鏈接即匹配又過濾用on和where搭配7.圖解SQL的內(nèi)連接與外連接內(nèi)外連接就相當(dāng)于多個(gè)業(yè)務(wù)表的一次合體，連接的條件必須是關(guān)鍵性的！連接成功就可看成一張表！在oracle的SQL語句常用的連接有內(nèi)連接(inner join)，外連接(outer join) 等，內(nèi)連接又包括等值連接，非等值連接，自連接；而外連接又分為左連接和 右連接。其中默認(rèn)

20、的是內(nèi)連接的等值連接。為了方便我們創(chuàng)建兩張最簡易的表 A、B，具體的表結(jié)構(gòu)參看下面，來分析內(nèi) 連接與外連接的區(qū)別 一對多關(guān)系,A 一(主表),B多(子表)，主表的一條記錄對應(yīng)子表的多條記錄from A；AIDAX AM1Al2 A23 A34 A45 A566 A 77 AS8 A9馬 rows sele匚圖1圖2orc 1 AS匚UT1loetBW戰(zhàn)from E：10021016102S10311046105SIM210721 19 口sc 1ccted 兩個(gè)表要做連接，必須要有連接字段，而在表A和表B中連接字段是Aid和Bn amid,下圖說明了連接之間關(guān)系圖3圖3（1）內(nèi)連接：利用內(nèi)連接

21、（等值）就可獲取藍(lán)色的公共部分 C，即圖3中的數(shù) 據(jù)集C，結(jié)果集為如下： 只顯示主表與子表有關(guān)聯(lián)的記錄，一個(gè)主表記錄可能有多個(gè)子表記錄（即在生成的 中間表中，主表數(shù)據(jù)一般重復(fù)出現(xiàn)，所以查詢出來的記錄數(shù)根據(jù)每個(gè)主表記錄有多 少個(gè)子表記錄而定，下同）Lrc LSCOTT>select* from XJoin b on A P A i J. Bn a m i dl *AID AN AMEIDBKAMID2 A23 0026 XQ10163 A310237 A7103tS A61046& A8MS82 210622 A?107w圖4其實(shí) select * from A join B on

22、 A.Aid=B.Bnamid; 等價(jià)于 select * from A,B where A.Aid=B.B namid;注：非等值連接主要的話是針對一個(gè)范圍來查詢數(shù)據(jù)，自連接主要就是把1張表堪稱兩張表來用(2)外連接：分為左外連接(left join)與右外連接(right join)左外連接即公共顯示的藍(lán)色部分 C1+顯示黃色的記錄集A1，顯示語句等價(jià)于select * from A,B where A.Aid=B.Bnamid ( +)；不但顯示主表與子表有關(guān)聯(lián)的記錄，還顯示主表與子表沒有關(guān)聯(lián)的主表記錄orct* from ALe f t Jain B on A . Ai< Bna

23、niid ：AID ANAMBIDBNAMID2 A2100cJ6 AB101ti3 A310237 A710316 ,A61046g A8103g2 ,A210622 A21072 AoI Al4 A4Q Q右外連接即公共顯示的藍(lán)色部分C1+顯示綠色的B1，顯示語句等價(jià)于selectfrom A,B where A.Aid(+)=B.Bnamid ；顯示子表在主表有關(guān)聯(lián)的記錄，還顯示子表與主表沒有關(guān)聯(lián)的子表記錄orcl i)rSCOTT>se 1 e 佇七 *f Fom A P i g h t J ci i n B on t.=. Bnanid 1AIDAN AMB!U2A210722

24、A210629A21002X3AS10236A6104G6A610167A7103呂AS05募10811& rows selected ”表A和表B情況是相對的，以上實(shí)驗(yàn)都是 A在左邊的情況，其實(shí)A left join B 與B right join A的情況的結(jié)果集是一樣的。自己的總結(jié)：兩表之間，只要有關(guān)聯(lián)，不管以什么關(guān)系存在，都可能有對方關(guān)聯(lián)或被 關(guān)聯(lián)不到的數(shù)據(jù)，這就是區(qū)分內(nèi)外連接的關(guān)鍵，數(shù)據(jù)的展示以關(guān)聯(lián)的一方為準(zhǔn)，被 關(guān)聯(lián)的可能重復(fù)被關(guān)聯(lián)以哪個(gè)表為準(zhǔn),就顯示這個(gè)表的全部信息，拋去另一個(gè)表獨(dú)有的部分這是僅僅的技術(shù)層面，至于要以哪個(gè)表為準(zhǔn)，要看業(yè)務(wù)邏輯的需要，哪個(gè)表的獨(dú)有 部分是我們

25、更想要的，這時(shí)候,表之間的對應(yīng)關(guān)系才更重要點(diǎn)，這是業(yè)務(wù)問題利用關(guān)鍵性的數(shù)據(jù)庫字段連接，才能更好地關(guān)聯(lián)8.SQL何時(shí)使用內(nèi)外連接所謂內(nèi)外連接，就是表與表有條件的拼接，內(nèi)連接是連接條件所列字段，(比女口 a.recorder = b.userid中的recorder與 userid) 有值且滿足表達(dá)式的記錄才 拼接成功并顯示，外鏈接的左外鏈接，連接條件是給右表說的，連接表達(dá)式成立 的記錄與左表拼接，不成立的行不顯示，不管拼接表達(dá)式是否成立，左表照常 顯示，連接條件不受限制，并不是必須有主表，也可以是和主表連接的其他表組 成連接條件需要查找兩張表同時(shí)存在的數(shù)據(jù)，使用內(nèi)連接，需要查找兩張表中一張表 存

26、在，另一張表不存在的時(shí)候使用外連接。SELECT T1.C1,T2.CX,T3.* -如果沒有要求特別顯示哪個(gè)字段，則所有表 的字段都顯示FROM TAB1 T1INNER JOIN TAB2 T2 ON (T1.C 1= T2.C2)LEFT JOIN TAB3 T3 ON ( T1.C1=T2.C3)-不管和哪個(gè)表組成連接條件，只 要表達(dá)式成立的記錄，才能和主表拼接并顯示。LEFT JOIN TAB4 T4 ON(T3.C2=T4.C3);比如有 tableA , tableB , tableA 和 tableB 是一對多 需求：根據(jù)tableB的某寫字段，查詢出tableA分析：如果此時(shí)

27、使用內(nèi)鏈接 則相應(yīng)的語句是：select a.* from tableA a ,tableB b where a.id =tableB.aid and b.code='4401'此時(shí)返回的結(jié)果是tableA在tableB有子數(shù)據(jù)的部分?jǐn)?shù)據(jù)，而如果表tableA 中的某條數(shù)據(jù)，在表tableB中不存在子數(shù)據(jù)，此時(shí)是查詢不出來！ 如果使用的是外連接，貝U相應(yīng)的語句如下：select a.* from tableA a left join tableB b on a.id=b.aid and b.code='4401'此時(shí)返回的結(jié)果是tableA中的全部數(shù)據(jù)，而且有重

28、復(fù)數(shù)出現(xiàn)，則應(yīng)該 用distinct過濾，語句如下：select disti net a.* from tableA a left joi n tableB b on a.id=b.aid and b.code='4401'內(nèi)聯(lián)接查詢可以連接兩個(gè)或兩個(gè)以上的表，參與內(nèi)聯(lián)接的表的地位是平等 的，而外聯(lián)接中參與聯(lián)接的表有主從之分以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù) 列，符合條件的數(shù)據(jù)將直接返回到結(jié)果集中，不符合的用NULL (空值)填充后再 返回到結(jié)果集中.介紹2內(nèi)連接的查詢結(jié)果都是滿足連接條件的元組。但有時(shí)我們也希望輸出那些不滿足連接條件的元組信息。比如，有學(xué)生表與選課表我們想知道每個(gè)學(xué)生的選課情況（每個(gè)學(xué)生有唯一的學(xué)號），包括已經(jīng)選課的 學(xué)生（這部分學(xué)生的學(xué)號在學(xué)生表中有，在選課表中也有，是滿足連接條件 的），也包括沒有選課的學(xué)生（這部分學(xué)生的學(xué)號在學(xué)生表中有，但在選課表 中沒有，不滿足連接條件），這時(shí)就需要使用外連接。外連接是只限制一張表 中的數(shù)據(jù)必須滿足連接條件，而另一張表中的數(shù)據(jù)可以不滿足連接條件的連接 方式。3種外連接：1）左外連接（LEFTOUTER JOIN）如