深入理解SQL的四種連接左外連接、右外連接、內(nèi)連接、全連接

1、深入理解SQL的四種連接-左外連接、右外連接、內(nèi)連接、全連接本篇文章是對(duì)SQL中的四種連接-左外連接、右外連接、內(nèi)連接、全連接進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下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)識(shí)號(hào)相同的所有行。       2、外聯(lián)接。外聯(lián)接可以是左向外聯(lián)接、右向外

2、聯(lián)接或完整外部聯(lián)接。     在 FROM子句中指定外聯(lián)接時(shí)，可以由下列幾組關(guān)鍵字中的一組指定：     1）LEFT  JOIN或LEFT OUTER JOIN     左向外聯(lián)接的結(jié)果集包括  LEFT OUTER子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。      

3、 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ù)值。 &

4、#160; 3、交叉聯(lián)接   交叉聯(lián)接返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉聯(lián)接也稱作笛卡爾積。    FROM 子句中的表或視圖可通過內(nèi)聯(lián)接或完整外部聯(lián)接按任意順序指定；但是，用左或右向外聯(lián)接指定表或視圖時(shí)，表或視圖的順序很重要。有關(guān)使用左或右向外聯(lián)接排列表的更多信息，請(qǐng)參見使用外聯(lián)接。     例子：   -  a表     id   nam

5、e     b表     id   job   parent_id                 1   張3               

6、0;   1     23     1                 2   李四                 2   &#

7、160; 34     2                 3   王武                 3     34     4

8、60;        a.id同parent_id   存在關(guān)系   -     1） 內(nèi)連接     select   a.*,b.*   from   a   inner   join   b     on 

9、0; a.id=b.parent_id         結(jié)果是       1   張3                   1     23     1  &

10、#160;  2   李四                  2     34     2     2）左連接     select   a.*,b.*   from   a

11、60;  left   join   b     on   a.id=b.parent_id         結(jié)果是       1   張3               

12、0;   1     23     1     2   李四                  2     34     2     3 &

13、#160; 王武                  null    3） 右連接     select   a.*,b.*   from   a   right   join   b    

14、 on   a.id=b.parent_id         結(jié)果是       1   張3                   1     23     1

15、     2   李四                  2     34     2     null            &

16、#160;          3     34     4    4） 完全連接     select   a.*,b.*   from   a   full   join   b    

17、on   a.id=b.parent_id     結(jié)果是       1   張3                  1     23     1     2 

18、;  李四                 2     34     2     null                 3   

19、;  34     4     3   王武                 null-一、交叉連接（CROSS JOIN）交叉連接（CROSS JOIN）：有兩種，顯式的和隱式的，不帶ON子句，返回的是兩表的乘積，也叫笛卡爾積。例如：下面的語句1和語句2的結(jié)果是相同的。語句1：隱式的交叉連接，沒有CROSS JOIN。SELE

20、CT O.ID, O.ORDER_NUMBER, C.ID, C.NAMEFROM ORDERS O , CUSTOMERS CWHERE O.ID=1;語句2：顯式的交叉連接，使用CROSS JOIN。SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM ORDERS O CROSS JOIN CUSTOMERS CWHERE O.ID=1;語句1和語句2的結(jié)果是相同的，查詢結(jié)果如下：二、內(nèi)連接（INNER JOIN）內(nèi)連接（INNER JOIN）：有兩種，顯式的和隱式的，返回連接表中符合連接條件和查詢條件的數(shù)據(jù)行。（所謂的鏈接表就是數(shù)據(jù)庫(kù)在做查詢形成的中間表

21、）。例如：下面的語句3和語句4的結(jié)果是相同的。語句3：隱式的內(nèi)連接，沒有INNER JOIN，形成的中間表為兩個(gè)表的笛卡爾積。SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C,ORDERS OWHERE C.ID=O.CUSTOMER_ID;語句4：顯示的內(nèi)連接，一般稱為內(nèi)連接，有INNER JOIN，形成的中間表為兩個(gè)表經(jīng)過ON條件過濾后的笛卡爾積。SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAMEFROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER

