在数据库操作中，经常需要从多个表中提取数据。这时，JOIN 子句就派上了用场。然而，许多开发者在使用 JOIN 时常常出现错误，这通常是因为他们没有正确理解每种 JOIN 的具体作用和行为，导致SQL过程或视图返回了意料之外的结果集。因此，本文旨在详细解释 SQL Server 中各种 JOIN 的类型及其用法。
在SQLServer 中，有以下类型的 JOIN：
本文将详细探讨上述 JOIN 类型及其在SQLServer 中的高级概念。
为了说明这些 JOIN 类型，将使用以下示例表：
假设有 5 名销售代表，他们的详细信息保存在 'RepDetails' 表中，他们完成的销售交易记录在 'SalesDetails' 表中。在 'SalesDetails' 表中，包含了一些没有匹配销售代表的交易。同样，在 'RepDetails' 表中，也有一些销售代表没有销售信息。
CREATE TABLE RepDetails(
RepId INT,
RepName VARCHAR(30)
)
CREATE TABLE SalesDetails(
RepId INT,
SaleMonth VARCHAR(6),
OrderNo VARCHAR(6),
SaleValue MONEY
)
CREATE TABLE RepRating(
RepId INT,
Rate INT,
YearMonth VARCHAR(6)
)
CREATE TABLE Settings(
S_Id INT,
S_Desc VARCHAR(20),
S_Value VARCHAR(20)
)
INSERT INTO RepDetails (RepId, RepName)
VALUES
(1, 'Eugene Thomas'),
(2, 'John Wheeler'),
(3, 'Curtis Bailey'),
(4, 'Jeffrey Garrett'),
(5, 'Rosemarie Hubbard')
INSERT INTO SalesDetails (RepId, SaleMonth, OrderNo, SaleValue)
VALUES
(7, '201607', 'XpyDy3', 839),
(1, '201607', 'NR0RTp', 496),
(4, '201607', '4552T4', 299),
...
INSERT INTO dbo.RepRating(RepId, Rate, YearMonth)
VALUES
(1, 1, '201608'),
(3, 2, '201608'),
(4, 1, '201609'),
(2, 2, '201609')
INSERT INTO dbo.Settings(S_Id, S_Desc, S_Value)
VALUES
(1, 'LedgerMonth', '201609'),
(2, 'TaxRate', '10%')
当使用INNER JOIN连接两个或多个表时，只有当在左右两个表中都能找到满足提供的条件的记录时，才会返回结果。
SELECT *
FROM dbo.RepDetails AS RD
JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
请注意：有销售代表 RepId 为 1,2,3,4,5。但在 SalesDetails 表中，有 RepId 为 1,2,4,6 & 7 的销售详情。因此，当这些表被连接时，存在于两个表中的 RepId，即 1, 2 和 4 将返回详细信息，最终给上述结果集。
在 LEFT OUTER JOIN 中，与 INNER JOIN 不同，它将选择 'Left' 表中的所有记录，并根据 JOIN 条件选择 'Right' 表中的任何匹配记录并返回结果。如果没有 'Right' 表中的匹配详细信息，相关列将返回为 'NULL'。
SELECT *
FROM dbo.RepDetails AS RD
LEFT JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
LEFT OUTER JOIN 可以用维恩图来表示，如下：
在 RIGHT OUTER JOIN 中，它将选择 'Right' 表中的所有记录，并根据 JOIN 条件选择左表中的任何匹配记录并返回。如果没有左表中的匹配记录，它将返回 'NULL' 值。
SELECT *
FROM dbo.SalesDetails AS SD
RIGHT JOIN dbo.RepDetails AS RD
ON SD.RepId = RD.RepId
RIGHT OUTER JOIN 可以用维恩图来表示，如下：
FULL OUTER JOIN 是 LEFT 和 RIGHT OUTER JOIN 的混合。它将根据 JOIN 条件返回 'Left' 和 'Right' 表中的所有行。当没有匹配的详细信息时，它将在相应列中返回 NULL 值。
SELECT *
FROM dbo.RepDetails AS RD
FULL OUTER JOIN dbo.SalesDetails AS SD
ON SD.RepId = RD.RepId
FULL OUTER JOIN 可以用维恩图来表示，如下：
CROSS JOIN 将返回一个结果集，其行数等于 'Left' 表中的行数乘以 'Right' 表中的行数。通常，当 WHERE 条件中没有提供条件时，会出现这种行为。因此，左表中的每一行都与右表中的每一行连接。这种行为通常称为 'Cartesian Product'。
SELECT *
FROM dbo.RepDetails AS RD
CROSS JOIN dbo.Settings AS S
但是，当通过 WHERE 子句提供某些条件时，CROSS JOIN 将表现得像 INNER JOIN：
SELECT *
FROM dbo.RepDetails AS RD
CROSS JOIN dbo.Settings AS S
WHERE RD.RepId = S.S_Id
注意：在CROSS JOIN中，不可能引用左表和右表中的值。例如：以下代码将导致错误。
SELECT *
FROM dbo.RepDetails AS RD
CROSS JOIN (
SELECT *
FROM dbo.Settings AS S
WHERE S.S_Id = RD.RepId
) AS ST
将导致错误：
错误消息：Msg 4104, Level 16, State 1, Line 78 The multi-part identifier "RD.RepId" could not be bound.
CROSS APPLY 表现得像 INNER JOIN，而 OUTER APPLY 表现得像 OUTER JOIN。但 APPLY 与 JOIN 的主要区别在于 APPLY 操作符的右侧可以引用左侧表中的列。这在 JOIN 中是不可能的。
例如，假设需要获取销售代表的详细信息以及他们所做的最大销售记录。因此，以下查询是不可能的，因为它由于上述原因返回错误。
SELECT *
FROM dbo.RepDetails AS RD
JOIN (
SELECT TOP 1 *
FROM dbo.SalesDetails AS SD
WHERE RD.RepId = SD.RepId
ORDER BY SD.SaleValue DESC
) AS SData
将导致错误：
错误消息：Msg 4104, Level 16, State 1, Line 78 The multi-part identifier "RD.RepId" could not be bound.
实现这一点的方法是使用 APPLY。
考虑到上述要求，可以使用 CROSS APPLY 来实现上述目标。
SELECT *
FROM dbo.RepDetails AS RD
CROSS APPLY (
SELECT TOP 1 *
FROM dbo.SalesDetails AS SD
WHERE RD.RepId = SD.RepId
ORDER BY SD.SaleValue DESC
) AS SData
注意到上述示例，可以看到它返回了三条记录。但是，如果仔细观察，SalesRep 表包含五个 Reps。但是 CROSS APPLY 只返回了最大销售值，如果有匹配的记录在 APPLY 操作符的右侧表中。（类似于 INNER JOIN）
SELECT *
FROM dbo.RepDetails AS RD
OUTER APPLY (
SELECT TOP 1 *
FROM dbo.SalesDetails AS SD
WHERE RD.RepId = SD.RepId
ORDER BY SD.SaleValue DESC
) AS SData