inner join,left join,right join,full join 的差別,用例子說明

[[email protected]] SQL>select * from a;

編号 姓名

---- ----------

1000 張三

2000 李四

3000 王五

[[email protected]] SQL>select * from b;

編号 商品

---- ----------

1000 電視機

2000 錄像機

4000 自行車

[[email protected]] SQL>set null 空值--這裡為了顯示方面我把NULL定義成了[空值]

[TE[email protected]] SQL>select a.*,b.* from a inner join b on a.編号=b.編号;

編号 姓名       編号 商品

---- ---------- ---- ----------

1000 張三       1000 電視機

2000 李四       2000 錄像機

[T[email protected]] SQL>select a.*,b.* from a left join b on a.編号=b.編号;

編号 姓名       編号 商品

---- ---------- ---- ----------

1000 張三       1000 電視機

2000 李四       2000 錄像機

3000 王五       空值 空值

[TE[email protected]] SQL>select a.*,b.* from a right join b on a.編号=b.編号;

編号 姓名       編号 商品

---- ---------- ---- ----------

1000 張三       1000 電視機

2000 李四       2000 錄像機

空值 空值       4000 自行車

[T[email protected]] SQL>select a.*,b.* from a full join b on a.編号=b.編号;

編号 姓名       編号 商品

---- ---------- ---- ----------

1000 張三       1000 電視機

2000 李四       2000 錄像機

3000 王五       空值 空值

空值 空值       4000 自行車

================================

為了從兩個或多個表中選出資料，一般使用表連接配接來實作這個功能。

   這裡先介紹join（連接配接）的概念. 為此我們準備了兩個試驗用表： album（專輯表） 和 track（曲目表）.

專輯表：包含200首來自Amazon的音樂CD的概要資訊。

album(asin, title, artist, price, release, label, rank)

曲目表：每張專輯中的曲目（因為是音樂CD，是以也可叫歌曲）的詳細資訊。

track(album, dsk, posn, song)

SQL短語 FROM album JOIN track ON album.asin=track.album 表示連接配接album和track表。

其中，album.asin表示專輯的惟一辨別号，track.album表示曲目表中和專輯關聯的專輯号。

連接配接後，得到一個臨時表，該臨時表中每條記錄包含的字段由兩部分組成，

除了專輯表中的對應字段album(title, artist ...)，還包含曲目表的所有字段track(album, disk, posn and song)。

有了這張臨時表，很多查詢就容易實作了。

看看一些具體的執行個體，

一、列出歌名為'Alison'的專輯名稱和作者

SELECT title, artist

FROM album JOIN track

ON (album.asin=track.album)

WHERE song = 'Alison'

顯然，歌名、專輯名稱和作者分别在兩個表中，必需使用表連接配接來完成這個查詢。

二、哪個artist錄制了歌曲'Exodus'

SELECT artist

FROM album JOIN track ON (asin=album)

WHERE song = 'Exodus'

用作連接配接的兩個字段asin，album因為在兩個表中都是惟一的，是以不一定要加表名作為字首。

但為了友善了解，建議使用字首，形如：album.asin=track.album

三、列出曲目表中所有屬于'Blur'專輯的歌曲

SELECT song

FROM album JOIN track ON (asin=album)

WHERE title = 'Blur'

如果我們把　album JOIN track ON (asin=album)　看成一個臨時表的話，join的概念就很好了解了。

以上我們介紹了表連接配接，更确切的說是inner joins內連接配接．

內連接配接僅選出兩張表中互相比對的記錄．是以，這會導緻有時我們需要的記錄沒有包含進來。

為更好的了解這個概念，我們介紹兩個表作示範。蘇格蘭議會中的政黨表(party)和議員表(msp)。

party(Code,Name,Leader)

Code: 政黨代碼

Name: 政黨名稱

Leader: 政黨領袖

msp(Name,Party,Constituency)

Name: 議員名

