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学习服务。
相关文章
- ASP .NET 如何在 SQL 查询层面实现分页
- [SQL] sql server中如何查看执行效率不高的语句
- Last_SQL_Error: Error 'Duplicate entry '1' for key 'PRIMARY''
- SQL Server 触发器
- [SQL] sql server中如何查看执行效率不高的语句
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- SQL Server 2008 R2 新建用户并指定该用户的数据库
- sql 语句的limit的用法
- Atitit 数据join 的原理与java实现 Atitit join表连接的原理与实现 13、SQL Server 表连接的三种方式 (1) Merge Join (2) Nested
- SQL基础【二十、索引】(超细致版本,前理论,后实践,应对sql面试绰绰有余)
- 大约SQL/NoSQL数据库搜索/思考查询
- 009-Hadoop Hive sql语法详解4-DQL 操作:数据查询SQL-select、join、union、udtf
- Dynamics CRM Microsoft SQL Server 指定的数据库具有更高的版本号
- 嵌套SQL语句訪问DB2中SQLCA的调用技巧
- 94.第十九章 MySQL数据库 -- SQL语言分类和常见SQL用法(四)
- sql array 数组基本用法(四)
- sql 精读(四) 标准 SQL 中聚合分析功能示例
- sql语法:inner join on, left join on, right join on具体用法
- SQL 中With as 的用法