SQL Selft Join 自连接

时间:2018-11-15 17:47:07  来源:igfitidea点击:

在本教程中,我们将学习如何使用一种称为SQL self join的特殊连接将表连接到自身。

SQL自连接介绍

SQL 自连接(self join)用于联接表或将表与自身进行比较。SQL自联接用于比较同一表中一列的值与另一列的值。

若要构造自联接,请通过使用带有内部联接或外部联接子句的select语句从同一表中选择两次。由于在同一语句中两次引用同一表,因此必须使用表别名。

以下是自联接的语法:

SELECT 
    column1, column2,...
FROM
    table AS A
(LEFT | INNER) JOIN
    table AS B ON join_condition

SQL自连接示例

我们将使用employees表进行演示。

employees表不仅存储员工数据,还存储组织结构。reportsto列指定员工的经理,并引用同一表中的employeeid列。

下面说明employees表中的数据,包括employee id、last name、first name和reports to:

SELECT 
    employeeid, lastname, firstname, reportsto
FROM
    employees;

使用内连接的SQL自连接示例

当您希望选择存储在一个表中的相关数据(如组织结构)时,SQL自连接非常有用。

例如,要显示谁向谁报告,可以像以下查询一样将employees表连接到其自身:

SELECT 
    concat(e.firstname, e.lastname) employee,
    concat(m.firstname, m.lastname) manager
FROM
    employees e
INNER JOIN
    employees m ON m.employeeid = e.reportsto;

使用左连接的SQL自连接示例

最高管理者,即CEO,不向公司中的任何人报告,因此report to列包含空值。
要查询包括CEO在内的整个组织结构,需要使用LEFT JOIN子句而不是INNER JOIN子句,就像下面的查询:

SELECT 
    concat(e.firstname, e.lastname) employee,
    concat(m.firstname, m.lastname) manager
FROM
    employees e
LEFT JOIN
    employees m ON m.employeeid = e.reportsto
ORDER BY manager;

通过使用自连接,你可以按任意列将行与employees表中的其他行进行比较。

例如,以下查询查找位于同一城市的员工。

SELECT 
    e1.firstname, e2.firstname, e1.city
FROM
    employees e1
INNER JOIN
    employees e2 ON e2.city = e1.city
WHERE
    e1.employeeid <> e2.employeeid
ORDER BY 
    e1.city , e1.firstname;

要查找与Michael位于同一城市的员工,请在上面查询语句的WHERE子句中添加以下条件:

SELECT 
    e1.firstname, e2.firstname, e1.city
FROM
    employees e1
INNER JOIN
    employees e2 ON e2.city = e1.city
WHERE
    e1.employeeid <> e2.employeeid AND 
    e1.firstname = 'Michael'   
ORDER BY 
    e1.city , e1.firstname;