自连接

自连接

自连接就是和自身连接.^[2] 下面的例子是一个很好的说明.

示例

构建一个查询, 它试图找到这样的记录: 每条记录包含两个雇员, 他们来自于同一个国家. 如果你有两张雇员表(Employee), 那么只要第一张表的雇员和第二张表的雇员在同样的国家的就行了, 你可以用一个通常的连接(相等连接)操作去得到这个表. 不过, 这里所有雇员信息都在一张单独的大表里.^[3]

下面一个修改过的雇员表 Employee:

雇员表 (Employee)

EmployeeID	LastName	Country	DepartmentID
123	Rafferty	Australia	31
124	Jones	Australia	33
145	Steinberg	Australia	33
201	Robinson	United States	34
305	Smith	United Kingdom	34
306	Jasper	United Kingdom	NULL

示例解决方案的查询可以写成如下:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID <</span> S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

它执行后将生成下面的表:

通过 Country 自连接后的雇员表(Employee)

EmployeeID	LastName	EmployeeID	LastName	Country
123	Rafferty	124	Jones	Australia
123	Rafferty	145	Steinberg	Australia
124	Jones	145	Steinberg	Australia
305	Smith	306	Jasper	United Kingdom

关于这个例子, 请注意:

• ); color: rgb(37, 37, 37); font-family: sans-serif; line-height: 22.3999996185303px; background-color: rgb(255, 255, 255);">
• F 和 S 是雇员表(employee)的第一个和第二个拷贝的别名
• 条件 F.Country = S.Country 排除了在不同国家的雇员的组合. 这个例子仅仅期望得到在相同国家的雇员的组合.
• 条件 F.EmployeeID < S.EmployeeID 排除了雇员号(EmployeeID)相同的组合.
• F.EmployeeID < S.EmployeeID 排除了重复的组合. 没有这个条件的话, 将生成类似下面表中的无用数据(仅以 United Kingdom 为例)

EmployeeID	LastName	EmployeeID	LastName	Country
305	Smith	305	Smith	United Kingdom
305	Smith	306	Jasper	United Kingdom
306	Jasper	305	Smith	United Kingdom
306	Jasper	306	Jasper	United Kingdom

只有当中的两行满足最初问题的要求, 第一项和最后一项对于本例来讲毫无用处.

替代方式

外连接查询得到的结果也可以通过关联子查询得到. 例如

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM   employee LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

也可以写成如下样子:

SELECT employee.LastName, employee.DepartmentID,
  (SELECT department.DepartmentName 
    FROM department
   WHERE employee.DepartmentID = department.DepartmentID )
FROM   employee