SQL內(nèi)連接與外連接

1、.1. 概述1、內(nèi)聯(lián)接（典型的聯(lián)接運(yùn)算，使用像 = 或 之類(lèi)的比較運(yùn)算符）。包括相等聯(lián)接和自然聯(lián)接。 內(nèi)聯(lián)接使用比較運(yùn)算符根據(jù)每個(gè)表共有的列的值匹配兩個(gè)表中的行。例如，檢索 students和courses表中學(xué)生標(biāo)識(shí)號(hà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 OUTER子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒(méi)有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均

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

3、select a.*,b.* from a inner join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四 2 34 2 2）左連接 select a.*,b.* from a left join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四 2 34 2 3 王武 null 3） 右連接 select a.*,b.* from a right join b on a.id=b.parent_id 結(jié)果是 1 張3 1 23 1 2 李四 2 34 2 null 3 34 4 4） 完全連接 (不做過(guò)多概述)s

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

5、TOMERS C,ORDERS OWHERE C.ID=O.CUSTOMER_ID;語(yǔ)句2：顯示的內(nèi)連接，一般稱為內(nèi)連接，有INNER JOIN，形成的中間表為兩個(gè)表經(jīng)過(guò)ON條件過(guò)濾后的笛卡爾積。SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;3.外連接（OUTER JOIN）：外連不但返回符合連接和查詢條件的數(shù)據(jù)行，還返回不符合條件的一些行。外連接分三類(lèi)：左外連接（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 右外”。說(shuō)明：左表就是在“（LEFT OUTER JOIN）”關(guān)鍵字左邊的表。右表當(dāng)然就是右邊的了。在三種類(lèi)型的外連接中，OUTER 關(guān)鍵字是可省略的。下面舉例說(shuō)明：語(yǔ)

7、句3：左外連接（LEFT OUTER JOIN）SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;語(yǔ)句4：右外連接（RIGHT OUTER JOIN）SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;注意：WHERE條件放在ON后面查

8、詢的結(jié)果是不一樣的。例如：語(yǔ)句5：WHERE條件獨(dú)立。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_IDWHERE O.ORDER_NUMBERMIKE_ORDER001;語(yǔ)句6：將語(yǔ)句5中的WHERE條件放到ON后面。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.I

9、D=O.CUSTOMER_ID AND O.ORDER_NUMBERMIKE_ORDER001;從語(yǔ)句5和語(yǔ)句6查詢的結(jié)果來(lái)看，顯然是不相同的，語(yǔ)句6顯示的結(jié)果是難以理解的。因此，推薦在寫(xiě)連接查詢的時(shí)候，ON后面只跟連接條件，而對(duì)中間表限制的條件都寫(xiě)到WHERE子句中。語(yǔ)句7：全外連接（FULL OUTER JOIN）。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;注意：MySQL是不支持全外的連接的，這里給出

10、的寫(xiě)法適合Oracle和DB2。但是可以通過(guò)左外和右外求合集來(lái)獲取全外連接的查詢結(jié)果。語(yǔ)句8：左外和右外的合集，實(shí)際上查詢結(jié)果和語(yǔ)句7是相同的。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_IDUNIONSELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUS

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

12、返回查詢結(jié)果。理解SQL查詢的過(guò)程是進(jìn)行SQL優(yōu)化的理論依據(jù)。ON后面的條件（ON條件）和WHERE條件的區(qū)別：ON條件：是過(guò)濾兩個(gè)鏈接表笛卡爾積形成中間表的約束條件。WHERE條件：在有ON條件的SELECT語(yǔ)句中是過(guò)濾中間表的約束條件。在沒(méi)有ON的單表查詢中，是限制物理表或者中間查詢結(jié)果返回記錄的約束。在兩表或多表連接中是限制連接形成最終中間表的返回結(jié)果的約束。從這里可以看出，將WHERE條件移入ON后面是不恰當(dāng)?shù)?。推薦的做法是：ON只進(jìn)行連接操作，WHERE只過(guò)濾中間表的記錄?？偨Y(jié)連接查詢是SQL查詢的核心，連接查詢的連接類(lèi)型選擇依據(jù)實(shí)際需求。如果選擇不當(dāng)，非但不能提高查詢效率，反而會(huì)帶

