SQL Join3

1. 前言

上一小节中我们学习到了外连接,本小节我们将介绍一种特殊的连接——自连接

本小节测试数据如下,请先在数据库中执行:

DROP TABLE IF EXISTS imooc_employee;
CREATE TABLE imooc_employee
(
 id int PRIMARY KEY,
 employee_name varchar(20),
 salary int,
 manager_id int
);
INSERT INTO imooc_employee VALUES(1, 'Joe', 7000, 3);
INSERT INTO imooc_employee VALUES(2, 'Henry', 8000, 4);
INSERT INTO imooc_employee VALUES(3, 'Sam', 6000, NULL);
INSERT INTO imooc_employee VALUES(4, 'Pedro', 9000, NULL);

说明: 我们新建一个 imooc_employee 表,imooc_employee 表包含了所有员工,员工的经理(manager_id)也属于员工,如果manager_id 字段为 NULL,则表示该员工暂无经理。

2. 自连接

自连接指的是与自身进行连接,即表 A 与表 A 自身进行连接,是一种特殊的连接方式。

2.1 例1、查询薪水

请编写 SQL 语句,查询imooc_employee中收入超过其经理的员工的姓名。

分析:

我们可以把 manager_id 理解为一个外键,这个外键指向经理 id,只不过是表与其自身的关联;因此可以充分利用连接操作,将表 imooc_employee 与其自身连接,连接条件是 manager_id 和 id。

表 a 可以理解为员工表,表 b 理解为经理表,筛选条件为员工表的薪水大于经理表薪水。

语句

整理可得语句如下:

SELECT a.employee_name
FROM imooc_employee AS a
JOIN imooc_employee AS b
ON a.manager_id = b.id
WHERE a.salary > b.salary;

结果如下:

+---------------+
| employee_name |
+---------------+
| Joe           |
+---------------+

由于自连接是与自身进行连接,因此对于别名的要求是必须的,否则解析引擎无法判断出二者之间的关系。

3. 个人经验

  • 自连接虽然使用较少,但往往在有的时候可以起到奇效。

  • 在使用自连接时,请先指定好别名,然后将其当作两张表来处理,这样就不会引起歧义了。