大漠駝鈴

sql中l(wèi)eft join、right join的簡單說明

數(shù)據(jù)庫常見的join方式有三種：inner join, left outter join, right outter join(還有一種full join，因不常用，本文不討論)。這三種連接方式都是將兩個(gè)以上的表通過on條件語句，拼成一個(gè)大表。以下是它們的共同點(diǎn)：

1. 關(guān)于左右表的概念。左表指的是在SQL語句中排在left join左邊的表，右表指的是排在left join右邊的表。
2. 在拼成的大表中，左表排在左邊，右表排在右邊。
3. on條件語句最好用=號(hào)對(duì)兩表相應(yīng)的主外鍵進(jìn)行連接。當(dāng)然，也可以用其他操作符，如>, <, 來連接兩表的任一字段，此時(shí)的關(guān)系將非常復(fù)雜，連接后的記錄數(shù)也隨之而變得不確定。如果在一些特殊的場合中需要用到這種方式，必須通過簡單的實(shí)例加以確 認(rèn)，否則，連接結(jié)果很可能不是我們所想要的！
4. on條件語句不能省略。
5. 可以連鎖使用join，每次使用join都令另一表與當(dāng)前的表或連接的結(jié)果相連接。

在下文中，用到了兩個(gè)表，"部門"表與"組織"表，其中，"部門"表有一名為"組織編號(hào)"的外鍵，指向"組織"表中的主鍵"編號(hào)"。

inner join

格式：select * from 部門 inner join 組織 on 部門.組織編號(hào) = 組織.編號(hào)

目的：將兩表中符合on條件的所有記錄都找出來。

規(guī)律：

1. 拼出的大表記錄不會(huì)增加。
2. 如果左邊與右表的關(guān)系是一對(duì)多的關(guān)系，在選出的任一記錄中，假若右表有多個(gè)記錄與其對(duì)應(yīng)，那么，連接后的左表，主鍵將不再唯一。

典型應(yīng)用：將存在多關(guān)系的引用表放在左表，將存在一關(guān)系的被引用表放在右表，通過=號(hào)將主外鍵進(jìn)行連接，通過對(duì)右表設(shè)定過濾條件，選出相應(yīng)的且主鍵唯一的左表記錄。

備注：inner join 是默認(rèn)的連接方式，可縮寫為join。

轉(zhuǎn)化為where子句：

select * from 部門, 組織 where 部門.組織編號(hào) = 組織.編號(hào)

left outter join

格式: select * from 部門 left join 組織 on 部門.組織編號(hào) = 組織.編號(hào)

格式: select * from 組織 left join 部門 on 組織.編號(hào) = 部門.組織編號(hào)

目的：將左表的所有記錄列出，右表中只要符合on條件的，與左表記錄相拼合，不符合條件的，填以null值。

規(guī)律：

1. 選出所有符合條件的左表，如果左邊與右表的關(guān)系是一對(duì)一的關(guān)系，則拼成的大表記錄不會(huì)改變。
如果左邊與右表的關(guān)系是多對(duì)一的關(guān)系，則拼成的大表記錄也不會(huì)改變。
如果左邊與右表的關(guān)系是一對(duì)多的關(guān)系，則拼成的大表記錄會(huì)增加。對(duì)于每一具有一對(duì)多關(guān)系的左表記錄，如果左表1：N與右表對(duì)應(yīng)，那么會(huì)多出N-1條記錄。 例如，如果左表第一條記錄1：3對(duì)應(yīng)于右表，多出2條記錄。如果左表第二條記錄1：2對(duì)應(yīng)于右表，則再多出1條記錄。這樣，總共多出3條記錄。其他類推。
2. 如果左邊與右表的關(guān)系是一對(duì)多的關(guān)系，在選出的任一記錄中，假若右表有多個(gè)記錄與其對(duì)應(yīng)，那么，連接后的左表，主鍵將不再唯一。
3. 如果左邊與右表的關(guān)系是一對(duì)多的關(guān)系，對(duì)于左表任一記錄，如果右表沒有記錄與其相對(duì)應(yīng)，則全部填以null值。

典型應(yīng)用：將存在多關(guān)系的引用表放在左表，將存在一關(guān)系的被引用表放在右表，通過對(duì)右表設(shè)定過濾條件，選出相應(yīng)的且主鍵唯一的左表記錄。

備注：left outter join可用left join代替。在有些數(shù)據(jù)庫中，如HSqlDb, 只能使用left join而不能使用left outter join。