13、來(lái)一些邏輯錯(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í)候，這些不同的連接類(lèi)型可以寫(xiě)到一塊。例如：SELECT T1.C1,T2.CX,T3.CYFROM TAB1 T1INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)LEFT OUTER JOIN TAB4 ON(T2.C2=T3

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

15、個(gè)表有加號(hào)，這個(gè)表就是匹配表。所以加號(hào)寫(xiě)在右表，左表就是全部顯示，故是左連接。SQL Select * from dave a,bl b where a.id=b.id(+); - 注意： 用（+） 就要用關(guān)鍵字where右連接用（+）來(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;6.On與where的區(qū)別數(shù)據(jù)庫(kù)在通過(guò)連接兩張或多張表來(lái)返回記錄時(shí)，都會(huì)生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回給用戶。 在使用l

16、eft jion時(shí)，on和where條件的區(qū)別如下：1、 on條件是在生成臨時(shí)表時(shí)使用的條件，它不管on中的條件是否為真，都會(huì)返回左邊表中的記錄。2、where條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。這時(shí)已經(jīng)沒(méi)有l(wèi)eft join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過(guò)濾掉。 假設(shè)有兩張表：表1：tab2 id size1 102 203 30表2：tab2 size name10 AAA20 BBB20 CCC兩條SQL:1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.

17、name=AAA2、select * form tab1 left join tab2 on (tab1.size = tab2.size and =AAA)第一條SQL的過(guò)程：1、中間表on條件:tab1.size = tab2.size tab1.id tab1.size tab2.size 1 10 10 AAA2 20 20 BBB2 20 20 CCC3 30 (null) (null)| |2、再對(duì)中間表過(guò)濾where 條件：=AAAtab1.id tab1.size tab2.size 1 10 10 AAA

18、第二條SQL的過(guò)程：1、中間表on條件:tab1.size = tab2.size and =AAA(條件不為真也會(huì)返回左表中的記錄) tab1.id tab1.size tab2.size 1 10 10 AAA2 20 (null) (null)3 30 (null) (null) 其實(shí)以上結(jié)果的關(guān)鍵原因就是left join,right join,full join的特殊性，不管on上的條件是否為真都會(huì)返回left或right表中的記錄，full則具有l(wèi)eft和right的特性的并集。 而inner jion沒(méi)這個(gè)特殊性，則條件放在on中和where中

19、，返回的結(jié)果集是相同的。心得：外鏈接匹配，內(nèi)連接過(guò)濾，外鏈接即匹配又過(guò)濾用on和where 搭配7. 圖解SQL的內(nèi)連接與外連接 內(nèi)外連接就相當(dāng)于多個(gè)業(yè)務(wù)表的一次合體，連接的條件必須是關(guān)鍵性的！ 連接成功就可看成一張表!在oracle的SQL語(yǔ)句常用的連接有內(nèi)連接（inner join），外連接（outer join）等，內(nèi)連接又包括等值連接，非等值連接，自連接；而外連接又分為左連接和右連接。其中默認(rèn)的是內(nèi)連接的等值連接。 為了方便我們創(chuàng)建兩張最簡(jiǎn)易的表A、B，具體的表結(jié)構(gòu)參看下面，來(lái)分析內(nèi)連接與外連接的區(qū)別一對(duì)多關(guān)系,A一(主表),B多(子表),主表的一條記錄對(duì)應(yīng)子表的多條記錄 圖1 圖2兩

20、個(gè)表要做連接，必須要有連接字段，而在表A和表B中連接字段是Aid和Bnamid,下圖說(shuō)明了連接之間關(guān)系圖3圖3（1) 內(nèi)連接：利用內(nèi)連接（等值）就可獲取藍(lán)色的公共部分C，即圖3中的數(shù)據(jù)集C，結(jié)果集為如下：只顯示主表與子表有關(guān)聯(lián)的記錄,一個(gè)主表記錄可能有多個(gè)子表記錄(即在生成的中間表中,主表數(shù)據(jù)一般重復(fù)出現(xiàn),所以查詢出來(lái)的記錄數(shù)根據(jù)每個(gè)主表記錄有多少個(gè)子表記錄而定,下同)圖4其實(shí)select * from A join B on A.Aid=B.Bnamid;等價(jià)于select * from A,B where A.Aid=B.Bnamid;注：非等值連接主要的話是針對(duì)一個(gè)范圍來(lái)查詢數(shù)據(jù)，自連接

21、主要就是把1張表堪稱兩張表來(lái)用 (2)外連接：分為左外連接（left join）與右外連接（right join）左外連接即公共顯示的藍(lán)色部分C1+顯示黃色的記錄集A1，顯示語(yǔ)句等價(jià)于select * from A,B where A.Aid=B.Bnamid（+）； 不但顯示主表與子表有關(guān)聯(lián)的記錄,還顯示主表與子表沒(méi)有關(guān)聯(lián)的主表記錄圖 5右外連接即公共顯示的藍(lán)色部分C1+顯示綠色的B1，顯示語(yǔ)句等價(jià)于select * from A,B where A.Aid(+)=B.Bnamid；顯示子表在主表有關(guān)聯(lián)的記錄,還顯示子表與主表沒(méi)有關(guān)聯(lián)的子表記錄圖6表A和表B情況是相對(duì)的，以上實(shí)驗(yàn)都是A在左邊

22、的情況，其實(shí)A left join B與B right join A的情況的結(jié)果集是一樣的。自己的總結(jié):兩表之間,只要有關(guān)聯(lián),不管以什么關(guān)系存在,都可能有對(duì)方關(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ú)有部分是我們更想要的,這時(shí)候,表之間的對(duì)應(yīng)關(guān)系才更重要點(diǎn),這是業(yè)務(wù)問(wèn)題利用關(guān)鍵性的數(shù)據(jù)庫(kù)字段連接,才能更好地關(guān)聯(lián)8.SQL何時(shí)使用內(nèi)外連接所謂內(nèi)外連接，就是表與表有條件的拼接，內(nèi)連接是連接條件所列字段,（比如a.

23、recorder = b.userid中的recorder與userid）有值且滿足表達(dá)式的記錄才拼接成功并顯示,外鏈接的左外鏈接，連接條件是給右表說(shuō)的，連接表達(dá)式成立的記錄與左表拼接，不成立的行不顯示，不管拼接表達(dá)式是否成立，左表照常顯示，連接條件不受限制，并不是必須有主表，也可以是和主表連接的其他表組成連接條件需要查找兩張表同時(shí)存在的數(shù)據(jù)，使用內(nèi)連接，需要查找兩張表中一張表存在，另一張表不存在的時(shí)候使用外連接。SELECT T1.C1,T2.CX,T3.* -如果沒(méi)有要求特別顯示哪個(gè)字段，則所有表的字段都顯示FROM TAB1 T1INNER JOIN TAB2 T2 ON (T1.C1=

24、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是一對(duì)多需求：根據(jù)tableB的某寫(xiě)字段，查詢出tableA 分析：如果此時(shí)使用內(nèi)鏈接 則相應(yīng)的語(yǔ)句是：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ù)，而如果

25、表tableA中的某條數(shù)據(jù)，在表tableB中不存在子數(shù)據(jù)，此時(shí)是查詢不出來(lái)！如果使用的是外連接，則相應(yīng)的語(yǔ)句如下：select a.* from tableA a left join tableB b on a.id=b.aid and b.code=4401; 此時(shí)返回的結(jié)果是tableA中的全部數(shù)據(jù)，而且有重復(fù)數(shù)出現(xiàn)，則應(yīng)該用distinct過(guò)濾，語(yǔ)句如下：select distinct a.* from tableA a left join tableB b on a.id=b.aid and b.code=4401; 內(nèi)聯(lián)接查詢可以連接兩個(gè)或兩個(gè)以上的表，參與內(nèi)聯(lián)接的表的地位是平等的

26、，而外聯(lián)接中參與聯(lián)接的表有主從之分.以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù)列,符合條件的數(shù)據(jù)將直接返回到結(jié)果集中,不符合的用NULL（空值）填充后再返回到結(jié)果集中. 介紹2 內(nèi)連接的查詢結(jié)果都是滿足連接條件的元組。但有時(shí)我們也希望輸出那些不滿足連接條件的元組信息。比如，有學(xué)生表與選課表我們想知道每個(gè)學(xué)生的選課情況（每個(gè)學(xué)生有唯一的學(xué)號(hào)），包括已經(jīng)選課的學(xué)生（這部分學(xué)生的學(xué)號(hào)在學(xué)生表中有，在選課表中也有，是滿足連接條件的），也包括沒(méi)有選課的學(xué)生（這部分學(xué)生的學(xué)號(hào)在學(xué)生表中有，但在選課表中沒(méi)有，不滿足連接條件），這時(shí)就需要使用外連接。外連接是只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中的數(shù)據(jù)可以不滿足連接條件的連接方式。3種外連接： 1)左外連接（LEFTOUTER JOIN）如果在連接查詢中，連接管子左端的表中所有的元組都列出來(lái)，并且能在右端的表中找到匹配的元組，那么連接成功。如果在右端的表中，沒(méi)能找到匹配