65.9K
CodeProject 正在变化。 阅读更多。
Home

准备学习 SQL Server：14. 外部联接简介

essentialSQL
starIconstarIconstarIconstarIconstarIcon

5.00/5 (4投票s)

2014 年 10 月 22 日

MIT

7分钟阅读

viewsIcon

10619

SQL Server 中外部联接介绍

外部联接

本系列文章从“数据库联接简介”一文开始。本课中的所有示例都基于 Microsoft SQL Server Management Studio 和 AdventureWorks2012 数据库。您可以使用我的指南“使用 SQL Server 入门”开始使用这些免费工具。在本文中，我们将介绍外部联接。

外部联接用于匹配两个表中的行。即使没有匹配项，也会包含这些行。其中一个表中的行始终会包含在内，而另一个表中的行在没有匹配项时会包含 NULL 值。

外部联接的类型

有三种类型的 outer join（外部联接）

  • Left Outer Join（左外部联接）– 包含左表中所有的行，右表中不匹配的行将替换为 NULL 值。
  • Right Outer Join（右外部联接）– 包含右表中所有的行，左表中不匹配的行将替换为 NULL 值。
  • Full Outer Join（全外部联接）– 包含两个表中的所有行，不匹配的行将用 NULL 值填充。

让我们深入探讨一下 left outer join。

左外部联接

请查看以下数据模型。该模型取自 AdventureWorks2012 数据库。在此模型中，存在 1 个人对应 0 或 1 个员工的关系。

要生成包含所有 Person LastNames（人员姓氏）的列表，同时如果该 Person（人员）是 Employee（员工），则显示其 JobTitle（职位），我们需要一种方法来联接这两个表，并包含结果中的 Person（人员）行，即使它们与 Employee（员工）不匹配。

这种类型的联接称为 left outer join（左外部联接），因为无论是否匹配，都会包含 JOIN（联接）关键字左侧的表的所有行。left outer join 的基本语法是：

SELECT columnlist
FROM   table
LEFT OUTER JOIN othertable ON join condition

上图中联接的 SQL 代码是：

SELECT person.Person.BusinessEntityID,
       Person.Person.LastName,
       HumanResources.Employee.NationalIDNumber,
       HumanResources.Employee.JobTitle
FROM   person.Person
LEFT OUTER JOIN
      HumanResources.Employee
      ON person.BusinessEntityID = Employee.BusinessEntityID

这是查询的前几行结果：

请注意，第二行中 NationalIDNumber（国民身份号码）和 JobTitle（职位）的值为 NULL。这是因为没有与 BusinessEntityID 293（业务实体 ID 293）匹配的员工。

右外部联接

让我们再看一遍图表，但这次我们执行 right outer join（右外部联接）。您可能已经猜到，left outer join 和 right outer join 之间的 SQL 语句差异不大。right outer join 的基本语法是：

SELECT columnlist
FROM   table
RIGHT OUTER JOIN othertable ON join condition

以下是将我们的示例查询写为 right outer join 的代码：

SELECT person.Person.BusinessEntityID,
       Person.Person.LastName,
       HumanResources.Employee.NationalIDNumber,
       HumanResources.Employee.JobTitle
FROM   person.Person
RIGHT OUTER JOIN
      HumanResources.Employee
      ON person.BusinessEntityID = Employee.BusinessEntityID

关键区别在于，现在我们将返回 Person（人员）表中的所有记录，该表位于 join（联接）关键字的右侧。如果找不到匹配的 Employee（员工）记录，则 BusinessEntityID（业务实体 ID）和 LastName（姓氏）将返回 NULL。

以下是查询结果：

我滚动查看了所有结果，但惊讶地没有看到任何 null 值。

您知道为什么吗？

答案在于数据模型。Employee（员工）和 Person（人员）之间存在 0..1 对 1 的关系。这意味着每个 Employee（员工）都对应一个 Person（人员）。鉴于此，对于 right join（右联接），不会存在任何不匹配的行。使用这种关系，您也可以使用 inner join（内联接）。

左外部联接与右外部联接

左外部联接和右外部联接在功能上没有区别。

语句……

SELECT person.Person.BusinessEntityID,
       HumanResources.Employee.NationalIDNumber
FROM   person.Person
LEFT OUTER JOIN
       HumanResources.Employee
      ON person.BusinessEntityID = Employee.BusinessEntityID

……返回的结果与以下语句相同：

SELECT person.Person.BusinessEntityID,
       HumanResources.Employee.NationalIDNumber
FROM   HumanResources.Employee
RIGHT OUTER JOIN
      person.Person
      ON person.BusinessEntityID = Employee.BusinessEntityID

当然，如果我只将联接从 LEFT 改为 RIGHT 而未切换表名，情况就不是这样了。

我通常比 right outer join 更常使用 left outer join。我认为这是因为当我绘制关系图时，我倾向于从左到右绘制。另外，我脑海中也倾向于从左到右遍历表。

这与 SQL 的工作方式很契合，因为“左表”位于 FROM（来自）语句中。

我很好奇您使用哪种方式。我非常想知道，如果您是阿拉伯语或其他“从右到左”语言的原生使用者，right join（右联接）对您来说是否更直观。

全外部联接

full outer join（全外部联接）是 left（左）和 right outer join（右外部联接）结果的组合。这种类型的 join（联接）返回的结果包括两个表中的所有行。匹配项发生时，值会相关联。当任一表中的匹配项不存在时，则返回 NULL。

full outer join 的基本语法是：

SELECT columnlist
FROM   table
FULL OUTER JOIN othertable ON join condition

