SQL Server Self Join
目录简介
自连接将表连接到自身。它有助于查询层级数据或比较同一表中的行。
自联接使用内联接(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;
```![](/i/l/?n=23&i=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;