SQL JOIN

SQL Server 最重要的特性是 Join，它帮助您组合两个或多个表以执行各种操作。如果没有 Join，就不会有数据规范化的概念，因为我们必须将所有数据存储在一个表中。

在关系型数据库管理系统中，我们将标准信息存储在主表中，将其事务存储在子表中。父表和子表通过主键和外键（规范化）连接。RDMS 中的一项基本任务是组合来自多个表的数据并检索记录以获取完整信息或提取有意义的见解。SQL Joins 将为您完成这项工作！

在这篇博文中，我们将解释 SQL Join 的概念、语法、可用类型数量、连接多个表，以及它们与内连接之间的区别。

理解 SQL Join 的基础知识

SQL Server Join 有助于组合或选择来自两个或多个表的数据列，因为数据库使用外键将我们的数据存储在单独的表中。因此，对于任何简单的查询结果，即使您正在选择客户记录，我们也必须从至少两个表中选择数据。您必须使用公共列指定连接条件，以建立表之间的关系。

尽管 JOIN 和 UNION 都用于合并或组合来自多个表的数据，但它们完全不同。您可以使用 Join 组合列，使用 Union 组合来自多个表的行。SQL Server Inner、Full Outer、Left Outer、Right Outer、Self 子句和 Cross Join 的可视化表示如下：

SQL JOINS Diagrammatic Representation

SQL Server 中有多少种 Join？

SQL 提供了六种类型的 Join 来处理各种场景。每种 Join 类型都与其他类型不同，并提供独特的功能。其中，有三种类型的外部连接，它们是 Right、Left 和 Full Outer Join。

在以下几种中，前三种是主要的。以下是常见类型的简要概述：

  1. 内连接 (Inner Join) 或 Join 是实时中最常用的默认类型。它检索两个表中的匹配或公共记录。JOIN 是 INNER 的最简单形式，您可以使用它们中的任何一个来获得相同的结果。
  2. 左连接 (Left 或 Left Outer Join) 检索左表或第一个表中的所有记录，以及第二个表或右表中的匹配记录。如果右表中不存在匹配值，则返回 NULL 值。
  3. 右连接 (Right 或 Right Outer Join) 检索右表中的所有记录，以及左表中的匹配记录。如果左表中不存在匹配值，则返回 NULL 值。
  4. 全连接 (Full 或 Full Outer Join) 检索两个表中的所有匹配和不匹配行。如果不存在匹配值，将返回 NULL 值。
  5. 交叉连接 (Cross Join) 返回两个表的笛卡尔积，将第一个表中的每一行与第二个表中的每一行组合起来。
  6. 自连接 (Self Join) 连接自身以执行逐行计算。

SQL Join 语法

常见 Join 的语法如下。对于自连接，表 1 和表 2 将是相同的；您必须使用别名。您可以使用所示的任何 Join 来获取记录。例如，删除其余名称以获取交叉连接结果集。

SELECT Table1.Column(s), Table2.Column(s), .....
FROM Table1
INNER JOIN | LEFT JOIN | RIGHT JOIN | CROSS JOIN | SELF JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

对于此 Join 语句示例，我们使用两个表：Employees 和 Department。

部门

JOIN 语句中表的顺序如何影响查询结果？

这取决于您使用的 Join 类型。如果使用 Inner Join，JOIN 语句中表的顺序不会影响查询结果。但是，如果使用 Left Join 和 Right Join，如果更改表的顺序，结果集将完全不同。

不同类型的 SQL JOIN 示例

为了执行任何 Join，两个表都应该有一个公共列。否则，我们无法执行 Join。我们使用公共列作为连接表的条件。如果您观察上面两个表，Employees 和 Department 表中都有一个 DeptID 整数列，我们使用此列来执行 Join。

本节通过示例解释如何编写查询来连接两个或多个表。以下是一些常见问题。

SQL Server 内连接示例是什么？

