SQL自连接

作者：--    发布时间：2019-11-20

本教程将向您展示如何使用sql自连接技术将表连接到自身。

1. sql自连接简介

有时，将表连接到自身是很有用的。 这种类型的连接称为自连接。

我们将一张表连接到自身来评估同一个表中其他行的行。 要执行自联接，我们使用内连接或左连接子句。

因为同一张表在单个查询中出现两次，所以必须使用表别名。 以下语句说明了如何将表连接到自身。

select
    column1,
    column2,
    column3,
        ...
from
    table1 a
inner join table1 b on b.column1 = a.column2;

在此语句中，使用inner join子句将table1连接到自身。 a和b是table1的表别名。 b.column1 = a.column2是连接条件。

除了inner join子句，还可以使用left join子句。

下面举几个使用自连接技术的例子。

2. sql自联接示例

请参阅以下employees表的结构。

mysql> desc employees;
+---------------+--------------+------+-----+---------+----------------+
| field         | type         | null | key | default | extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int(11)      | no   | pri | null    | auto_increment |
| first_name    | varchar(20)  | yes  |     | null    |                |
| last_name     | varchar(25)  | no   |     | null    |                |
| email         | varchar(100) | no   |     | null    |                |
| phone_number  | varchar(20)  | yes  |     | null    |                |
| hire_date     | date         | no   |     | null    |                |
| job_id        | int(11)      | no   | mul | null    |                |
| salary        | decimal(8,2) | no   |     | null    |                |
| manager_id    | int(11)      | yes  | mul | null    |                |
| department_id | int(11)      | yes  | mul | null    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set

在employees表中，manager_id列指定员工的经理。 以下语句将employees表连接到自身，以查询每位员工的上级经理信息。

select 
    concat(e.first_name, ' ', e.last_name) as employee,
    concat(m.first_name, ' ', m.last_name) as manager 
from
    employees e
        inner join
    employees m on m.employee_id = e.manager_id
order by manager;

执行上面查询语句，得到以下结果 -

+----------------+---------------+
| employee       | manager       |
+----------------+---------------+
| bruce wong     | alexander lee |
| david liang    | alexander lee |
| valli chen     | alexander lee |
| diana chen     | alexander lee |
| karen zhang    | avg su        |
| alexander su   | avg su        |
| shelli zhang   | avg su        |
| sigal zhang    | avg su        |
| guy zhang      | avg su        |
| alexander lee  | lex liang     |
| irene liu      | matthew han   |
... ...
| avg su         | steven lee    |
| john liu       | steven lee    |
| karen liu      | steven lee    |
| jonathon yang  | steven lee    |
| jack yang      | steven lee    |
+----------------+---------------+
39 rows in set

由于内部联接子句仅包括在另一个表中具有匹配行的行，因此主席未显示在上述查询的结果集中。
主席(president)没有任何经理。 在employees表中，包含主席的行的manager_id为null。
要在结果集中包含主席，使用left join子句而不是inner join子句作为以下查询。

select 
    concat(e.first_name, ' ', e.last_name) as employee,
    concat(m.first_name, ' ', m.last_name) as manager 
from
    employees e
        left join
    employees m on m.employee_id = e.manager_id
order by manager;

执行上面查询语句，得到以下结果 -

+----------------+---------------+
| employee       | manager       |
+----------------+---------------+
| steven lee     | null          |
| diana chen     | alexander lee |
| bruce wong     | alexander lee |
| david liang    | alexander lee |
| valli chen     | alexander lee |
| guy zhang      | avg su        |
... ...
| john liu       | steven lee    |
| karen liu      | steven lee    |
| jonathon yang  | steven lee    |
+----------------+---------------+
40 rows in set

在本教程中，演示了如何使用inner join或left join子句将表连接到自身。