SQL Join的一些總結(jié)(實(shí)例)
發(fā)布日期：2022-01-14 16:12 | 文章來源：源碼之家
1.1.1 摘要
Join是關(guān)系型數(shù)據(jù)庫系統(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)行查詢的特性 本文將通過具體例子介紹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來內(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 College表中的數(shù)據(jù)
如上圖1所示，我們把Apply表包含的學(xué)校信息查詢出來了，由于Harvard并沒有被查詢出來，所以我們知道暫時(shí)還沒有學(xué)生申請(qǐng)Harvard。
內(nèi)聯(lián)接（Inner join）滿足交換律：“A inner join B” 和 “B inner join A” 是相等的。
Outer join
假設(shè)我們想看到所有學(xué)校信息；即使是那些沒有申請(qǐng)的學(xué)校（如：Harvard），這時(shí)我們可以使用外部聯(lián)接（Outer join）進(jìn)行查詢。由于外部聯(lián)接保存一個(gè)或兩個(gè)輸入表的所有行，即使無法找到匹配聯(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
---- 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
表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的組合）。也就是說，它匹配一個(gè)表與另一個(gè)表中的每一行；我們不能通過使用ON子句在交叉聯(lián)接指定謂詞，雖然我們可以使用WHERE子句來實(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
在SQL Server 2005中提供了Cross apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行join查詢。例如，現(xiàn)在我們想通過函數(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
在介紹Cross apply和Outer join之后，現(xiàn)在讓我們理解Out apply也就不難了，Outer apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行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])
---- 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 毫秒。
---- 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子句，原來是通過Left Semi Join實(shí)現(xiàn)的，所以說Semi-join在SQL Server中又許多使用場(chǎng)合。
---- 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)是通過Anti-semi-join，通過執(zhí)行計(jì)劃我們發(fā)現(xiàn)在查找匹配sID時(shí)，SQL使用 Left Anti Semi Join進(jìn)行查詢。
圖12 執(zhí)行計(jì)劃 1.1.3 總結(jié)
本文介紹了SQL中常用了聯(lián)接查詢方式：Inner join、Outer join、Cross join和Cross apply的使用場(chǎng)合和特性。
Join是關(guān)系型數(shù)據(jù)庫系統(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)行查詢的特性 本文將通過具體例子介紹SQL中的各種常用Join的特性和使用場(chǎng)合： 1.1.2 正文
首先我們?cè)趖empdb中分別定義三個(gè)表College、Student和Apply，具體SQL代碼如下：
復(fù)制代碼 代碼如下:
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);
復(fù)制代碼 代碼如下:
---- 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
| cName | state | enrollment |
| Stanford | CA | 15000 |
| Berkeley | CA | 36000 |
| MIT | MA | 10000 |
| Cornell | NY | 21000 |
| Harvard | MA | 29000 |
假設(shè)我們想看到所有學(xué)校信息；即使是那些沒有申請(qǐng)的學(xué)校（如：Harvard），這時(shí)我們可以使用外部聯(lián)接（Outer join）進(jìn)行查詢。由于外部聯(lián)接保存一個(gè)或兩個(gè)輸入表的所有行，即使無法找到匹配聯(lián)接謂詞的行。 具體SQL代碼如下：
復(fù)制代碼 代碼如下:
---- Gets all college information
SELECT College.cName, College.state, College.enrollment,
Apply.cName, Apply.major, Apply.decision
FROM College LEFT OUTER JOIN
復(fù)制代碼 代碼如下:
---- 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
|
聯(lián)接類型 |
保留數(shù)據(jù)行 |
|
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 外部聯(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的組合）。也就是說，它匹配一個(gè)表與另一個(gè)表中的每一行；我們不能通過使用ON子句在交叉聯(lián)接指定謂詞，雖然我們可以使用WHERE子句來實(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代碼如下：
復(fù)制代碼 代碼如下:
---- 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ù)
在SQL Server 2005中提供了Cross apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行join查詢。例如，現(xiàn)在我們想通過函數(shù)的結(jié)果值和表Student進(jìn)行查詢，這時(shí)我們可以使用Cross apply進(jìn)行查詢：
復(fù)制代碼 代碼如下:
---- 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代碼如下：
復(fù)制代碼 代碼如下:
---- 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
在介紹Cross apply和Outer join之后，現(xiàn)在讓我們理解Out apply也就不難了，Outer apply使表可以和表值函數(shù)（table-valued functions TVF‘s）結(jié)果進(jìn)行join查詢，找到匹配值則有值，沒有找到匹配值則以NULL表示。
復(fù)制代碼 代碼如下:
---- 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例子中，我們也可以通過Inner join實(shí)現(xiàn)相同的查詢。
復(fù)制代碼 代碼如下:
---- 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 毫秒。
復(fù)制代碼 代碼如下:
---- 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子句，原來是通過Left Semi Join實(shí)現(xiàn)的，所以說Semi-join在SQL Server中又許多使用場(chǎng)合。
復(fù)制代碼 代碼如下:
---- 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)是通過Anti-semi-join，通過執(zhí)行計(jì)劃我們發(fā)現(xiàn)在查找匹配sID時(shí)，SQL使用 Left Anti Semi Join進(jìn)行查詢。
圖12 執(zhí)行計(jì)劃 1.1.3 總結(jié)
本文介紹了SQL中常用了聯(lián)接查詢方式：Inner join、Outer join、Cross join和Cross apply的使用場(chǎng)合和特性。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信