内连接 (Inner Join) 是一种基本的连接类型，也称为 Join。它返回左右表中都存在的匹配行。否则，它返回零记录。通常，我们使用内连接来检索多个表中的公共记录。此示例展示了如何编写内连接。

此查询将根据“DeptID”列连接“Employees”和“Department”表，并返回名字、姓氏、教育程度、部门名称、年收入和销售额。尽管员工表中有 15 行，但 SQL 内连接只写入了 11 条记录，因为 EmpID 为 10、11、14 和 15 的 DeptID 为 NULL。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL Server Inner Join Example

SQL Server 中的左外连接是什么？

左连接，也称为左外连接 (Left Outer Join)，返回左侧表的所有行，以及右表中的匹配行（如果有）。如果右表中没有匹配值，则用 NULL 值填充右表列。

此示例展示了如何在 SQL Server 中编写左外连接以获取员工表中的所有员工及其对应的（匹配的）部门名称。EmpID 为 10、11、14 和 15 的员工没有相关的部门名称，因此这些行填充了 NULL 值。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

什么是 SQL 右连接？

它确保所有右表行都应包含在最终结果集中，无论左表中是否存在匹配记录。让我们看看如何编写带条件的右外连接。

SQL Server 右连接，也称为右外连接 (Right Outer Join)，返回右侧表中的所有行，以及左表中的匹配行（如果有）。如果左表中没有匹配记录，则用 NULL 值填充左表列。

此查询返回所有部门名称和匹配的员工。Module Lead 和 Team Lead 部门名称没有对应的员工，因此它们填充了 NULL。底部有一整行 NULL（第 14 条记录），因为 DeptID 为 9 的部门名称为 NULL，并且在员工中没有匹配的记录。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL Server Right Outer Join Example

SQL Server 全连接是什么？

当我们不需要考虑匹配，而需要查看两个表的完整数据时，全连接 (Full Join) 会很有用。此示例将向您展示如何编写全外连接。

全外连接 (Full Outer) 也称为全连接 (Full Join)。它检索左右表中所有行的数据，并且不考虑是否存在匹配值。对于两个表中所有不匹配的记录，它都填充 NULL 值。

此查询返回“Employees”和“Department”表中的所有记录。这意味着没有部门名称的员工和没有员工的部门 ID。如您在结果集中所见，它生成了 18 行。员工表中有 15 行，部门表中有三行额外行。它们是 Module Lead、Team Lead 和 NULL。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

什么是 SQL 自连接？

自连接用于将表与自身组合。当您想在同一表中的记录之间建立关系时，它会很有用。要使用自连接，您必须为表使用别名以区分并将同一表视为两个不同的实体。例如，我们可以使用自连接技术来计算累计总计等。

自连接中的条件决定了同一表的行应如何匹配。在此示例中，我们使用自连接将 Employees 表连接到自身。为此，我们使用别名 Emp 和 NewEmp 将同一表视为两个不同的实体。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,NewEmp.[YearlyIncome] + 25000 AS [New Income]
  FROM [Employees] AS Emp,
	[Employees] AS NewEmp
  WHERE Emp.EmpID = NewEmp.EmpID
Self Join Example

SQL Server 中的交叉连接是什么？

交叉连接 (Cross Join) 有助于返回两个表的笛卡尔积。它将第一个表中的每一行与第二个表中的每个记录组合起来。您不需要任何条件来使用它，因为它会生成所有组合。

此交叉连接示例展示了如何查找笛卡尔积。它组合了 Employees 和 Department 表，并返回每个员工与每个部门名称的配对。结果集将是 Employees 中的行数乘以 Department 中的行数。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept

歧义列是您在使用 Join 时可能遇到的最常见错误之一。使用别名表名选择列名以避免错误。通过这种方式，服务器可以识别该列属于哪个表。

高级 SQL Join 概念

非等值连接 (Non-Equi)、半连接 (Semi-joins) 和反连接 (Anti-joins) 是您应该练习以提升技能的高级概念。常规 Join 允许您组合来自不同表的数据，而这些高级连接提供了强大的过滤功能。