让我们来看一下 AdventureWork2012 数据库的不同部分。这次，我们将关注 SalesOrderHeader（销售订单头）和 CurrencyRate（货币汇率）表之间的关系。

模型显示如下：

假设我们想知道所有可以下单的货币以及在这些货币中下了哪些订单？

SELECT sales.SalesOrderHeader.AccountNumber,
       sales.SalesOrderHeader.OrderDate,
       sales.CurrencyRate.ToCurrencyCode,
       sales.CurrencyRate.AverageRate
FROM   sales.SalesOrderHeader
FULL OUTER JOIN
       sales.CurrencyRate
       ON sales.CurrencyRate.CurrencyRateID = 
          sales.SalesOrderHeader.CurrencyRateID

以下是结果的一部分，显示了一些销售与货币匹配，而另一些则未匹配。之所以存在未匹配的销售，是因为这些销售是以美元进行的。

在结果的更下方，您可以看到没有匹配销售的货币。这反映了这样一个事实，即这些货币没有发生销售。

注意：我惊讶地看到了美元（USD）的列表，请参见第 42463 行，因为我本以为大部分销售都是以美元进行的。我的想法是，SalesOrderHeader（销售订单头）中的 CurrencyRateID（货币汇率 ID）值对于所有美元交易都设置为 null，而不是引用这些交易的货币汇率。我认为这不一致，也不是我应该做的方式，但这毕竟不是我的数据库……

高级示例

到目前为止，我们已经了解了三种类型的外部联接，但尚未探讨一些更高级的概念，例如联接多个表和在联接子句中使用多个条件。

我们在探讨内联接时已经涵盖了这些概念，所以接下来我将展示的内容不应该太陌生，但我认为回顾一下仍然有意义，因为在某些情况下，混合使用全外部联接和内联接可能会产生意想不到或不期望的结果。

让我们将注意力转向生产模式（production schema），探索产品和类别。让我们生成一个包含所有产品类别和其中包含的产品型号的列表。

Product（产品）与 ProductModel（产品型号）和 ProductSubcategory（产品子类别）之间存在一对多关系。由于它位于这两个表之间，因此 ProductModel（产品型号）和 ProductSubcategory（产品子类别）之间存在隐式的一对多关系。因此，它是外部联接的良好候选对象，因为可能存在未分配产品的产品型号，以及没有产品的 ProductSubcategory（产品子类别）条目。

为了解决这种情况，我们将对 ProductModel（产品型号）和 ProductCategory（产品类别）表执行外部联接。

SQL 代码如下：

SELECT   PC.Name AS Category,
         PSC.Name AS Subcategory,
         PM.Name AS Model,
         P.Name AS Product
FROM     Production.Product AS P
FULL OUTER JOIN
         Production.ProductModel AS PM
         ON PM.ProductModelID = P.ProductModelID
FULL OUTER JOIN
         Production.ProductSubcategory AS PSC
         ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
 INNER JOIN
         Production.ProductCategory AS PC
          ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name

有几点需要注意：

  • 我使用了表别名来使 SQL 代码更易读。
  • 有多个 full outer join（全外部联接）子句。
  • ProductCategory（产品类别）表也是 outer join（外部联接）的一部分。

最初，当我编写此查询的 SQL 代码时，我在 ProductSubcategory（产品子类别）和 ProductCategory（产品类别）之间使用了一个内联接，但我没有看到预期的不匹配记录的 NULL 值。

当我将联接更改为 full outer join（全外部联接）后，我看到了期望的结果。发生这种情况的原因比较微妙。

检查数据后，我确认所有类别都已分配了子类别。鉴于此，您可能会认为内联接会起作用；但是，请考虑在整个语句执行并返回行时，只要产品未匹配产品子类别，ProductSubcategoryID（产品子类别 ID）的值就会为 NULL。

根据定义，Null 值彼此不相等，因此内联接失败。鉴于此，当这些值与 ProductCategory（产品类别）匹配时，除非与 ProductCategory（产品类别）的联接是 outer join（外部联接），否则它们不会包含在结果中。

事实上，联接不一定是 full outer join（全外部联接），left join（左联接）同样有效。

SELECT   PC.Name AS Category,
PSC.Name AS Subcategory,
PM.Name AS Model,
P.Name AS Product
FROM     Production.Product AS P
FULL OUTER JOIN
Production.ProductModel AS PM
ON PM.ProductModelID = P.ProductModelID
FULL OUTER JOIN
Production.ProductSubcategory AS PSC
ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
LEFT OUTER JOIN
         Production.ProductCategory AS PC
         ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name

外部联接的用途

由于 outer joins（外部联接）不仅包含匹配的行，还包含不匹配的行，因此它们是查找表中缺失条目的绝佳方式。当您需要对数据库进行诊断以确定是否存在数据完整性问题时，这非常有用。

例如，假设我们担心可能有一些 ProductSubcategory（产品子类别）条目未匹配 Categories（类别）。我们可以通过运行以下 SQL 进行测试：

SELECT   PSC.Name AS Subcategory
FROM     Production.ProductCategory AS PSC
LEFT OUTER JOIN
         Production.ProductSubcategory AS PC
         ON PC.ProductCategoryID = PSC.ProductCategoryID
WHERE PSC.ProductCategoryID is NULL

outer join 以 NULL 值返回不匹配的行值。where（其中）子句过滤掉非 null 值，只留下不匹配的 Subcategory（子类别）名称供我们审查。

Outer joins（外部联接）还可以用于提出以下类型的问题：

  • “有哪些销售人员从未进行过销售？”
  • “哪些产品未分配给产品型号？”
  • “哪些部门没有分配的员工？”
  • “列出所有未分配销售人员的销售区域。”
© . All rights reserved.