SQL Join的一些總結

1.1.1 摘要

Join是關係型資料庫系統的重要操作之一，SQL Server中包含的常用Join：內聯結、外聯結和交叉聯結等。如果我們想在兩個或以上的表擷取其中從一個表中的行與另一個表中的行匹配的資料，這時我們應該考慮使用Join，因為Join具體聯結表或函數進行查詢的特性

本文將通過具體例子介紹SQL中的各種常用Join的特性和使用場合：

目錄

• Inner join
• Outer join
• Cross join
• Cross apply
• Cross apply 和 Inner join的區別
• Semi-join和Anti-semi-join

1.1.2 本文

首先我們在tempdb中分別定義三個表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

內聯結（Inner join）是最常用的聯結類型之一，它查詢滿足聯結謂詞的資料。

假設我們要查詢申請表Apply中申請學校的相關資訊，由於Apply表中包含學校名字我們並不能預知，所以我們可以根據cName來內聯結（Inner join）表College和Apply，從而找到Apply表中包含學校的資訊。

具體SQL代碼如下：

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

圖1查詢結果

cName	state	enrollment
Stanford	CA	15000
Berkeley	CA	36000
MIT	MA	10000
Cornell	NY	21000
Harvard	MA	29000

表1 College表中的資料

如1所示，我們把Apply表包含的學校資訊查詢出來了，由於Harvard並沒有被查詢出來，所以我們知道暫時還沒有學生申請Harvard。

內聯結（Inner join）滿足交換律：“A inner join B” 和 “B inner join A” 是相等的。

Outer join

假設我們想看到所有學校資訊；即使是那些沒有申請的學校（如：Harvard），這時我們可以使用外部聯結（Outer join）進行查詢。由於外部聯結儲存一個或兩個輸入表的所有行，即使無法找到匹配聯結謂詞的行。

具體SQL代碼如下：

---- Gets all college informationSELECT College.cName, College.state, College.enrollment,Apply.cName, Apply.major, Apply.decisionFROM  College LEFT OUTER JOIN        Apply ON College.cName = Apply.cName

圖3左聯結查詢結果

如3所示：由於在Apply表中並沒有學生申請Harvard，但是我們通過左聯結（left outer join）把所有學校資訊查詢出來了。

由於左聯結（left outer join）產生表College的完全集，而Apply表中匹配的則有值，而不匹配的則以NULL值取代，所以我們知道Apply表中沒有學生申請Harvard。

通過左聯結查詢我們可以擷取College的完全集，假設現在我們既要擷取College的完全集又要擷取Apply的完全集，那麼我們可以考慮使用完整外部聯結（full outer join）。使用完整外部聯結，我們可以查詢所有的學校，不管它們是否匹配聯結謂詞：

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

圖3 完整外部聯結查詢結果

現在我們擷取了College和Apply的完全資料集，對於表中匹配的則有值，即使沒有找到匹配cName的則以NULL值取代。

下表顯示每種外部聯結（outer join）匹配時保留資料行的情況：

聯結類型	保留資料行
A left outer join B	all A rows
A right outer join B	all B rows
A full outer join B	all A and B rows

表2 外部聯結保留資料行

完整外部聯結（full outer join）滿足交換律：“A full outer join B” 和 “B full outer join A” 是相等的。

Cross join

交叉聯結（cross join）執行兩個表的笛卡爾積（就是把表A和表B的資料進行一個N*M的組合）。也就是說，它匹配一個表與另一個表中的每一行；我們不能通過使用ON子句在交叉聯結指定謂詞，雖然我們可以使用WHERE子句來實現相同的結果，這是交叉聯結基本上是作為一個內部聯結了。

交叉聯結相對於內部聯結使用率較低，而且兩個大表不應該進行交叉聯結，因為這將導致一個非常昂貴的操作和一個非常大的結果集。

具體SQL代碼如下：

---- College Cross join Apply.SELECT College.cName, College.state, College.enrollment,Apply.cName, Apply.major, Apply.decisionFROM CollegeCROSS JOIN Apply

圖4 College表和Apply表的行數

圖5 交叉聯結

