SQL OUTER JOIN概述和示例

This article will provide a full overview, with examples of the SQL Outer join, including the full, right and left outer join as well as cover the union between SQL left and right outer joins.

本文将提供完整的概述，并提供SQL外连接示例，包括完整，右侧和左侧外部连接，以及SQL左侧和右侧外部连接之间的联合。

It is essential to understand the process to get the data from the multiple tables. A beginner might not have the idea of Joins in SQL Server. In this tip, we will take an overview of the SQL joins, learn SQL OUTER JOIN along with its syntax, examples, and use cases.

必须了解从多个表中获取数据的过程。 初学者可能没有在SQL Server中加入连接的想法。 在本文中，我们将概述SQL连接，学习SQL OUTER JOIN及其语法，示例和用例。

In a relational database system, it is best practice to follow the principles of Normalization, in which, basically, we split large tables into the smaller tables. In a select statement, we can retrieve the data from these tables using joins. We can join the tables and get the required fields from these tables in the result set. These tables should have some common field to relate with each other. You might find data split across multiple databases and sometimes it is a very complex structure as well. With Joins, we can join the data together from the multiple tables, databases into a user-friendly way and represent this data in the application.

在关系数据库系统中，最佳实践是遵循规范化原则，在该原则中，我们基本上将大表拆分为较小的表。 在select语句中，我们可以使用联接从这些表中检索数据。 我们可以联接表并从结果集中的这些表中获取必填字段。 这些表应具有一些相互关联的公共字段。 您可能会发现数据分散在多个数据库中，有时它也是一个非常复杂的结构。 使用Joins，我们可以将来自多个表，数据库的数据连接在一起，以一种用户友好的方式进行，并在应用程序中表示该数据。

We can represent a SQL JOIN using the following image

我们可以使用下图表示一个SQL JOIN

SQL JOIN

We can many SQL Join types in SQL Server. In the following image, you can see SQL Joins categories

我们可以在SQL Server中使用许多SQL Join类型。 在下图中，您可以看到SQL Joins类别

SQL Join types

Let’s explore SQL Outer Join in details in the upcoming section.

让我们在接下来的部分中详细探讨SQL外连接。

SQL OUTER JOIN概述 (Overview of the SQL OUTER JOIN)

We use the SQL OUTER JOIN to match rows between tables. We might want to get match rows along with unmatched rows as well from one or both of the tables. We have the following three types of SQL OUTER JOINS.

我们使用SQL OUTER JOIN来匹配表之间的行。 我们可能想从一个或两个表中获取匹配行以及不匹配行。 我们有以下三种类型SQL OUTER JOINS。

  1. SQL Full Outer Join

    SQL Full外连接
  2. SQL Left Outer Join

    SQL左外部联接
  3. SQL Right Outer Join

    SQL右外部联接

Let’s explore each of SQL Outer Join with examples.

让我们通过示例探索每个SQL Outer Join。

SQL Full外连接 (SQL Full Outer Join)

In SQL Full Outer Join, all rows from both the tables are included. If there are any unmatched rows, it shows NULL values for them.

在SQL Full Outer Join中，两个表中的所有行都包括在内。 如果有任何不匹配的行，则显示它们的NULL值。

We can understand efficiently using examples. Let’s create a sample table and insert data into it.

我们可以使用示例来有效地理解。 让我们创建一个示例表并将数据插入其中。

CREATE TABLE [dbo].[Employee](
  [EmpID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  [EmpName] [varchar](50) NULL,
  [City] [varchar](30) NULL,
  [Designation] [varchar](30) NULL]
)
 
CREATE TABLE Departments
(EmpID          INT  PRIMARY KEY CLUSTERED , 
 DepartmentID  INT, 
 DepartmentName VARCHAR(50)
);

You can refer the following data model of both the tables.

您可以参考以下两个表的数据模型。

Table models

Insert data into the Employee table with the following script.

使用以下脚本将数据插入Employee表。

