SQL - 自连接：初学者的友好指南

你好，有抱负的SQL爱好者们！我很高兴能成为你们在这个激动人心的SQL自连接世界中的向导。作为多年教授计算机科学的教师，我看到无数学生在掌握这个概念时都有过“恍然大悟”的时刻。那么，让我们跳进去，创造一些SQL魔法吧！

什么是自连接？

在我们深入了解之前，让我们从一个简单的类比开始。想象你在家庭聚会上，你想创建一个包含所有父母-子女关系的列表。你有一个大家庭成员的表格，但你需要在这个表格中把人连接到他们的父母。这就是SQL中的自连接所做的！

自连接是当一张表与自己连接时发生的。就像表格在照镜子并与自己的倒影连接一样。听起来有点令人困惑，对吧？别担心，很快这一切都会变得有意义！

为什么使用自连接？

当你在单个表中拥有层次结构或递归数据时，自连接非常有用。想想以下情况：

1. 员工-经理关系
2. 家谱树
3. 制造业中的部件和子部件
4. 论坛中的回复线程

SQL自连接实战

让我们创建一个简单的例子来演示自连接是如何工作的。我们将使用一个员工表来进行演示。

首先，让我们创建我们的表：

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id)
VALUES
(1, 'John Doe', NULL),
(2, 'Jane Smith', 1),
(3, 'Bob Johnson', 1),
(4, 'Alice Brown', 2),
(5, 'Charlie Davis', 2);

现在，假设我们想列出每个员工及其经理的名字。这时自连接就派上用场了：

SELECT
e1.name AS employee,
e2.name AS manager
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.employee_id;

让我们分解这个查询：

1. 我们从employees表中选择两次，分别给它别名e1和e2。
2. e1代表员工，而e2代表潜在的经理。
3. 我们在这些“两个”表之间建立连接，条件是e1的manager_id与e2的employee_id相匹配。
4. 我们使用LEFT JOIN确保我们得到所有员工，包括那些没有经理的员工。

结果可能如下所示：

员工	经理
John Doe	NULL
Jane Smith	John Doe
Bob Johnson	John Doe
Alice Brown	Jane Smith
Charlie Davis	Jane Smith

这难道不酷吗？仅用一个查询，我们就绘制出了我们这个小公司的整个管理结构！

自连接与ORDER BY子句

现在，让我们给我们的查询添加一点调料，通过排序我们的结果。我们可能希望按员工的字母顺序查看员工：

SELECT
e1.name AS employee,
e2.name AS manager
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.employee_id
ORDER BY
e1.name ASC;

这个查询与我们的前一个查询相同，只是在最后添加了ORDER BY子句。

结果现在看起来像这样：

员工	经理
Alice Brown	Jane Smith
Bob Johnson	John Doe
Charlie Davis	Jane Smith
Jane Smith	John Doe
John Doe	NULL

好多了！现在我们可以轻松找到任何员工及其经理。

高级自连接技巧

准备好升级了吗？让我们尝试一些更复杂的操作。假设我们想找到拥有相同经理的员工？

SELECT
e1.name AS employee1,
e2.name AS employee2,
m.name AS shared_manager
FROM
employees e1
JOIN
employees e2 ON e1.manager_id = e2.manager_id AND e1.employee_id < e2.employee_id
JOIN
employees m ON e1.manager_id = m.employee_id;

这个查询可能看起来令人畏惧，但让我们分解它：

1. 我们两次连接employees表（e1和e2）来比较员工。
2. 条件e1.employee_id < e2.employee_id确保我们不会得到重复的对（比如“Alice和Bob”以及“Bob和Alice”）。
3. 我们第三次连接（m）来获取经理的名字。

结果可能如下所示：

employee1	employee2	shared_manager
Jane Smith	Bob Johnson	John Doe
Alice Brown	Charlie Davis	Jane Smith

就这样！我们找到了拥有相同经理的员工。

结论

自连接一开始可能看起来有点棘手，但它们是你在SQL工具箱中非常强大的工具。它们允许你高效地查询层次结构数据，并在单个表中发现关系。

记住，熟能生巧！尝试创建你自己的表并尝试不同的自连接查询。在你意识到之前，你将成为自连接的高手！

快乐查询，未来的SQL大师们！记住，在数据库的世界里，与自己对话……我是说，与自己连接是完全正常的！

Credits: Image by storyset