22、_ID;語句3和語句4的查詢結(jié)果：三、外連接（OUTER JOIN）：外連不但返回符合連接和查詢條件的數(shù)據(jù)行，還返回不符合條件的一些行。外連接分三類：左外連接（LEFT OUTER JOIN）、右外連接（RIGHT OUTER JOIN）和全外連接（FULL OUTER JOIN）。三者的共同點(diǎn)是都返回符合連接條件和查詢條件（即：內(nèi)連接）的數(shù)據(jù)行。不同點(diǎn)如下：左外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。右外連接還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接還返回左表中不符合連接條件單符合查詢條件的數(shù)據(jù)行，并且還返回右表中不符合連接條件單符合查詢條件的數(shù)據(jù)行。全外連接實(shí)

23、際是上左外連接和右外連接的數(shù)學(xué)合集（去掉重復(fù)），即“全外=左外 UNION 右外”。說明：左表就是在“（LEFT OUTER JOIN）”關(guān)鍵字左邊的表。右表當(dāng)然就是右邊的了。在三種類型的外連接中，OUTER 關(guān)鍵字是可省略的。下面舉例說明：語句5：左外連接（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;語句6：右外連接（RIGHT OUTER JOIN）SELECT O.ID,

24、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é)果是不一樣的。例如：語句7：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_NUMBER<>'MIK

25、E_ORDER001'語句8：將語句7中的WHERE條件放到ON后面。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 AND O.ORDER_NUMBER<>'MIKE_ORDER001'從語句7和語句8查詢的結(jié)果來看，顯然是不相同的，語句8顯示的結(jié)果是難以理解的。因此，推薦在寫連接查詢的時(shí)候，ON后面只跟連接條件，而對(duì)中間表限制的條件都寫到WHERE子句中。語句9：全外連接（

26、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。但是可以通過左外和右外求合集來獲取全外連接的查詢結(jié)果。下圖是上面SQL在Oracle下執(zhí)行的結(jié)果：語句10：左外和右外的合集，實(shí)際上查詢結(jié)果和語句9是相同的。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.N

27、AMEFROM 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;語句9和語句10的查詢結(jié)果是相同的，如下：四、聯(lián)合連接（UNION JOIN）：這是一種很少見的連接方式。Oracle、MySQL均不支持，其作用是：找出全外連接和內(nèi)連接之間差異的所有行。這在數(shù)據(jù)分析中排錯(cuò)中比較常用。也可以

28、利用數(shù)據(jù)庫(kù)的集合操作來實(shí)現(xiàn)此功能。語句11：聯(lián)合查詢（UNION JOIN）例句，還沒有找到能執(zhí)行的SQL環(huán)境。SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID語句12：語句11在DB2下的等價(jià)實(shí)現(xiàn)。還不知道DB2是否支持語句11呢！SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON

29、C.ID=O.CUSTOMER_IDEXCEPTSELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;語句13：語句11在Oracle下的等價(jià)實(shí)現(xiàn)。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_IDMINUSSELECT O.ID,O.ORDER_NU

30、MBER,O.CUSTOMER_ID,C.ID,C.NAMEFROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;查詢結(jié)果如下：五、自然連接（NATURAL INNER JOIN）：說真的，這種連接查詢沒有存在的價(jià)值，既然是SQL2標(biāo)準(zhǔn)中定義的，就給出個(gè)例子看看吧。自然連接無需指定連接列，SQL會(huì)檢查兩個(gè)表中是否相同名稱的列，且假設(shè)他們?cè)谶B接條件中使用，并且在連接條件中僅包含一個(gè)連接列。不允許使用ON語句，不允許指定顯示列，顯示列只能用*表示（ORACLE環(huán)境下測(cè)試的）。對(duì)于每種連接類型（除了交叉連接外），均可指定NATURAL。下面給出幾個(gè)例子。語句14：SELECT *FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;語句15：SELECT *FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;語句16：SELECT *FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;語句17：SELECT *FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;六、SQL查詢的基本原理：兩種情況介紹。第一、單表查詢：根據(jù)WHERE