Party: 議員所在政黨代碼

Constituency: 選區

在介紹左連接配接、右連接配接和全連接配接前，有一個資料庫中重要的概念要介紹一下，即空值(NULL)。

有時表中，更确切的說是某些字段值，可能會出現空值, 這是因為這個資料不知道是什麼值或根本就不存在。空值不等同于字元串中的空格，也不是數字類型的0。是以，判斷某個字段值是否為空值時不能使用=,& lt;>這些判斷符。必需有專用的短語：IS NULL 來選出有空值字段的記錄，同理，可用 IS NOT NULL 選出不包含空值的記錄。

例如：下面的語句選出了沒有上司者的政黨。（不要奇怪，蘇格蘭議會中确實存在這樣的政黨）

SELECT code, name FROM party

WHERE leader IS NULL

　　又如：一個議員被開除出黨，看看他是誰。(即該議員的政黨為空值)

SELECT name FROM msp

WHERE party IS NULL

　　好了，讓我們言歸正傳，看看什麼叫左連接配接、右連接配接和全連接配接。

　　A left join（左連接配接）包含所有的左邊表中的記錄甚至是右邊表中沒有和它比對的記錄。

　　同理，也存在着相同道理的 right join（右連接配接），即包含所有的右邊表中的記錄甚至是左邊表中沒有和它比對的記錄。

而full join(全連接配接)顧名思義，左右表中所有記錄都會選出來。

　　講到這裡，有人可能要問，到底什麼叫：包含所有的左邊表中的記錄甚至是右邊表中沒有和它比對的記錄。

　　我們來看一個執行個體：

SELECT msp.name, party.name

FROM msp JOIN party ON party=code

　　這個是我們上一節所學的Join(注意：也叫inner join)，這個語句的本意是列出所有議員的名字和他所屬政黨。

　　很遺憾，我們發現該查詢的結果少了兩個議員：Canavan MSP, Dennis。為什麼，因為這兩個議員不屬于任何政黨，即他們的政黨字段(Party)為空值。那麼為什麼不屬于任何政黨就查不出來了？這是因為空值在作 怪。因為議員表中政黨字段(Party)的空值在政黨表中找不到對應的記錄作比對，即FROM msp JOIN party ON party=code　沒有把該記錄連接配接起來，而是過濾出去了。

　　在該短語中，msp在Join的左邊，所有稱為左表。party在Join的右邊，所有稱為右表。

　　現在再看看這句話，“包含所有的左邊表中的記錄甚至是右邊表中沒有和它比對的記錄”，意思應該很明白了吧。執行下面這個語句，那兩個沒有政黨的議員就漏不了了。

SELECT msp.name, party.name

FROM msp LEFT JOIN party ON party=code

　　關于右連接配接,看看這個查詢就明白了：

SELECT msp.name, party.name

FROM msp RIGHT JOIN party ON msp.party=party.code

　　這個查詢的結果列出所有的議員和政黨，包含沒有議員的政黨，但不包含沒有政黨的議員。 那麼既要包含沒有議員的政黨，又要包含沒有政黨的議員該怎麼辦呢，對了，全連接配接(full join)。

SELECT msp.name, party.name

FROM msp FULL JOIN party ON msp.party=party.code

===================

JOIN傳回”first_table”中所有的行盡管在” second_table”中沒有相比對的資料。

RIGHT JOIN傳回”second_table”中所有的行盡管在”first_table”中沒有相比對的資料。

INNER JOIN傳回的結果集是兩個表中所有相比對的資料。

沒聽明白？請繼續看下文分解。

2，分解：

還是用一個例子來的痛快些。。。

declare @a table(a int,b int)

declare @b table(a int,b int)

insert @a values(1,1)

insert @a values(2,2)

insert @b values(1,1)

insert @b values(3,3)

--左:

select * from @a Aa left join @b Bb on Aa.a=Bb.a

--右：

select * from @a Aa right join @b Bb on Aa.a=Bb.a

