SQL 连接
4.40/5 (31投票s)
本文旨在介绍所有不同类型的 SQL 连接
我通常觉得人们对 SQL Server 中的连接（Join）感到害怕。但如果他们了解 SQL Server 中不同类型的连接是什么，以及如何最好地使用它们，他们会真正喜欢上使用连接。通过使用连接，我们可以根据一些逻辑条件从多个表中获取数据。
SQL Server 中不同类型的连接
- 内连接（Inner join）或等值连接（Equi join）
- 外连接（Outer Join）
- 交叉连接（Cross join）
假设我们有两个表，Employee（员工）和 Department（部门），其描述如下：-
CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Departmentid] [INT]
)
CREATE TABLE [dbo].[Department](
[Departmenttid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DepartmentName] [nvarchar](255) NOT NULL
)
创建表之后，我们需要向这些表中插入数据。使用以下查询来插入数据：-
insert into Employee
(EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A001','Samir','Singh','samir@abc.com',2,2)
insert into Employee
(EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A002','Amit','Kumar','amit@abc.com',1,1)
insert into Employee (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A003','Neha','Sharma','neha@abc.com',1,2)
insert into Employee (EmpNumber,EmpFirstName,EmpLastName,EmpEmail,Managerid,Departmentid)
values('A004','Vivek','Kumar','vivek@abc.com',1,NULL)
insert into Department(DepartmentName)
values('Accounts')
insert into Department(DepartmentName)
values('Admin')
insert into Department(DepartmentName)
values('HR')
insert into Department(DepartmentName)
values('Technology')
内连接（Inner Join）
这种类型的连接也称为等值连接（Equi join）。这种连接返回两个表中存在匹配的所有行。当我们需要仅选择在 ON 子句中指定的列中具有共同值的那些行时，可以使用这种类型的连接。
现在，如果我们想要获取那些至少属于一个部门的员工的员工ID、员工名字、员工姓氏及其部门名称，那么我们可以使用内连接。
内连接的查询
SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName
FROM Employee Emp
INNER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
结果
Empid EmpFirstName EmpLastName DepartmentName 1 Samir Singh Admin 2 Amit Kumar Accounts 3 Neha Sharma Admin
解释
在此查询中，我们基于“Departmentid”列（在“Employee”和“Department”两个表中都存在的公共列）使用了内连接。此查询将返回两个表中“Departmentid”列具有共同值的所有行。Neha Sharma 和 Samir Singh 在 Employee 表的 Departmentid 列中的值为“2”。在 Department 表中，部门“Admin”在 Departmentid 列中的值为“2”。因此，上述查询为“Admin”部门返回两行，一行是 Neha Sharma，另一行是 Samir Singh。
自连接（Self Join）
有时我们需要将一个表与其自身连接。这种类型的连接称为自连接（Self join）。它是内连接的一种，其中两个连接列都属于同一个表。在这种连接中，我们需要在内存中打开同一个表的两个副本。由于两个实例的表名相同，我们使用表别名来使同一个表的相同副本在不同的内存位置打开。例如，如果我们需要获取员工姓名及其经理姓名，我们就需要使用自连接，因为员工的经理ID也存储在与员工相同的表中。
自连接的查询
SELECT Emp1.Empid,
Emp1.EmpFirstName+' '+Emp1.EmpLastName as EmployeeName,
Emp2.EmpFirstName+' '+Emp2.EmpLastName as ManagerName
FROM Employee Emp1
INNER JOIN Employee Emp2
ON Emp1.Managerid=Emp2.Empid
结果
Empid EmployeeName ManagerName 1 Samir Singh Amit Kumar 2 Amit Kumar Samir Singh 3 Neha Sharma Samir Singh 4 Vivek Kumar Samir Singh
解释
由于员工和经理的信息都包含在同一个表（Employee 表，因为他们都是员工）中，我们必须使用自连接。在自连接查询中，我们通过使用别名 Emp1 和 Emp2 创建了 Employee 表的两个副本，然后通过使用 Emp1 的 managerid 列和 Emp2 表的 Empid 列在它们之间进行内连接。在此示例中，我们使用 Employee 表的 managerid 和 empid 列，因为员工的经理的员工ID存储在 Employee 表的 managerid 中。
外连接（Outer Join）
当我们需要从左侧（或右侧或两侧）的表中选择所有行，而不管另一个表是否有共同值时，就需要这种类型的连接，它通常会为缺失的数据输入 null 值。
外连接可以是三种类型
- 左外部联接
- 右外连接（Right Outer Join）
- 全外部联接
左外部联接
如果我们想获取所有员工的员工ID、员工名字、员工姓氏及其部门名称，而不管他们是否属于任何部门，那么我们可以使用左外连接。在这种情况下，我们将 Employee 表放在连接子句的左侧。它将为右表中缺失的数据插入 NULL 值。
左外连接的查询
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
LEFT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
结果
Empid EmpFirstName EmpLastName DepartmentName 1 Samir Singh Admin 2 Amit Kumar Accounts 3 Neha Sharma Admin 4 Vivek Kumar NULL
解释
由于我们使用了左外连接，此查询将从 Employee 表中给出所有员工的信息（员工ID、员工名字、员工姓氏及其部门名称），并在员工不属于任何部门的情况下，在 DepartmentName 列中插入 NULL 值。在 Employee 表中，由于 Samir Singh、Amit Kumar 和 Neha Sharma 在他们的 Departmentid 列中有值，因此上述查询将在 DepartmentName 标题下显示他们的部门名称。但由于 Vivek Kumar 不属于任何部门，并且在 Departmentid 列中为 null 值，因此上述查询将在 DepartmentName 列标题下显示 NULL 值。
右外连接（Right Outer Join）
如果我们想获取所有部门的名称以及属于该部门的所有员工的员工ID、员工名字和员工姓氏，而不管一个部门是否有员工，那么我们可以使用右外连接。在这种情况下，我们将 Department 表放在连接子句的右侧。它将为左表（Employee）中缺失的数据插入 NULL 值。
右外连接的查询
SELECT Dept.DepartmentName,
Emp.Empid, Emp.EmpFirstName,
Emp.EmpLastName
FROM Employee Emp
RIGHT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmentid
结果
DepartmentName Empid EmpFirstName EmpLastName Accounts 2 Amit Kumar Admin 1 Samir Singh Admin 3 Neha Sharma HR NULL NULL NULL Technology NULL NULL NULL
解释
由于我们使用了右外连接，此查询将根据 Departmentid 列中包含的值连接 Employee 和 Department 两个表。它将从 Department 表中给出部门名称，以及属于该部门的所有员工的员工ID、员工名字和员工姓氏。如果任何部门不包含任何员工，则它会在来自 Employee 表的列中插入 NULL 值。由于没有员工与 HR 和 Technology 部门相关联，此查询将在 HR 和 Technology 部门的 Empid、EmpFirstName 和 EmpLastName 列下显示 NULL 值。由于 Admin 和 Accounts 部门包含员工，因此 Empid、EmpFirstName 和 EmpLastName 列分别包含员工ID、员工名字和员工姓氏的信息。
全外部联接
如果我们想获取所有部门的名称以及所有员工的员工ID、员工名字、员工姓氏，而不管一个部门是否有员工，或者一个员工是否属于一个部门，那么我们可以使用全外连接。它将为两个表中缺失的数据插入 null 值。
全外连接的查询
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
FULL OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid
结果
Empid EmpFirstName EmpFirstName DepartmentName 1 Samir Singh Admin 2 Amit Kumar Accounts 3 Neha Sharma Admin 4 Vivek Kumar NULL NULL NULL NULL HR NULL NULL NULL Technology
解释
由于我们使用了全外连接，此查询将从 Department 表中给出所有部门的名称，并从 Employee 表中给出所有员工的员工ID、员工名字、员工姓氏。如果任何部门不包含任何员工，则它会在 Empid、EmpFirstName、EmpLastName 列中插入 NULL 值；如果任何员工不属于任何部门，则它会在 DepartmentName 列中插入 NULL 值。在这里，由于 Vivek Kumar 不属于任何部门，结果在 DepartmentName 列下显示 NULL 值。由于 HR 和 Accounts 部门不包含任何员工，上述查询的结果在 HR 和 Technology 部门的 Empid、EmpFirstName 和 EmpLastName 列下显示 NULL 值。
交叉连接（Cross Join）
这种连接将左表中的所有行与右表中的每一行进行组合。当我们需要从两个表中选择所有可能的行和列的组合时，就需要这种类型的连接。这种连接通常不被推荐，因为它需要很长时间并产生一个通常无用的巨大结果。
交叉连接的查询
SELECT Emp.Empid,
Emp.EmpFirstName,
Emp.EmpLastName,
Dept.DepartmentName
FROM Employee Emp
CROSS JOIN Department dept
结果
Empid EmpFirstName EmpLastName DepartmentName 1 Samir Singh Accounts 2 Amit Kumar Accounts 3 Neha Sharma Accounts 4 Vivek Kumar Accounts 1 Samir Singh Admin 2 Amit Kumar Admin 3 Neha Sharma Admin 4 Vivek Kumar Admin 1 Samir Singh HR 2 Amit Kumar HR 3 Neha Sharma HR 4 Vivek Kumar HR 1 Samir Singh Technology 2 Amit Kumar Technology 3 Neha Sharma Technology 4 Vivek Kumar Technology
解释
这个交叉连接查询将把 Employee 表中的所有行与 Department 表中的每一行进行组合。由于 Employee 表包含4行，Department 表包含4行，因此这个结果将返回 4*4=16 行。此查询不包含任何 ON 子句。
总结
以上讨论可以总结为：连接用于在单个查询中从多个表中选择数据。内连接用于仅选择那些在连接所基于的列中具有共同值的行。左外连接用于从左侧表中选择行，而不管右侧表是否有共同值。同样，右外连接用于从右侧表中选择行，而不管左侧表是否有共同值。交叉连接用于从两个表中获取所有可能的行和列组合。只有在没有其他办法时才应使用它，因为它可能会运行很长时间并返回一个可能无用的巨大结果集。
如果连接是基于具有很少唯一值的列，则应避免表的连接。为了提高连接性能，最好通过在查询中包含 WHERE 子句来限制需要连接的行数。如果用于连接表的列有自己的索引，连接性能也可以提高。