現在我們對College和Apply表進行交叉聯結，而且產生資料行為College和Apply表行數的笛卡爾積即5 * 20 = 100。

Cross apply

在SQL Server 2005中提供了Cross apply使表可以和資料表值函式（table-valued functions TVF‘s）結果進行join查詢。例如，現在我們想通過函數的結果值和表Student進行查詢，這時我們可以使用Cross apply進行查詢：

---- 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))ASBEGIN  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])

我們也可以使用內部聯結實現和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之後，現在讓我們理解Out apply也就不難了，Outer apply使表可以和資料表值函式（table-valued functions TVF‘s）結果進行join查詢，找到匹配值則有值，沒有找到匹配值則以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的區別

首先我們知道Inner join是表和表的聯結查詢，而Cross apply是表和資料表值函式的聯結查詢，在前面Cross apply例子中，我們也可以通過Inner join實現相同的查詢。

---- Student cross apply function fn_Apply.SET STATISTICS PROFILE ONSET STATISTICS TIME ONSELECT Student.sName, Student.GPA, Student.sizeHS,cName, major FROM Student CROSS APPLY dbo.fn_Apply([sID])SET STATISTICS PROFILE OFFSET STATISTICS TIME OFF

---- Student INNER JOIN Apply base on sID.SET STATISTICS PROFILE ONSET STATISTICS TIME ONSELECT Student.sName, Student.GPA, Student.sizeHS,cName, major FROM Student INNER JOIN [Apply]ON Student.sID = [Apply].sIDSET STATISTICS PROFILE OFFSET STATISTICS TIME OFF

Cross apply查詢執行時間:

CPU 時間= 0 毫秒，佔用時間= 11 毫秒。

Inner join查詢執行時間:

CPU 時間= 0 毫秒，佔用時間= 4 毫秒。

圖8 執行計畫

8所示：Cross apply首先執行TVF（table-valued functions），然後對錶Studnet進行全表掃描，接著通過遍曆sID尋找匹配值。

Inner join對錶Student和Apply進行全表掃描，然後通過雜湊匹配尋找匹配的sID值。

通過以上的SQL執行時間和執行計畫，我們能不能說Inner join比Cross apply好呢？答案是否定的，如果表的資料量很大，那麼Inner join的全表掃描耗費時間和CPU資源就增加了（可通過資料量大的表進行測試）。

雖然大多數採用Cross apply實現的查詢，可以通過Inner join實現，但Cross apply可能產生更好的執行計畫和更佳的效能，因為它可以在聯結執行之前限制集合加入。

Semi-join和Anti-semi-join

Semi-join從一個表中返回的行與另一個表中資料行進行不完全聯結查詢（尋找到匹配的資料行就返回，不再繼續尋找）。

Anti-semi-join從一個表中返回的行與另一個表中資料行進行不完全聯結查詢，然後返回不匹配的資料。

不同於其他的聯結運算，Semi-join和Anti-semi-join沒有明確的文法來實現，但Semi-join和Anti-semi-join在SQL Server中有多種應用場合。我們可以使用EXISTS子來實現Semi-join查詢，Not EXISTS來實現Anti-semi-join。現在讓我們通過具體的例子說明吧！

假設要求我們找出Apply和Student表中sID匹配的學生資訊，這和前面的Inner join查詢結果將一樣，具體SQL代碼如下：

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

我們發現常用的EXISTS子句，原來是通過Left Semi Join實現的，所以說Semi-join在SQL Server中又許多使用場合。

圖9 查詢結果

圖10 執行計畫

現在要求我們找出還沒有申請學校的學生資訊，這時我們立刻反應可以使用NOT EXISTS子句來實現該查詢，具體SQL代碼如下：

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

其實，我們常用的NOT EXISTS子句的實現是通過Anti-semi-join，通過執行計畫我們發現在尋找匹配sID時，SQL使用 Left Anti Semi Join進行查詢。

圖11 查詢結果

圖12 執行計畫

1.1.3 總結

本文介紹了SQL中常用了聯結查詢方式：Inner join、Outer join、Cross join和Cross apply的使用場合和特性。