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

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

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

3、m a inner join b on a.id=b.parent_id結(jié)果是1312312 四 23422）左連接select a.*,b.* from a left join b on a.id=b.parent_id結(jié)果是1312312 四 23423 王武 null3） 右連接select a.*,b.* from a right join b on a.id=b.parent_id結(jié)果是1312312 四 2342 null 3 34 44） 完全連接 ( 不做過多概述)select a.*,b.* from a full join b on a.id=b.parent_id結(jié)果是1

4、312312四 2342null33443王武 null2.連接（ INNER JOIN）連接（ INNER JOIN）：有兩種，顯式的和隱式的，返回連接表中符合連接條件和查詢條件的數(shù)據(jù)行。（所謂的表就是數(shù)據(jù)庫在做查詢形成的中間表）。例如：下面的語句3 和語句 4 的結(jié)果是相同的。語句 1：隱式的連接，沒有INNER JOIN，形成的中間表為兩個表的笛卡爾積。SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C,ORDERS OWHERE C.ID=O.CUSTOMER_ID;語句 2：顯示的連接，一般稱為連接，有INNER JOIN，形

5、成的中間表為兩個表經(jīng)過ON條件過濾后的笛卡爾積。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ù)行，還返回不符合條件的一些行。外連接分三類：左外連接（ LEFT OUTER JOIN）、右外連接（ RIGHT OUTER JOIN）和全外連接（ FULL OUTERJOIN）。三者的共同點是都返回符合連接條件和查詢條件（即：連接）的數(shù)據(jù)行。不同點如下：左外連接還返回左表中不符合

6、連接條件單符合查詢條件的數(shù)據(jù)行。右外連接還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行，并且還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接實際是上左外連接和右外連接的數(shù)學(xué)合集（去掉重復(fù)），即“全外=左外 UNION 右外”。說明：左表就是在“（LEFT OUTER JOIN）”關(guān)鍵字左邊的表。右表當然就是右邊的了。在三種類型的外連接中，OUTER關(guān)鍵字是可省略的。下面舉例說明：語句 3：左外連接（LEFT OUTER JOIN）SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.

7、NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;語句 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后面查詢的結(jié)果是不一樣的。例如：語句 5： WHERE條件獨立。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,

8、C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_IDWHERE O.ORDER_NUMBER<>'MIKE_ORDER001'語句 6：將語句5 中的 WHERE條件放到ON后面。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O LEFT OUTER JOIN CUSTOMERSONCC.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE

9、_ORDER001'從語句5 和語句6 查詢的結(jié)果來看， 顯然是不相同的，語句6 顯示的結(jié)果是難以理解的。因此，推薦在寫連接查詢的時候，ON后面只跟連接條件，而對中間表限制的條件都寫到WHERE 子句中。語句 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是不支持全外的連接的，這里給出的寫法適合Oracle 和 DB2。但是可以通過左外和

10、右外求合集來獲取全外連接的查詢結(jié)果。語句 8：左外和右外的合集，實際上查詢結(jié)果和語句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.CUSTOMER_ID;4.SQL 查詢的基本原理：

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

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

13、誤或者性能低下。下面總結(jié)一下兩表連接查詢選擇方式的依據(jù)：1、 查兩表關(guān)聯(lián)列相等的數(shù)據(jù)用連接。2、 Col_L是 Col_R 的子集時用右外連接。3、 Col_R 是 Col_L 的子集時用左外連接。4、 Col_R 和 Col_L 彼此有交集但彼此互不為子集時候用全外。多個表查詢的時候，這些不同的連接類型可以寫到一塊。例如：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=T

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

15、： + 表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在右表，左表就是全部顯示，故是左連接。SQL> Select * from dave a,bl b where a.id=b.id(+); - 注意： 用（ +） 就要用關(guān)鍵字 where右連接用（+）來實現(xiàn)， 這個 +號可以這樣來理解： + 表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在左表，右表就是全部顯示，故是右連接。SQL> Select * from dave a,bl b where a.id(+)=b.id;6.On 與 where 的區(qū)別數(shù)據(jù)庫在通過連接兩或多表來返回記錄時， 都會生成一中間的臨

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

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

