SQL Join的一些總結(jié)(實(shí)例)

Join是關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)的重要操作之一，SQL Server中包含的常用Join：內(nèi)聯(lián)接、外聯(lián)接和交叉聯(lián)接等

1.1.1 摘要
join是關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)的重要操作之一，sql server中包含的常用join：內(nèi)聯(lián)接、外聯(lián)接和交叉聯(lián)接等。如果我們想在兩個(gè)或以上的表獲取其中從一個(gè)表中的行與另一個(gè)表中的行匹配的數(shù)據(jù)，這時(shí)我們應(yīng)該考慮使用join，因?yàn)閖oin具體聯(lián)接表或函數(shù)進(jìn)行查詢的特性

本文將通過(guò)具體例子介紹sql中的各種常用join的特性和使用場(chǎng)合：

1.1.2 正文
首先我們?cè)趖empdb中分別定義三個(gè)表college、student和apply，具體sql代碼如下：
代碼如下:

USE?tempdb?    ----?If?database?exists?the?same?name?datatable?deletes?it.?  IF?EXISTS(SELECT?TABLE_NAME?FROM?INFORMATION_SCHEMA.TABLES?WHERE?TABLE_NAME?=?'College')?  DROP?TABLE?College;?  IF?EXISTS(SELECT?TABLE_NAME?FROM?INFORMATION_SCHEMA.TABLES?WHERE?TABLE_NAME?=?'Student')?  DROP?TABLE?Student;?  IF?EXISTS(SELECT?TABLE_NAME?FROM?INFORMATION_SCHEMA.TABLES?WHERE?TABLE_NAME?=?'Apply')?  DROP?TABLE?Apply;?    ----?Create?Database.?  create?table?College(cName?nvarchar(50),?state?text,?enrollment?int);?  create?table?Student(sID?int,?sName?nvarchar(50),?GPA?real,?sizeHS?int);?  create?table?Apply(sID?int,?cName?nvarchar(50),?major?nvarchar(50),?decision?text);

Inner join

內(nèi)聯(lián)接（Inner join）是最常用的聯(lián)接類型之一，它查詢滿足聯(lián)接謂詞的數(shù)據(jù)。

假設(shè)我們要查詢申請(qǐng)表Apply中申請(qǐng)學(xué)校的相關(guān)信息，由于Apply表中包含學(xué)校名字我們并不能預(yù)知，所以我們可以根據(jù)cName來(lái)內(nèi)聯(lián)接（Inner join）表College和Apply，從而找到Apply表中包含學(xué)校的信息。

具體SQL代碼如下：
代碼如下:

----?Gets?college?information?from?college?table?  ----?bases?on?college?name.?  SELECT?DISTINCT?College.cName,?College.enrollment?  FROM?College?INNER?JOIN?  Apply?ON?College.cName?=?Apply.cName

圖1查詢結(jié)果
表1 College表中的數(shù)據(jù)?

如上圖1所示，我們把Apply表包含的學(xué)校信息查詢出來(lái)了，由于Harvard并沒(méi)有被查詢出來(lái)，所以我們知道暫時(shí)還沒(méi)有學(xué)生申請(qǐng)Harvard。

內(nèi)聯(lián)接（Inner join）滿足交換律：“A inner join B” 和 “B inner join A” 是相等的。

Outer join
假設(shè)我們想看到所有學(xué)校信息；即使是那些沒(méi)有申請(qǐng)的學(xué)校（如：Harvard），這時(shí)我們可以使用外部聯(lián)接（Outer join）進(jìn)行查詢。由于外部聯(lián)接保存一個(gè)或兩個(gè)輸入表的所有行，即使無(wú)法找到匹配聯(lián)接謂詞的行。

具體SQL代碼如下：
代碼如下:

----?Gets?all?college?information?  SELECT?College.cName,?College.state,?College.enrollment,?  Apply.cName,?Apply.major,?Apply.decision?  FROM?College?LEFT?OUTER?JOIN

圖3左聯(lián)接查詢結(jié)果

如上圖3所示：由于在Apply表中并沒(méi)有學(xué)生申請(qǐng)Harvard，但是我們通過(guò)左聯(lián)接（left outer join）把所有學(xué)校信息查詢出來(lái)了。

由于左聯(lián)接（left outer join）產(chǎn)生表College的完全集，而Apply表中匹配的則有值，而不匹配的則以NULL值取代，所以我們知道Apply表中沒(méi)有學(xué)生申請(qǐng)Harvard。

通過(guò)左聯(lián)接查詢我們可以獲取College的完全集，假設(shè)現(xiàn)在我們既要獲取College的完全集又要獲取Apply的完全集，那么我們可以考慮使用完整外部聯(lián)接（full outer join）。使用完整外部聯(lián)接，我們可以查詢所有的學(xué)校，不管它們是否匹配聯(lián)接謂詞：
代碼如下:

