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

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类别

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.

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

Insert data into the Employee table with the following script.

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

USE [SQLShackDemo];
GO
SET IDENTITY_INSERT [dbo].[Employee] ON; 
GO
INSERT INTO [dbo].[Employee]
([EmpID], 
 [EmpName], 
 [City], 
 [Designat