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;

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