---?Gets?all?information?from?college?and?apply?table.?  SELECT?College.cName,?College.state,?College.enrollment,?  Apply.cName,?Apply.major,?Apply.decision?  FROM?College?FULL?OUTER?JOIN?  Apply?ON?College.cName?=?Apply.cName

圖3 完整外部聯(lián)接查詢結(jié)果

現(xiàn)在我們獲取了College和Apply的完全數(shù)據(jù)集，對(duì)于表中匹配的則有值，即使沒(méi)有找到匹配cName的則以NULL值取代。

下表顯示每種外部聯(lián)接（outer join）匹配時(shí)保留數(shù)據(jù)行的情況：?

表2 外部聯(lián)接保留數(shù)據(jù)行

完整外部聯(lián)接（full outer join）滿足交換律：“A full outer join B” 和 “B full outer join A” 是相等的。

Cross join
交叉聯(lián)接（cross join）執(zhí)行兩個(gè)表的笛卡爾積（就是把表A和表B的數(shù)據(jù)進(jìn)行一個(gè)N*M的組合）。也就是說(shuō)，它匹配一個(gè)表與另一個(gè)表中的每一行；我們不能通過(guò)使用ON子句在交叉聯(lián)接指定謂詞，雖然我們可以使用WHERE子句來(lái)實(shí)現(xiàn)相同的結(jié)果，這是交叉聯(lián)接基本上是作為一個(gè)內(nèi)部聯(lián)接了。

交叉聯(lián)接相對(duì)于內(nèi)部聯(lián)接使用率較低，而且兩個(gè)大表不應(yīng)該進(jìn)行交叉聯(lián)接，因?yàn)檫@將導(dǎo)致一個(gè)非常昂貴的操作和一個(gè)非常大的結(jié)果集。

具體SQL代碼如下：
代碼如下:

----?College?Cross?join?Apply.?  SELECT?College.cName,?College.state,?College.enrollment,?  Apply.cName,?Apply.major,?Apply.decision?  FROM?College?  CROSS?JOIN?Apply

圖4 College表和Apply表的行數(shù)

圖5 交叉聯(lián)接

現(xiàn)在我們對(duì)College和Apply表進(jìn)行交叉聯(lián)接，而且生成數(shù)據(jù)行為College和Apply表行數(shù)的笛卡爾積即5 * 20 = 100。

Cross apply
在SQL Server 2005中提供了Cross apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行join查詢。例如，現(xiàn)在我們想通過(guò)函數(shù)的結(jié)果值和表Student進(jìn)行查詢，這時(shí)我們可以使用Cross apply進(jìn)行查詢：
代碼如下:

----?Creates?a?function?to?get?data?from?Apply?base?on?sID.?  CREATE?FUNCTION?dbo.fn_Apply(@sID?int)?  RETURNS?@Apply?TABLE?(cName?nvarchar(50),?major?nvarchar(50))?  AS?  BEGIN?  INSERT?@Apply?SELECT?cName,?major?FROM?Apply?where?[sID]?=?@sID?  RETURN?  END?  ----?Student?cross?apply?function?fn_Apply.?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS,?  cName,?major?  FROM?Student?CROSS?APPLY?dbo.fn_Apply([sID])

我們也可以使用內(nèi)部聯(lián)接實(shí)現(xiàn)和Cross apply相同的查詢功能，具體SQL代碼如下：
代碼如下:

----?Student?INNER?JOIN?Apply?bases?on?sID.?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS,?  cName,?major?  FROM?Student?INNER?JOIN?[Apply]?  ON?Student.sID?=?[Apply].sID

圖6 Cross apply查詢

Outer apply
在介紹Cross apply和Outer join之后，現(xiàn)在讓我們理解Out apply也就不難了，Outer apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行join查詢，找到匹配值則有值，沒(méi)有找到匹配值則以NULL表示。
代碼如下:

----?Student?outer?apply?function?fn_Apply.?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS,?  cName,?major?  FROM?Student?OUTER?APPLY?dbo.fn_Apply([sID])

圖7 Outer apply查詢

Inner Join和Cross apply的區(qū)別

首先我們知道Inner join是表和表的聯(lián)接查詢，而Cross apply是表和表值函數(shù)的聯(lián)接查詢，在前面Cross apply例子中，我們也可以通過(guò)Inner join實(shí)現(xiàn)相同的查詢。
代碼如下:

----?Student?cross?apply?function?fn_Apply.?  SET?STATISTICS?PROFILE?ON?  SET?STATISTICS?TIME?ON?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS,?  cName,?major?  FROM?Student?CROSS?APPLY?dbo.fn_Apply([sID])?  SET?STATISTICS?PROFILE?OFF?  SET?STATISTICS?TIME?OFF?  ----?Student?INNER?JOIN?Apply?base?on?sID.?  SET?STATISTICS?PROFILE?ON?  SET?STATISTICS?TIME?ON?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS,?  cName,?major?  FROM?Student?INNER?JOIN?[Apply]?  ON?Student.sID?=?[Apply].sID?  SET?STATISTICS?PROFILE?OFF?  SET?STATISTICS?TIME?OFFCross?apply

