SQL SELF JOIN 自联接

SQL 自联接(SELF JOIN)是一个数据表和它本身的正常联接。

注: SQL 自联接可以是任何类型的联接:SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN, SQL FULL JOIN。 SQL 自联接(SELF JOIN)本身不是一个SQL关键词。

SQL 自联接(SELF JOIN) 语法

SELECT a.Column1, a.Column2, ... , b.Column3, b.Column4, ...
FROM 表1 a
INNER JOIN 表1 b
ON a.Column1 = b.Column2

SQL 自联接(SELF JOIN) 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary ManagerID
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800 203
205 Syllauu Dfaafk Finance 57000 203
302 Gecrrcc Srlkrt Development 62000 305
303 Tyshub Yunshee Development 58000 305
305 Hnnval Keevvs Development 79000

选择所有的职员(employee)和其经理(如果有的话):

SELECT a.FirstName
           , a.LastName
           , b.LastName AS Manager
FROM Employees a
LEFT JOIN Employees b
ON a.ManagerID = b.EmployeeId

结果会类似于:

FirstName LastName Manager
Mazojys Fxoj
Jozzh Lnanyo Fxoj
Syllauu Dfaafk Fxoj
Gecrrcc Srlkrt Keevvs
Tyshub Yunshee Keevvs
Hnnval Keevvs

选择所有的和Mazojys Fxoj在同一部门的职员(employee):

SELECT a.FirstName
           , a.LastName
           , a.Department
FROM Employees a
INNER JOIN Employees b
ON a.Department= b.Department
WHERE b.FirstName = 'Mazojys' AND b.LastName = 'Fxoj'

结果会类似于:

FirstName LastName Department
Mazojys Fxoj Finance
Jozzh Lnanyo Finance
Syllauu Dfaafk Finance