SQL自連線

本教學將向您展示如何使用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子句將表連線到自身。