SQL的SELF JOIN用法

前面介绍了如何使用JOIN子句，针对的是多个不同的表，使用公共的列，来进行信息的组合。

而对同一个表，也能使用JOIN功能。

下面我们就来介绍一下，什么是SELF JOIN，它是如何工作的，你什么时候需要用它。

In this article, we will discuss what a self join is, how it works, and when you need it in your SQL queries.

使用时的表名使用别名(Aliases)，用来区分同一表的不同内容。

语法如下：

SELECT column_name(s)

FROM table1 T1

JOIN table1 T2

ON condition;

或者不使用JOIN子句也能达到同样的效果：

SELECT column_name(s)

FROM table1 T1, table1 T2

WHERE condition;

举例如下：

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A

JOIN Customers B

ON A.CustomerID <> B.CustomerID

AND A.City = B.City

ORDER BY A.City;

或

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City

FROM Customers A, Customers B

WHERE A.CustomerID <> B.CustomerID

AND A.City = B.City

ORDER BY A.City;

上面的SQL语句，将同一城市中的不同ID的客户对都找出来。

不过这个例子的意义不是很明确，只是介绍了使用的语法。

下面这个例子表示的更清晰些，如下表数据：

Employees表

Id	FullName	Salary	ManagerId
1	John Smith	10000	3
2	Jane Anderson	12000	3
3	Tom Lanon	15000	4
4	Anne Connor	20000
5	Jeremy York	9000	1

这个表显示了员工数据，每个员工都有一个ID，并在后面加上了其主管的ID。这就是一个很普通的组织架构信息。

我们现在想显示每个员工的主管的名字信息，SQL语句如下：

SELECT

    employee.Id,

        employee.FullName,

        employee.ManagerId,

        manager.FullName as ManagerName

FROM Employees employee

JOIN Employees manager

ON employee.ManagerId = manager.Id

结果是：

Id	FullName	ManagerId	ManagerName
1	John Smith	3	Tom Lanon
2	Jane Anderson	3	Tom Lanon
3	Tom Lanon	4	Anne Connor
5	Jeremy York	1	John Smith

这个SQL查询(query)语句，将有主管的员工的信息都显示出来，并带上主管的ID和名字。

而这个员工表里的信息，虽然每条记录的信息格式一样，但本身是分类的，分为员工和主管两种角色。

所以当我们执行SELF JOIN时，实际上是同一个表，但形式上分别作为员工和主管两张表。

在表达上，我们使用表的别名，用来区分表的不同角色。

注意上面员工ID为4时，没有主管，如果要显示所有的员工，就要使用LEFT JOIN。

SELECT

    employee.Id,

        employee.FullName,

        employee.ManagerId,

        manager.FullName as ManagerName

FROM Employees employee

LEFT JOIN Employees manager

ON employee.ManagerId = manager.Id

结果如下：

Id	FullName	ManagerId	ManagerName
1	John Smith	3	Tom Lanon
2	Jane Anderson	3	Tom Lanon
3	Tom Lanon	4	Anne Connor
4	Anne Connor
5	Jeremy York	1	John Smith

而默认的JOIN是INNER JOIN，在上面用普通的FROM ... WHERE子句也可以替换。

但如果是LEFT、RIGHT、FULL JOIN，就没用用FROM...WHERE子句来做了。

SLEF JOIN的功能：

1，处理分级数据(Hierarchy)

同一个表中的数据，某行数据和其他行数据有等级关系。你可以当作父子关系。

通过LEFT JOIN，来将这种关系显示出来。

除了上面的员工与主管，还可以是Human表中的父子关系：

Human表

Id	Name	Age	ParentId
1	Jonathan	5	3
2	Alexandra	7	3
3	Barbara	30

运行SQL：

SELECT

    child.Id as ChildId,

        child.FirstName as ChildFirstName,

        child.Age as ChildAge,

        child.ParentId,

        parent.FirstName as ParentFirstName,

        parent.age as ParentAge

FROM Human child

INNER JOIN Human parent

ON child.ParentId = parent.Id

可以使用SELF JOIN的到父子关系的结果集：

ChildId	ChildFirstName	ChildAge	ParentId	ParentFirstName	ParentAge
1	Jonathan	5	3	Barbara	30
2	Alexandra	7	3	Barbara	30

还有类别的包含关系，如下面的食品分类：

Category表

Id	Quantity	Category	ParentCategoryId
1	60	Food
2	50	Fruit	1
3	40	Apple	2
4	20	Granny Smith	3
5	100	Milk	1
6	60	Soy Milk	5
7	40	Cow Milk	5
8	30	Whole Milk	7
9	10	Fat-Free Milk	7

运行SQL:

SELECT

    category.Id,

    category.Quantity,

        category.Category,

        category.ParentCategoryId,

        parentcategory.Category as ParentCategory

FROM Category category

JOIN Category parentcategory

ON category.ParentCategoryId = parentcategory.Id

