sql語句中join的用法和效率說明

1、數(shù)據(jù)庫 join 語句使用及效率說明在我們開發(fā)數(shù)據(jù)庫的時候，一定要時刻考慮效率的要求個人建議當多表關(guān)聯(lián)的時候，盡量少用 inner join2010宋述臣第 1章 目錄第 1 章Sql 之 left join 、 right join 、 inner join 的區(qū)別 3第 2 章INNER JOIN 和 left JOIN 82.1 現(xiàn)在比較： left join 和 inner join 13第 3 章提升 left join 效率 14第 4 章SQL Server 中 Inner join 和 where 的效率差異 14第 1 章 Sql 之 left join、right join

2、、inner join的區(qū)別left join（左聯(lián)接）返回包括左表中的所有記錄和右表中聯(lián)結(jié)字段相等的記錄right join（ 右聯(lián)接 ） 返回包括右表中的所有記錄和左表中聯(lián)結(jié)字段相等的記錄inner join（ 等值連接 ） 只返回兩個表中聯(lián)結(jié)字段相等的行舉例如下：表 A 記錄如下：aID aNum1 a200501112 a200501123 a200501134 a200501145 a20050115表 B 記錄如下 :bID bName20060324012 20060324023 20060324034 20060324048 20060324081.left joinsql 語

3、句如下 : select * from A left join B on A.aID = B.bID 結(jié)果如下 : aID aNum bID1 a200501112 a20050112bName1 20060324012 2006032402a200501132006032403a2005011420060324045 a20050115NULL NULL所影響的行數(shù)為 5 行）結(jié)果說明 :left join 是以 A 表的記錄為基礎(chǔ)的 ,A 可以看成左表 ,B 可以看成右表 ,left join 是以左表為 準的.換句話說，左表（A）的記錄將會全部表示出來,而右表（B）只會顯示符合搜索條件的

4、記錄（例子中為 : A.aID = B.bID）.B 表記錄不足的地方均為 NULL.2.right joinsql 語句如下select * from Aright join Bon A.aID = B.bID結(jié)果如下 :aIDaNumbIDbNameNULLa20050111a20050112a20050113a20050114NULL所影響的行數(shù)為5 行)20060324012006032402200603240320060324042006032408仔細觀察一下 ,就會發(fā)現(xiàn) ,和結(jié)果說明 :left join 的結(jié)果剛好相反 ,這次是以右表 (B) 為基礎(chǔ)的 ,A 表不足的地方用 N

5、ULL 填充 .3.inner joinsql 語句如下select * from Ainnerjoin Bon A.aID = B.bID結(jié)果如下 :aID aNum bIDbName1 a200501111 20060324012 a200501122 20060324023 a200501133 20060324034 a200501144 2006032404結(jié)果說明 :很明顯 ,這里只顯示出了A.aID = B.bID 的記錄 . 這說明 inner join 并不以誰為基礎(chǔ) ,它只顯示符合條件的記錄注:LEFT JOIN 操作用于在任何的 FROM 子句中，組合來源表的記錄。 使用

6、 LEFT JOIN 運 算來創(chuàng)建一個左邊外部聯(lián)接。 左邊外部聯(lián)接將包含了從第一個 （左邊） 開始的兩個表中的全 部記錄，即使在第二個（右邊）表中并沒有相符值的記錄。語法： FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2說明： table1, table2 參數(shù)用于指定要將記錄組合的表的名稱。fieldl, field2參數(shù)指定被聯(lián)接的字段的名稱。且這些字段必須有相同的數(shù)據(jù)類型及包含相同類型的數(shù)據(jù)，但它們不需要有相同的名稱。compopr參數(shù)指定關(guān)系比較運算符："="，"<

7、;"，">"，"<="，">="或"<>"。如果在INNER JOIN操作中要聯(lián)接包含 Memo數(shù)據(jù)類型或 OLE Object數(shù)據(jù)類型數(shù)據(jù)的字段，將會發(fā)生錯誤時間漩渦用上面提到的A、B表為例:select * from Aleft join(select blD,bName from B where blD <= 3)Ton T.blD = A.alD第2章 inner join， left join， right join 圖形解析現(xiàn)在有兩張表：1.部門表(bran

8、ch):2. 員工表(employee):a. 內(nèi)連接(inner join)內(nèi)連接就是獲取兩張表共有的數(shù)據(jù)Sql代碼國1. SELECT b.branch, FROM branch AS b INNER JOIN employee AS e ON b.id=e.bra nchid結(jié)果:branchname遊務(wù)部|建名丄姓客2人事辭蛙名3口人事貳姓名4如果Sql換個寫法:Sql代碼障1. SELECT b.branch, FROM employee AS e INNER JOIN branch AS b ON b.id=e.bra nchid結(jié)果:branchname財

9、務(wù)魏姓名2人事部址名3人事茹姓名4呵呵，發(fā)現(xiàn)結(jié)果是一樣的，不管內(nèi)連接的哪張表，結(jié)果都是獲取的兩張表的 共有數(shù)據(jù)！此外，還有一種寫法，能達到同樣的效果:Sql代碼侖1. SELECT b.branch, FROM employee AS e,branch AS b WHERE b.i d=e.bra nchid結(jié)果:branchname財務(wù)部|娃名L財務(wù)剖姓名2人事部姓名3人事部蛙名4b. 左連接(left join):Sql代碼®1. SELECT b.branch, FROM branch AS b LEFT JOIN employee AS e ON b.

