1.1.1 摘要

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

本文将通过具体例子介绍SQL中的各种常用Join的特性和使用场合：

目录

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.decision
FROM College CROSS 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))
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

图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 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 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 Student
WHERE 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 Student WHERE 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的使用场合和特性。