得到结果：

Id	Quantity	Category	ParentCategoryId	ParentCategory
2	50	Fruit	1	Food
3	40	Apple	2	Fruit
4	20	Granny Smith	3	Apple
5	100	Milk	1	Food
6	60	Soy Milk	5	Milk
7	40	Cow Milk	5	Milk
8	30	Whole Milk	7	Cow Milk
9	10	Fat-Free Milk	7	Cow Milk

所以这SELF JOIN的第一个功能，就是一列作为ID，是唯一标识，另一列标识所属关系的ID，可以重复。

数据结构其实是一个层层分类的级别图。

在执行SELF JOIN时，对应关系是一对一的，结果集数目是小于或等于第一列ID数目的。

2，显示表中的配对关系

比如下面的colleagues表：

Id	FullName	Age
1	Bart Thompson	43
2	Catherine Anderson	44
3	John Burkin	35
4	Nicole McGregor	29

假如我们想生成一个表，要列出所有的一对同事的可能性，这样每个人都有机会互相能够交谈的话：

SELECT

    teammate1.FullName as Teammate1FullName,

    teammate1.Age as Teammate1Age,

        teammate2.FullName as Teammate2FullName,

    teammate2.Age as Teammate2Age

FROM Colleagues teammate1

CROSS JOIN Colleagues teammate2

ON teammate1.FullName <> teammate2.FullName

结果就是：

Teammate1FullName	Teammate1Age	Teammate2FullName	Teammate2Age
Catherine Anderson	44	Bart Thompson	43
John Burkin	35	Bart Thompson	43
Nicole McGregor	29	Bart Thompson	43
Bart Thompson	43	Catherine Anderson	44
John Burkin	35	Catherine Anderson	44
Nicole McGregor	29	Catherine Anderson	44
Bart Thompson	43	John Burkin	35
Catherine Anderson	44	John Burkin	35
Nicole McGregor	29	John Burkin	35
Bart Thompson	43	Nicole McGregor	29
Catherine Anderson	44	Nicole McGregor	29
John Burkin	35	Nicole McGregor	29

这里的结果，去掉了自己和自己进行的配对。这个关系和前面的一对多不同，就变成了多对多。这就变成了乘积关系。

注意这里使用了关键字：CROSS JOIN，效果和JOIN是一样的，只是表示起来就是笛卡尔乘积的意思。

比如：

SELECT * FROM  table1 CROSS JOIN table2;

这个结果就是将table1和table2的记录相乘，进行分别匹配。

3，多表组合时，对同一个表的多次JOIN

进行表间数据组合时，有时需要加入的多个信息同属于同一个表，这是就要JOIN同一个表多次。

如下表：

Airport 机场信息

AirportId	Country	City
1	USA	New York
2	Canada	Toronto
3	Germany	Frankfurt
4	France	Paris
5	Italy	Rome

Flight 航班

FlightId	AirplaneId	StartTimestamp	EndTimestamp	StartAirportId	EndAirportId
2	555877	2020-01-14 13:00:00	2020-01-14 15:00:00	3	4
3	222536	2020-02-04 01:00:00	2020-02-04 16:00:00	1	5
4	111745	2020-02-15 09:00:00	2020-02-15 12:00:00	5	4
5	777524	2020-02-24 03:00:00	2020-02-24 19:00:00	4	2
6	888521	2020-03-25 10:00:00	2020-03-25 12:00:00	2	1
7	444937	2020-04-01 00:00:00	2020-04-01 17:00:00	3	1
243	111654	2020-01-01 02:00:00	2020-01-01 04:00:00	1	2

为了显示航班的起点和终点信息，就要JOIN机场这个表两次：

SELECT

        flight.FlightId,

        flight.AirplaneId,

        flight.StartAirportId,

        startairport.Country as StartAirportCountry,

        startairport.City as StartAirportCity,

        flight.EndAirportId,

        endairport.Country as EndAirportCountry,

        endairport.City as EndAirportCity

FROM Flight flight

JOIN Airport startairport

ON flight.StartAirportId = startairport.AirportId

JOIN Airport endairport

ON flight.EndAirportId = endairport.AirportId

得到结果：

FlightId	AirplaneId	StartAirportId	StartAirportCountry	StartAirportCity	EndAirportId	EndAirportCountry	EndAirportCity
1	111654	1	USA	New York	2	Canada	Toronto
2	555877	3	Germany	Frankfurt	4	France	Paris
3	222536	1	USA	New York	5	Italy	Rome
4	111745	5	Italy	Rome	4	France	Paris
5	777524	4	France	Paris	2	Canada	Toronto
6	888521	2	Canada	Toronto	1	USA	New York
7	444937	3	Germany	Frankfurt	1	USA	New York

参考：

What Is a Self Join in SQL? An Explanation With Seven Examples | LearnSQL.com

LearnSQL.com提供one-stop-shop，一站式的SQL学习服务。