非等值连接

SQL 非等值连接类型在连接条件中使用比较运算符，例如 <、>、<=、>=、<>、!=、!<、!>。除了 Join 类型和用于建立关系的公共列之外，它还使用上述比较运算符。非等值连接有助于进一步筛选结果集。

下面的查询连接了 Employees 和 Department 表，非等值连接指示服务器显示销售额大于或等于 500 的员工。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
FROM [Employees] AS Emp
INNER JOIN [Department] AS Dept
ON Emp.DeptID = Dept.DeptID AND Emp.[Sales] >= 500
SQL Non-Equi Join

半连接

半连接类型只返回一个表中的记录。这些记录应该在第二个表中有一个匹配的记录。SQL 半连接在您希望显示左表中与右表中的值匹配的记录时很有用。它执行一个简单的比较，并充当过滤器以显示特定的一组行。

下面的半连接查询显示所有具有匹配部门名称的员工，该部门名称必须是 Software Engineer。为了实现此示例，我们使用了 EXISTS 运算符。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
FROM [Employees] AS Emp
WHERE EXISTS (
    SELECT 1 FROM
    [Department] AS Dept
    WHERE Emp.DeptID = Dept.DeptID
    AND Dept.[DepartmentName] = 'Software Developer'
)

反连接

反连接与半连接完全相反。这意味着反连接类型返回左表中没有在右表中匹配行的行。为了实现此示例，我们使用了 NOT EXISTS 运算符。

下面的反连接查询返回所有部门名称不是 Software Engineer 的员工。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
FROM [Employees] AS Emp
WHERE NOT EXISTS (
    SELECT 1 FROM
    [Department] AS Dept
    WHERE Emp.DeptID = Dept.DeptID
    AND Dept.[DepartmentName] = 'Software Developer'
)

我们可以在 SQL 中进行多重连接吗？

是的。单个查询可以包含相同或不同类型的多个连接。例如，我们可以在三个表上使用两个左外连接或两个内连接。这完全取决于我们需要的数据。

在实际操作中，连接多个表以检索所需信息是标准做法。例如，我们需要 Adventure Works DW 数据库产品信息。在这种情况下，我们必须连接 Product Category、Product SubCategory 和 DimProduct 表。如果您需要这些产品的 Internet Sales，则必须连接 Fact Internet Sales 表。

连接多个 SQL 表允许您使用公共列组合来自不同表的数据，以查看完整信息。在上述情况下，您可以使用 ProductSubcategoryKey 连接 DimProduct 和 DimProductSubcategory。类似地，使用 ProductCategoryKey 连接 DimProductSubcategory 和 DimProductCategory。要添加 FactInternetSales，请使用 FactInternetSales 和 DimProduct 中的 ProductKey。

以下是连接多个表的步骤。

  1. 识别包含所需信息的表。
  2. 检查这些表之间的关系。
  3. 检查主键和外键列。
  4. 识别这些表之间的公共列。必须有一个公共列。
  5. 识别要在 SELECT 语句中包含的所需列。
  6. 根据需求，您必须决定哪种连接类型适合连接任意两个表。例如，您可以使用内连接连接 DimProductCategory、DimProductSubcategory 和 DimProduct。在建立 DimProduct 和 FactInternetSales 之间的连接时，您可以考虑使用左连接或内连接。
  7. 您还可以将 ORDER BY、WHERE 和 TOP 子句与连接组合使用，以筛选和排序数据。

登录中存在的数据

在此示例中，我们在一个 SELECT 语句中使用了多个连接。我们对上述记录使用了 Inner 和 Left 连接。

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
	  ,logg.USERNAME
	  ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  LEFT JOIN [LogInTable] AS logg
  ON Emp.EmpID = logg.LogID
SQL Multiple Joins Example 9

我们可以在 SQL 中连接 3、5 和 10 个表吗？