轉(zhuǎn)化為where子句：

select * from 部門, 組織 where 部門.組織編號(hào) = 組織.編號(hào)

right outter join

格式: select * from 部門 right join 組織 on 部門.組織編號(hào) = 組織.編號(hào)

格式: select * from 組織 right join 部門 on 部門.組織編號(hào) = 組織.編號(hào)

目的：將右表的所有記錄列出，左表中只要符合on條件的，與右表記錄相拼合，不符合條件的，填以null值。

規(guī)律：(與left outter join相反)

典型應(yīng)用：可轉(zhuǎn)化成left outter join。例如

select * from 組織 right join 部門 on 部門.組織編號(hào) = 組織.編號(hào)
與
select * from 部門 left join 組織 on 部門.組織編號(hào) = 組織.編號(hào)
的效果一樣

假設(shè)有A，B兩個(gè)表。 

表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 

結(jié)果如下: 
aID　　　　　aNum　　　　　bID　　　　　bName 
1　　　　　a20050111　　　　1　　　　　2006032401 
2　　　　　a20050112　　　　2　　　　　2006032402 
3　　　　　a20050113　　　　3　　　　　2006032403 
4　　　　　a20050114　　　　4　　　　　2006032404 
5　　　　　a20050115　　　　NULL　　　　　NULL 

（所影響的行數(shù)為 5 行） 
結(jié)果說明: 
left join是以A表的記錄為基礎(chǔ)的,A可以看成左表,B可以看成右表,left join是以左表為準(zhǔn)的. 
換句話說,左表(A)的記錄將會(huì)全部表示出來,而右表(B)只會(huì)顯示符合搜索條件的記錄(例子中為: A.aID = B.bID). 
B表記錄不足的地方均為NULL. 
-------------------------------------------- 
2.right join 
sql語句如下:  
select * from A 
right join B  
on A.aID = B.bID 

結(jié)果如下: 
aID　　　　　aNum　　　　　bID　　　　　bName 
1　　　　　a20050111　　　　1　　　　　2006032401 
2　　　　　a20050112　　　　2　　　　　2006032402 
3　　　　　a20050113　　　　3　　　　　2006032403 
4　　　　　a20050114　　　　4　　　　　2006032404 
NULL　　　　　NULL　　　　　8　　　　　2006032408 

（所影響的行數(shù)為 5 行） 
結(jié)果說明: 
仔細(xì)觀察一下,就會(huì)發(fā)現(xiàn),和left join的結(jié)果剛好相反,這次是以右表(B)為基礎(chǔ)的,A表不足的地方用NULL填充. 
-------------------------------------------- 
3.inner join 
sql語句如下:  
select * from A 
innerjoin B  
on A.aID = B.bID 

結(jié)果如下: 
aID　　　　　aNum　　　　　bID　　　　　bName 
1　　　　　a20050111　　　　1　　　　　2006032401 
2　　　　　a20050112　　　　2　　　　　2006032402 
3　　　　　a20050113　　　　3　　　　　2006032403 
4　　　　　a20050114　　　　4　　　　　2006032404 

結(jié)果說明: 
很明顯,這里只顯示出了 A.aID = B.bID的記錄.這說明inner join并不以誰為基礎(chǔ),它只顯示符合條件的記錄. 
-------------------------------------------- 
PS:  
LEFT JOIN操作用于在任何的 FROM 子句中，組合來源表的記錄。使用 LEFT JOIN 運(yùn)算來創(chuàng)建一個(gè)左邊外部聯(lián)接。左邊外部聯(lián)接將包含了從第一個(gè)（左邊）開始的兩個(gè)表中的全部記錄，即使在第二個(gè)（右邊）表中并沒有相符值的記錄。  

語法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2  

說明：table1, table2參數(shù)用于指定要將記錄組合的表的名稱。 
field1, field2參數(shù)指定被聯(lián)接的字段的名稱。且這些字段必須有相同的數(shù)據(jù)類型及包含相同類型的數(shù)據(jù)，但它們不需要有相同的名稱。 
compopr參數(shù)指定關(guān)系比較運(yùn)算符："="， "<"， ">"， "<="， ">=" 或 "<>"。 
如果在INNER JOIN操作中要聯(lián)接包含Memo 數(shù)據(jù)類型或 OLE Object 數(shù)據(jù)類型數(shù)據(jù)的字段，將會(huì)發(fā)生錯(cuò)誤  

注：left和right是外連接，Inner是內(nèi)連接。