Sample data
USE [SQLShackDemo];
GO
SET IDENTITY_INSERT [dbo].[Employee] ON; 
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(1, 
 N'Charlotte Robinson', 
 N'Chicago', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(2, 
 N'Madison Phillips', 
 N'Dallas', 
 N'Senior Analyst'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(3, 
 N'Emma Hernandez', 
 N'Phoenix', 
 N'Senior Analyst'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(4, 
 N'Samantha Sanchez', 
 N'San Diego', 
 N'Principal Conultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(5, 
 N'Sadie Ward', 
 N'San Antonio', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(6, 
 N'Savannah Perez', 
 N'New York', 
 N'Principal Conultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(7, 
 N'Victoria Gray', 
 N'Los Angeles', 
 N'Assistant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(8, 
 N'Alyssa Lewis', 
 N'Houston', 
 N'Consultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(9, 
 N'Anna Lee', 
 N'San Jose', 
 N'Principal Conultant'
);
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designation]
)
VALUES
(10, 
 N'Riley Hall', 
 N'Philadelphia', 
 N'Senior Analyst'
);
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF;
GO

Insert Data into the Departments table

将数据插入“部门”表

Sample data
USE [SQLShackDemo];
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(1, 
 0, 
 N'Executive'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(2, 
 1, 
 N'Document Control'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(3, 
 2, 
 N'Finance'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(4, 
 3, 
 N'Engineering'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(5, 
 4, 
 N'Facilities and Maintenance'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(6, 
 2, 
 N'Finance'
);
GO
INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(10, 
 4, 
 N'Facilities and Maintenance'
);
GO

We can represent a logical relationship between two tables using a Venn diagram. In a Venn diagram contains multiple overlapping circles and each circle represents an entity or table. The common area or overlapping area in Venn diagram represents the common values between both tables.

我们可以使用维恩图表示两个表之间的逻辑关系。 在维恩图中，包含多个重叠的圆圈，每个圆圈代表一个实体或表格。 Venn图中的公共区域或重叠区域表示两个表之间的公共值。

For example, in the following screenshot, we have two overlapping circles. Each circle resent a table (Employee and Departments). Let’s understand the FULL Outer Join using the following example.

例如，在以下屏幕截图中，我们有两个重叠的圆圈。 每个圈子都寄出一张表格（员工和部门）。 让我们使用以下示例了解“完全外部联接”。

We have a common field ( EmpID) in both the tables; therefore, we can join the table with this column. In the following query, we defined the FULL OUTER JOIN between departments and Employee table on the EMPID column of both the table.

两个表中都有一个公共字段（EmpID）。 因此，我们可以将此表与该列连接。 在以下查询中，我们在两个表的EMPID列上的部门和Employee表之间定义了FULL OUTER JOIN。

SELECT *
FROM Employee
     FULL OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID;

SQL Full Outer Join

SQL Full Outer Join gives following rows in an output

SQL Full Outer Join在输出中给出以下行

  • Matching Rows between both the tables

    两个表之间的匹配行
  • Unmatched Rows from both the tables (NULL values)

    两个表中不匹配的行（NULL值）

Let’s execute this query to return Full Outer Join query output. We get the following output.

让我们执行此查询以返回“完全外部联接”查询输出。 我们得到以下输出。

We can see a few records with NULL values as well. Let’s understand this in a better way using a Venn diagram.

我们还可以看到一些带有NULL值的记录。 让我们使用维恩图更好地理解这一点。

In the following screenshot, you can see the following information

在以下屏幕截图中，您可以看到以下信息

  • EmpID 1, 2,3,4,5,6,10 exists in both Employee and Departments table. In Full Outer Join query output, we get all these rows with data from both the tables

    雇员表和部门表中都存在EmpID 1、2、3、4、5、6、10。 在“完全外部联接”查询输出中，我们从两个表中获取了所有这些行以及数据
  • EmpID 7, 8, 9 exists in the Employee table but not in the Departments table. It does not include any matching rows in the departments table; therefore; we get NULL values for those records

    EmpID 7、8、9存在于Employee表中，但不存在于Departments表中。 它在Departments表中不包含任何匹配的行； 因此; 我们获得这些记录的NULL值

SQL Full Outer Join example

Now, for demo purpose let’s insert one more record in Departments tables. In this query, we insert EmpID 11 that does not exist in the Employee table.

现在，出于演示目的，让我们在Departments表中再插入一条记录。 在此查询中，我们插入Employee表中不存在的EmpID 11。

INSERT INTO [dbo].[Departments]
([EmpID], 
 [Department_ID], 
 [DepartmentName]
)
VALUES
(11, 
 4, 
 N'Facilities and Maintenance'
);
GO

Rerun the SQL Full Outer Join query. In the following image, you get one additional row with NULL values. We do not have any matching row for EmpID 11 in the employee table. Due to this, we get NULL values for it in the output.

重新运行SQL Full Outer Join查询。 在下图中，您将获得另外一行具有NULL值的行。 在employee表中没有与EmpID 11相匹配的行。 因此，我们在输出中获得了NULL值。

SQL Full Outer Join example

As a summary, we can represent the SQL Full Outer Join using the following Venn diagram. We get what is represented in the highlighted area in the output of Full Outer Join.

概括来说，我们可以使用以下维恩图表示SQL Full Outer Join。 我们得到“完全外部联接”输出中突出显示的区域中表示的内容。

SQL Full Outer Join

SQL FULL OUTER JOIN和WHERE子句 (SQL FULL OUTER JOIN and WHERE clause )

We can add a WHERE clause with a SQL FULL OUTER JOIN to get rows with no matching data between the both Join tables.

我们可以添加带有SQL FULL OUTER JOIN的WHERE子句，以获取两个Join表之间没有匹配数据的行。

In the following query, we add a where clause to show only records having NULL values.

在以下查询中，我们添加where子句以仅显示具有NULL值的记录。

SELECT *
FROM Employee
     FULL OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID
WHERE Employee.EmpID IS NULL
      OR Departments.EmpID IS NULL;

Execute this command and view the output. It only returns rows that do not match either in Employee or Departments table.

执行此命令并查看输出。 它仅返回在Employee或Departments表中不匹配的行。

SQL Full Outer Join example

SQL左外连接 (SQL LEFT OUTER JOIN)

In a SQL Left Outer Join, we get following rows in our output.

在SQL左外部联接中，我们在输出中得到以下行。

  • It gives the output of the matching row between both the tables

    它给出两个表之间匹配行的输出
  • If no records match from the left table, it also shows those records with NULL values

    如果左表中没有记录匹配，则还会显示那些具有NULL值的记录

Execute the following code to return SQL LEFT OUTER JOIN output

执行以下代码以返回SQL LEFT OUTER JOIN输出

SELECT *
FROM Employee
     LEFT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID

In the following image, you can see we have NULL values for EmpID 7,8 and 9. These EmpID does not exist in the right side Department table.

在下图中，您可以看到EmpID 7,8和9具有NULL值。这些EmpID在右侧Department表中不存在。

SQL LEFT OUTER JOIN

We need to note the table placement position in the Join statement. Currently, we have an Employee table on the left side and Departments table in Right side.

我们需要注意Join语句中表的放置位置。 当前，我们在左侧有一个Employee表，在右侧有Departments表。

Let’s rewrite query and swap the position of tables in query. In this query, we have the Department table in left position, so the Left Outer Join should check the values for this table and return a NULL value in case of a mismatch.

让我们重写查询并交换表在查询中的位置。 在此查询中，我们的Department表位于左侧，因此Left Outer Join应该检查该表的值，并在不匹配的情况下返回NULL值。

In the following screenshot, you can see that only one NULL value for EmpID 11. It is because EmpID 11 is not available in the Employee table.

在下面的屏幕快照中，您可以看到EmpID 11只有一个NULL值。这是因为EmpID 11在Employee表中不可用。

SQL LEFT OUTER JOIN

As a summary, we can represent SQL Left Outer Join using the following Venn diagram. We get the highlighted area in the output of SQL Left Outer Join.

作为总结，我们可以使用下面的维恩图表示SQL Left Outer Join。 我们在SQL Left Outer Join的输出中获得了突出显示的区域。

SQL LEFT OUTER JOIN

SQL Right外连接 (SQL Right OUTER JOIN)

In SQL Right Outer Join, we get the following rows in our output.

在SQL Right Outer Join中，我们在输出中获得以下行。

  • It gives the output of the matching row between both the tables

    它给出两个表之间匹配行的输出
  • If no records match from the right table, it also shows those records with NULL values

    如果右表中没有记录匹配，则还显示那些具有NULL值的记录

Execute the following query to get the output of Right Outer Join

执行以下查询以获取Right Outer Join的输出

SELECT *
FROM Employee
     RIGHT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID

In the following image, you can see we get all matching rows along with one row with NULL values. Null value row has EmpID 11 because it does not exist in the Employee table. You can also notice the position of the Department table is in the right position in Join. Due to this, we do not get values from the Employee table (left position) which does not match with Department table (Right side).

在下图中，您可以看到我们得到了所有匹配的行以及带有NULL值的一行。 空值行的EmpID为11，因为它在Employee表中不存在。 您还可以注意到“部门”表的位置在“联接”中的正确位置。 因此，我们无法从Employee表（左侧）获得的值与Department表（右侧）不匹配。

SQL Right OUTER JOIN

As highlighted earlier, the table position is important in the JOIN statement. If we change the table positions, we get different output. In the following query, we have Departments table (Left) and Employee table (Right).

如前所述，表位置在JOIN语句中很重要。 如果更改表的位置，则会得到不同的输出。 在以下查询中，我们具有Departments表（左）和Employee表（右）。

SELECT *
FROM Departments
     RIGHT OUTER JOIN Employee ON Departments.EmpID = Employee.EmpID
 

You can notice the difference in Right Outer Join after swapping tables positions in above query.

在上述查询中交换表位置后，您会注意到在右外部联接中的差异。

SQL Right OUTER JOIN

As a summary, we can represent the SQL Right Outer Join using the following Venn diagram. We get highlighted area in the output of SQL Right Outer Join.

作为总结，我们可以使用下面的维恩图表示SQL Right Outer Join。 我们在SQL Right Outer Join的输出中突出显示了区域。

Right Outer Join

SQL左外部联接和SQL右外部联接之间的联合 (The union between SQL Left Outer Join and SQL Right Outer Join)

In the previous examples, we explored the SQL Left Outer Join, and the SQL Right Outer Join with different examples. We can do a Union of the result of both SQL Left Outer Join and SQL Right Outer Join. It gives the output of SQL Full Outer Join.

在前面的示例中，我们使用不同的示例探讨了SQL Left Outer Join和SQL Right Outer Join。 我们可以对SQL Left Outer Join和SQL Right Outer Join的结果进行并集。 它给出了SQL Full Outer Join的输出。

Execute the following query as an alternative to SQL Full Outer Join.

执行以下查询，以替代SQL Full Outer Join。

SELECT *
FROM Employee
     LEFT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID
   UNION ALL
   SELECT *
FROM Employee
     RIGHT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID

In the following output, we get all matching records, unmatch records from the left table and unmatch records from the right table. It is similar to an output of SQL Full Outer Join.

在以下输出中，我们获得所有匹配的记录，左表中的不匹配记录和右表中的不匹配记录。 它类似于SQL Full Outer Join的输出。

SQL JOIN

结论 (Conclusion)

In this article, we explored the SQL Outer Join and its types along with examples. I hope you found this article helpful. Feel free to provide feedback in the comments below.

在本文中，我们探讨了SQL外连接及其类型以及示例。 希望本文对您有所帮助。 请随时在下面的评论中提供反馈。

翻译自: https://www.sqlshack.com/sql-outer-join-overview-and-examples/