18、tab1.size tab2.size 1 10 10 AAA第二條 SQL 的過程：1、中間表on 條件 :tab1.size = tab2.size and = AAA(條件不為真也會返回左表中的記錄) tab1.id tab1.size tab2.size 1 10 10 AAA2 20 (null) (null)3 30 (null) (null)其實以上結(jié)果的關(guān)鍵原因就是left join,right join,full join的特殊性，不管 on為真都會返回left 或 right 表中的記錄， full 則具有 left 和

19、 right 的特性的并集。沒這個特殊性，則條件放在on 中和 where 中，返回的結(jié)果集是相同的。上的條件是否而 inner jion心得：外匹配，連接過濾，外即匹配又過濾用on 和 where 搭配7.圖解 SQL 的連接與外連接外連接就相當于多個業(yè)務(wù)表的一次合體，連接的條件必須是關(guān)鍵性的！ 連接成功就可看成一表 !在 oracle 的 SQL 語句常用的連接有連接（ inner join），外連接（ outer join）等，連接又包括等值連接，非等值連接，自連接；而外連接又分為左連接和右連接。其中默認的是連接的等值連接。為了方便我們創(chuàng)建兩最簡易的表 A 、B，具體的表結(jié)構(gòu)參看下面，來

20、分析連接與外連接的區(qū)別一對多關(guān)系 ,A 一 (主表 ),B 多(子表 ),主表的一條記錄對應(yīng)子表的多條記錄圖1圖2兩個表要做連接，必須要有連接字段，而在表Bnamid,下圖說明了連接之間關(guān)系圖3A 和表B 中連接字段是Aid和圖 3（ 1) 連接：利用連接（等值）就可獲取藍色的公共部分 C，即圖 3 中的數(shù)據(jù)集 C，結(jié)果集為如下：只顯示主表與子表有關(guān)聯(lián)的記錄 ,一個主表記錄可能有多個子表記錄 (即在生成的中間表中 ,主表數(shù)據(jù)一般重復(fù)出現(xiàn) ,所以查詢出來的記錄數(shù)根據(jù)每個主表記錄有多少個子表記錄而定 ,下同 )圖 4其實 select * from A join B on A.Aid=B.Bnam

21、id; 等價于 select * from A,B where A.Aid=B.Bnamid;注：非等值連接主要的話是針對一個圍來查詢數(shù)據(jù)， 自連接主要就是把 1 表堪稱兩表來用(2)外連接 ：分為左外連接（ left join ）與右外連接（ right join ）左外連接即公共顯示的藍色部分 C1+顯示黃色的記錄集 A1 ，顯示語句等價于 select * from A,B where A.Aid=B.Bnamid （+）；不但顯示主表與子表有關(guān)聯(lián)的記錄,還顯示主表與子表沒有關(guān)聯(lián)的主表記錄圖 5右外連接即公共顯示的藍色部分 C1+顯示綠色的 B1，顯示語句等價于 select * fro

22、m A,B where A.Aid(+)=B.Bnamid ；顯示子表在主表有關(guān)聯(lián)的記錄,還顯示子表與主表沒有關(guān)聯(lián)的子表記錄圖6表 A 和表 B 情況是相對的，以上實驗都是A 在左邊的情況，其實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ū)分外連接的關(guān)鍵 ,數(shù)據(jù)的展示以關(guān)聯(lián)的一方為準 ,被關(guān)聯(lián)的可能重復(fù)被關(guān)聯(lián)以哪個表為準 ,就顯示這個表的全部信息,拋去另一個表獨有的部分這是僅僅的技術(shù)層面 ,至于要以哪個表為準 ,要看業(yè)務(wù)邏輯的需要 ,哪個表的獨有部分

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

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

25、的語句是：select a.* from tableA a ,tableB bwhere a.id =tableB.aid and b.code='4401'此時返回的結(jié)果是tableA 在 tableB 有子數(shù)據(jù)的部分數(shù)據(jù)，而如果表據(jù)，在表tableB 中不存在子數(shù)據(jù)，此時是查詢不出來！tableA中的某條數(shù)如果使用的是外連接，則相應(yīng)的語句如下：select a.* from tableA aleft join tableB b on a.id=b.aid and b.code='4401'此時返回的結(jié)果是tableA中的全部數(shù)據(jù)，而且有重復(fù)數(shù)出現(xiàn)，則應(yīng)該用d

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