7.12 SQL Server自连接

SQL Server Self Join

目录

• SQL Server Self Join
  • 简介
  • 自连接查询示例
    • 1）使用自连接查询层级数据
    • 2）使用自联接比较表中的行

简介

自连接将表连接到自身。它有助于查询层级数据或比较同一表中的行。

自联接使用内联接（INNER JOIN）或左联接（LEFT JOIN）子句。因为使用自联接的查询引用的是同一个表，，所以为了区别表它们，得使用别名为查询中的同一表分配不同的名称。

注意：在查询中引用多个相同的表而不使用表别名将报错。

语法：

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

查询两次引用表T。表别名t1和t2用于在查询中为T表分配不同的名称。

自连接查询示例

1）使用自连接查询层级数据

有如下员工表（sales.staffs）：

员工表存储了员工得基本信息：员工Id，名字，姓氏，邮箱，还有一列manager_id制定了直接管理者Id。比如说因为Mireya的manager_id中的值是Fabiola,所以Fabiola是Mireya得上级领导。Mireya得像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匹配员工和领导关系。

因为是使用的内连接的自连接(INNER JOIN)，由于Fabiola Jackson没有上层领导，所以他就没有匹配上，没有出现在结果集中，如果想要Fabiola Jackson也展示出来，则可以使用左连接(LEFT JOIN):

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;

2）使用自联接比较表中的行

有如下客户表：

以下语句使用自联接查找位于同一城市的客户。

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

以下条件确保该语句不会与同一客户进行比较：

c1.customer_id > c2.customer_id

以下条件匹配两个客户的城市：

AND c1.city = c2.city

注意，如果把大于号(>)改成不等于(<>)符号，结果中将会包含更多行：

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;
```![](https://img2023.cnblogs.com/blog/1552138/202301/1552138-20230129111101384-1487636211.png)

我们看看`ON`子句中的`>`和`<>`之间的区别，将其限制为一个城市，以便于比较。
```sql
SELECT 
   customer_id, first_name + ' ' + last_name c, 
   city
FROM 
   sales.customers
WHERE
   city = 'Albany'
ORDER BY 
   c;

此查询在ON子句中使用（>）运算符:

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

此查询在ON子句中使用（<>）运算符：

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