--内

select * from @a Aa join @b Bb on Aa.a=Bb.a

--外：

select * from @a Aa full join @b Bb on Aa.a=Bb.a

--完全

select * from @a,@b

-- cross join

select * from @a Aa cross join @b Bb

cross join 是笛卡兒乘積就是一張表的行數乘以另一張表的行數

left join 第一張表的連接配接列在第二張表中沒有比對是,第二張表中的值傳回null

right join 第二張表的連接配接列在第一張表中沒有比對是,第一張表中的值傳回null

full join 傳回兩張表中的行 left join+right join

inner join 隻傳回兩張表連接配接列的比對項

==========================

表A記錄如下：

aID        aNum

1           a20050111

2           a20050112

3           a20050113

4           a20050114

5           a20050115

表B記錄如下:

bID        bName

1            2006032401

2           2006032402

3           2006032403

4           2006032404

8           2006032408

實驗如下:

1.    left join

sql語句如下:

SELECT * FROM A

LEFT JOIN B

ON A.aID = B.bID

結果如下:

aID        aNum                   bID           bName

1            a20050111         1               2006032401

2            a20050112         2              2006032402

3            a20050113         3              2006032403

4            a20050114         4              2006032404

5            a20050115         NULL       NULL

（所影響的行數為 5 行）

結果說明:

        left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的.

換句話說,左表(A)的記錄将會全部表示出來,而右表(B)隻會顯示符合搜尋條件的記錄(例子中為: A.aID = B.bID).

B表記錄不足的地方均為NULL.

2.    right join

sql語句如下:

SELECT * FROM A

RIGHT JOIN B

ON A.aID = B.bID

結果如下:

aID        aNum                   bID           bName

1            a20050111         1               2006032401

2            a20050112         2              2006032402

3            a20050113         3              2006032403

4            a20050114         4              2006032404

NULL    NULL                8              2006032408

（所影響的行數為 5 行）

結果說明:

        仔細觀察一下,就會發現,和left join的結果剛好相反,這次是以右表(B)為基礎的,A表不足的地方用NULL填充.

3.inner join

sql語句如下:

SELECT * FROM A

INNERJOIN B

ON A.aID = B.bID

結果如下:

aID        aNum                   bID           bName

1            a20050111         1               2006032401

2            a20050112         2              2006032402

3            a20050113         3              2006032403

4            a20050114         4              2006032404

結果說明:

        很明顯,這裡隻顯示出了 A.aID = B.bID的記錄.這說明inner join并不以誰為基礎,它隻顯示符合條件的記錄.

    LEFT JOIN操作用于在任何的 FROM 子句中，組合來源表的記錄。使用 LEFT JOIN 運算來建立一個左邊外部聯接。左邊外部聯接将包含了從第一個（左邊）開始的兩個表中的全部記錄，即使在第二個（右邊）表中并沒有相符值的記錄。

文法：

FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

說明：

①　table1, table2參數用于指定要将記錄組合的表的名稱。

②　field1, field2參數指定被聯接的字段的名稱。且這些字段必須有相同的資料類型及包含相同類型的資料，但它們不需要有相同的名稱。

③　compopr參數指定關系比較運算符："="， "<"， ">"， "<="， ">=" 或 "<>"。

④　如果在INNER JOIN操作中要聯接包含Memo 資料類型或 OLE Object 資料類型資料的字段，将會發生錯誤。 LEFT JOIN 等同于LEFT OUTER JOIN,RIGHT JOIN 等同于RIGHT OUTER JOIN,FULL JOIN 等同于FULL OUTER JOIN

==============

JOIN: 如果表中有至少一個比對，則傳回行

LEFT JOIN: 即使右表中沒有比對，也從左表傳回所有的行

RIGHT JOIN: 即使左表中沒有比對，也從右表傳回所有的行

FULL JOIN: 隻要其中一個表中存在比對，就傳回行