是的，连接 3、5、10 个或更多表是可行的。但是，您应该记住一条黄金法则：随着表数量的增加，查询复杂性增加，性能下降。

在 SQL Server 中，您可以连接五个表以从五个不同的表中选择数据，以用于您的洞察或报告。在编写查询之前，您必须识别表之间的关系、公共列和连接类型。除此之外，您必须遵循连接多个表部分中提到的所有步骤。

下面的查询将连接 Adventure Works DW 数据库中的五个表，以查看客户产品信息和销售报告。它们是 DimProductCategory、DimProductSubcategory、DimProduct、DimCustomer 和 FactInternetSales。在这里，我们使用内连接来连接表。但是，您可以根据业务需求使用任何一种。

SELECT ProdCat.EnglishProductCategoryName, 
       ProdSubCat.EnglishProductSubcategoryName, 
       Prod.EnglishProductName, 
       Prod.Color, 
       Cust.FirstName, 
       Cust.LastName, 
       Cust.Gender, 
       Cust.YearlyIncome, 
       Fact.OrderQuantity, 
       Fact.TotalProductCost, 
       Fact.SalesAmount
FROM DimProductSubcategory AS ProdSubCat
INNER JOIN DimProduct AS Prod ON 
	ProdSubCat.ProductSubcategoryKey = Prod.ProductSubcategoryKey 
INNER JOIN DimProductCategory  AS ProdCat 
	ON ProdSubCat.ProductCategoryKey = ProdCat.ProductCategoryKey 
INNER JOIN FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey 
INNER JOIN DimCustomer AS Cust ON Fact.CustomerKey = Cust.CustomerKey
Joining Five Tables in SQL

我们可以在 JOIN 中使用 WHERE 子句吗？

是的，您也可以将 Where 子句与 SQL Server Joins 一起使用，以限制它返回的记录。类型决定了返回多少记录，WHERE 子句允许您应用额外的过滤。

在此示例中，我们使用 Inner Join 连接 Employee 和 Department 表。因此，它返回两个表中的公共行。然后，WHERE 子句将筛选结果集以显示年收入大于或等于 60000 的员工。

SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE YearlyIncome >= 60000
JOIN WHERE Clause Example 7

Join 和 WHERE 示例 2

这是另一个在 Where 子句中显示 Join 的示例。在这里，我们使用 Left 连接 Emp 和 Department 表。接下来，我们使用 Where 条件来显示学历不是学士的员工。

SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE Education <> 'Bachelors'

Where 子句和 IS NOT NULL 示例

我们使用 Right 连接 Employee 和 Department。接下来，在 SQL Server Right Join 的 Where 子句中，我们使用 IS NOT NULL 来显示 ID 不为 NULL 的记录。

SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL

使用 Full Join 组合表。接下来，在 Where 子句中

  • 我们使用 IS NOT NULL 来显示 ID 不为 NULL 的记录
  • AND 运算符用于添加另一个条件
  • Sales > 1000 显示销售额大于 1000 的员工
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL AND Sales > 1000

我们使用 Cross Join 和 Where 子句来连接多个表和条件。

SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  WHERE EmpID IS NOT NULL AND Sales > 3000
Cross Join with Where Clause Example 9

SQL Server Joins 与 Order By 和 Top 子句

您也可以将其与 Top 子句和 Order By 一起使用。如果您将 ORDER BY 子句与 Join 结合使用，则组合数据将按升序或降序排序。如果您使用 TOP 子句和任何 Join 类型组合，查询将根据给定值返回前 10 或 50 条记录。

在此示例中，我们使用 Inner 连接 Employees 和 Departments。接下来，我们使用 Order By 子句按年收入降序排序记录。最后，Top 子句将从查询结果中选择前 8 条记录。

SELECT TOP 8 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  ORDER BY YearlyIncome DESC

这是另一个体验 top 和 order by 以及组合来自多个表的数据的示例。

SELECT TOP 10 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  ORDER BY DepartmentName DESC

评论已关闭。