查詢執(zhí)行時(shí)間:

CPU 時(shí)間= 0 毫秒，占用時(shí)間= 11 毫秒。

Inner join查詢執(zhí)行時(shí)間:

CPU 時(shí)間= 0 毫秒，占用時(shí)間= 4 毫秒。

圖8 執(zhí)行計(jì)劃

如圖8所示：Cross apply首先執(zhí)行TVF（table-valued functions），然后對(duì)表Studnet進(jìn)行全表掃描，接著通過(guò)遍歷sID查找匹配值。

Inner join對(duì)表Student和Apply進(jìn)行全表掃描，然后通過(guò)哈希匹配查找匹配的sID值。

通過(guò)以上的SQL執(zhí)行時(shí)間和執(zhí)行計(jì)劃，我們能不能說(shuō)Inner join比Cross apply好呢？答案是否定的，如果表的數(shù)據(jù)量很大，那么Inner join的全表掃描耗費(fèi)時(shí)間和CPU資源就增加了（可通過(guò)數(shù)據(jù)量大的表進(jìn)行測(cè)試）。

雖然大多數(shù)采用Cross apply實(shí)現(xiàn)的查詢，可以通過(guò)Inner join實(shí)現(xiàn)，但Cross apply可能產(chǎn)生更好的執(zhí)行計(jì)劃和更佳的性能，因?yàn)樗梢栽诼?lián)接執(zhí)行之前限制集合加入。

Semi-join和Anti-semi-join

Semi-join從一個(gè)表中返回的行與另一個(gè)表中數(shù)據(jù)行進(jìn)行不完全聯(lián)接查詢（查找到匹配的數(shù)據(jù)行就返回，不再繼續(xù)查找）。

Anti-semi-join從一個(gè)表中返回的行與另一個(gè)表中數(shù)據(jù)行進(jìn)行不完全聯(lián)接查詢，然后返回不匹配的數(shù)據(jù)。

不同于其他的聯(lián)接運(yùn)算，Semi-join和Anti-semi-join沒(méi)有明確的語(yǔ)法來(lái)實(shí)現(xiàn)，但Semi-join和Anti-semi-join在SQL Server中有多種應(yīng)用場(chǎng)合。我們可以使用EXISTS子來(lái)實(shí)現(xiàn)Semi-join查詢，Not EXISTS來(lái)實(shí)現(xiàn)Anti-semi-join。現(xiàn)在讓我們通過(guò)具體的例子說(shuō)明吧！

假設(shè)要求我們找出Apply和Student表中sID匹配的學(xué)生信息，這和前面的Inner join查詢結(jié)果將一樣，具體SQL代碼如下：
代碼如下:

----?Student?Semi-join?Apply?base?on?sID.?  SELECT?Student.sName,?Student.GPA,?Student.sizeHS?  ----[Apply].cName,?[Apply].major?  FROM?Student?  WHERE?exists?(?  SELECT?*?  from?[Apply]?  where?[Apply].sID?=?Student.sID?  )

我們發(fā)現(xiàn)常用的EXISTS子句，原來(lái)是通過(guò)Left Semi Join實(shí)現(xiàn)的，所以說(shuō)Semi-join在SQL Server中又許多使用場(chǎng)合。

圖9 查詢結(jié)果

圖10 執(zhí)行計(jì)劃

現(xiàn)在要求我們找出還沒(méi)有申請(qǐng)學(xué)校的學(xué)生信息，這時(shí)我們立刻反應(yīng)可以使用NOT EXISTS子句來(lái)實(shí)現(xiàn)該查詢，具體SQL代碼如下：
代碼如下:

----?Gets?student?still?not?apply?for?school.?  SELECT?Student.sID,?Student.sName,?Student.GPA,?Student.sizeHS?  ----[Apply].cName,?[Apply].major?  FROM?Student?  WHERE?NOT?EXISTS?(?  SELECT?*?  FROM?[Apply]?  WHERE?[Apply].sID?=?Student.sID?  )

其實(shí)，我們常用的NOT EXISTS子句的實(shí)現(xiàn)是通過(guò)Anti-semi-join，通過(guò)執(zhí)行計(jì)劃我們發(fā)現(xiàn)在查找匹配sID時(shí)，SQL使用 Left Anti Semi Join進(jìn)行查詢。

圖11 查詢結(jié)果

圖12 執(zhí)行計(jì)劃

1.1.3 總結(jié)
本文介紹了SQL中常用了聯(lián)接查詢方式：Inner join、Outer join、Cross join和Cross apply的使用場(chǎng)合和特性，更多相關(guān)內(nèi)容請(qǐng)關(guān)注PHP中文網(wǎng)（www.php.cn）！