SQLJoin的一些总结(实例)

Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等

1.1.1 摘要
Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据，这时我们应该考虑使用Join，因为Join具体联接表或函数进行查询的特性

本文将通过具体例子介绍SQL中的各种常用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 = &＃39;College&＃39;) 
DROP TABLE College; 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = &＃39;Student&＃39;) 
DROP TABLE Student; 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = &＃39;Apply&＃39;) 
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.enrollment 
FROM College INNER JOIN 
Apply ON College.cName = Apply.cName

---- 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

代码如下:

---- College Cross join Apply. 
SELECT College.cName, College.state, College.enrollment, 
Apply.cName, Apply.major, Apply.decision 
FROM College 
CROSS JOIN 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)) 
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])

我们也可以使用内部联接实现和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

代码如下:

---- 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

查询执行时间:

CPU 时间= 0 毫秒，占用时间= 11 毫秒。

Inner join查询执行时间:

CPU 时间= 0 毫秒，占用时间= 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 
)

我们发现常用的EXISTS子句，原来是通过Left Semi Join实现的，所以说Semi-join在SQL Server中又许多使用场合。

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

其实，我们常用的NOT EXISTS子句的实现是通过Anti-semi-join，通过执行计划我们发现在查找匹配sID时，SQL使用 Left Anti Semi Join进行查询。