SQL Server自連接

在本教學中，將學習如何使用SQL Server自聯接將表連接到自身表。

SQL Server自連接語法

自聯接用於將表連接到自身(同一個表)。 它對於查詢分層數據或比較同一個表中的行很有用。

自聯接使用內連接或左連接子句。 由於使用自聯接的查詢引用同一個表，因此表別名用於為查詢中的表分配不同的名稱。

請注意，如果在不使用表別名的情況下在查詢中多次引用同一個表，則會出現錯誤。

以下是將表T連接到自身的語法：

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate;

上面查詢語句中兩次引用表T。表別名t1和t2用於為T表分配不同的名稱。

SQL Server自連接示例

讓我們舉幾個例子來理解自連接的工作原理。

A. 使用自聯接查詢分層數據

請參考示例資料庫中的staffs表：

表中存儲的行記錄如下：

staffs表存儲員工資訊，如身份證，名字，姓氏和電子郵件。 它還有一個名為manager_id的列，用於指定直接管理者。 例如，員工Mireya向管理員者Fabiola彙報工作，因為Mireya的manager_id列中的值是Fabiola。

Fabiola沒有經理，因為它的manager_id列是一個NULL值。

要獲取工作彙報關係，請使用自聯接，如以下查詢中所示：

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

執行上面查詢語句，得到以下結果：

在這個例子中，兩次引用了staffs表：一個是員工的e，另一個是管理者的m。 連接謂詞使用e.manager_id和m.staff_id列中的值匹配employee和manager關係。

由於INNER JOIN效應，employee列沒有Fabiola Jackson。 如果用LEFT JOIN子句替換INNER JOIN子句，如以下查詢所示，將獲得在employee列中包含Fabiola Jackson的結果集：

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

執行上面查詢語句，得到以下結果：

B. 使用自聯接來比較表中的行

請參閱以下customers表：

以下語句使用自聯接查找位於同一城市的客戶。

SELECT
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2,
    c1.city
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY
    customer_1,
    customer_2;

執行上面查詢語句，得到以下結果：