10、id=e.bra nchid結(jié)果:branchn&ae|蛙名1財済部姓名2人事記咼容3亙?nèi)耸虏扛p名4n(NULL分析下，為什么會有這樣的結(jié)果，找個訣竅，看Sql語句的from關(guān)鍵字的 后面是哪張表（bra nch表），那么結(jié)果一定包含bran ch表中的所查詢的字段（branch字段）所關(guān)聯(lián)左表（employee表）的所有數(shù)據(jù)，如果左連接表（employee 表中）不存在branch中的所查詢字段，那么該字段的值為 Null。好了，現(xiàn)在換個寫法:Sql代碼厲1. SELECT b.branch, FROM employee AS e LEFT JOIN branch AS

11、b ON b.id=e.bra nchid結(jié)果:najie拄茗2姓名3蛙名4branch |耳呈菱對務(wù)衛(wèi) 亙?nèi)耸耡:在分析下，看Sql語句的from 關(guān)鍵字的后面是哪張表（employee表），那 么結(jié)果一定包含employee表中的所查詢的字段（name字段）所關(guān)聯(lián)左表（branch 表）的所有數(shù)據(jù)。c. 右連接(right join):第3章 INNER JOIN 和 left JOIN假如我們描述這樣一種關(guān)系，表1存儲的是區(qū)域和區(qū)域ID,表二代表的是微軟公司的子公司以及子公司所在的區(qū)域1、表1CREATE TABLE regio n(regio nid i nt IDENTITY (1,

12、 1) NOT NULL ,regio nn ame varchar (50) COLLATE Chi nese_PRC_CI_AS NOT NULL ,CONSTRAINT PK_regio n PRIMARY KEYCLUSTERED(regi onid)ON PRIMARY)ON PRIMARYGO2、假如我們描述這樣一種關(guān)系CREATE TABLE MicroSoft(subId i nt IDENTITY (1, 1) NOT NULL ,regio nid i nt NOT NULL ,n ame varchar(50) n ull,CONSTRAINT PK_microsoft

13、PRIMARY KEY CLUSTERED(subId)ON PRIMARY)ON PRIMARYGOregionlDregionName1_J北京24、表2的數(shù)據(jù)為:subl Dregioni dname11beijing sub22shanghai su b33henan subregi on t2ON t1.regi on id = t2.regi onidregi on t2ON t1.regio nid = t2.regio nid3.1 現(xiàn)在比較：left join 和 inner joinSELECT * FROM microsoft t1 INNER JOINSU bl Dre

14、gi oni dnameregi oni dregio nnam e11beijing su b1beijin g22shangh si sub2shan ghaiSELECT * FROM microsoft t1 left JOINsu bl Dregi oni dnameregi oni dregionname11beijin g su b1beijin g22shangh2shanai subghai£rhennan subnullnull由此可以看出，inn er join內(nèi)聯(lián)接使用比較運算符根據(jù)每個表共有的列的值匹配兩個表中的 行。左向外聯(lián)接的結(jié)果集包括LEFT OUTE

15、R子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。 如果右表的某行在左表中沒有匹配行，則將為左表返回空值。完整外部聯(lián)接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結(jié)果集行包含基表的數(shù)據(jù)值?？柗e第4章提升left join效率一：from子句中過濾數(shù)據(jù)后left join跟 先left join后過濾數(shù)據(jù)的執(zhí)行效率比較; 分別舉例如下：testl:select t1.emp_ n

16、o,t1.emp_ name,t2.dep_ no,t2.dep_ namefrom (select t.emp_ no,t.emp_ name,t.dep_ nofrom employeewhere t.emp_ no < 80707999)t1left join departme nt t2 on t1.dep_ no = t2.dep_ notest2：select t1.emp_ no,t1.emp_ name,t2.dep_ no,t2.dep_ namefrom employee t1left join departme nt t2 on t1.dep_ no = t2.de

17、p_ nowhere t1.emp_no < 80707999在大數(shù)據(jù)量的情況下test1比test2效率高第5章 SQL Server 中 Inner join 和 where各大論壇，包括 MSDN上很多人提出了這個問題，但回答是眾說紛紜。總體上總結(jié)出來時說：對小數(shù)據(jù)量（N萬）的來說效率幾乎無差異，更有說法說Inn er join和Where只是SQL標準不同，在查詢分析器中SQL Server查詢分析器是將 Where直接轉(zhuǎn)換為Join后查 詢的。還是自己來做試驗吧。 如是有了如下比較結(jié)果（均在查詢分析器中查詢和計時）：語句（1）declare operatorName nvarc

18、har(50)set operatorName = '%'select distinct item.* from item , customer_item , customer_operator ,operatorwhere item.itemcode = customer_item.itemCodeand customer_item.customerCode = customer_operator.customerCodeand customer_operator.operatorld = customer_operator.operatorldand operator.op

19、eratorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted =0查詢結(jié)果，74行，共時間0: 00: 04語句(2)declare operatorName nvarchar(50)set operatorName = '%'select distinet item.* from item inner join customer_itemon item.itemcode = customer_item.itemCodein

20、ner join customer_operator on customer_item.customerCode = customer_operator.customerCodeinner join operator on customer_operator.operatorld = operator.operatorldwhere operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted =共74行，時間0: 0

21、0: 01后檢查發(fā)現(xiàn)語句（1）中有一個重復(fù)自查詢條件：customer_operator.operatorId = customer_operator.operatorId將其葉加到語句2中，語句（3）declare operatorName nvarchar（50）set operatorName = '%'select distinet item.* from item inner join customer_itemon item.itemcode = customer_item.itemCodeinner join customer_operator on customer_item.customerCode = customer_operator.customerCodeinner join operator on customer_operator.operatorId = operator.operatorldwhere operator.operatorName like operatorNameand item.deleted = 0 and customer_item.deleted = 0 and customer_operator.delet