【关联的力量(下)】探索更多可能：外连接与自连接-CSDN博客

这是我们系列博客的第十篇，将补全JOIN的知识体系，并介绍一种非常巧妙的连接技巧。

【关联的力量(下)】探索更多可能：外连接与自连接

专辑： SQL快速上手：15篇入门到精通
标签： SQL LEFT JOIN OUTER JOIN SELF JOIN 多表查询

前言

在上一篇【关联的力量(上)】SQL的精髓：内连接INNER JOIN 中，我们掌握了INNER JOIN，它能够完美地找出在多个表中都存在匹配的数据，也就是数据的“交集”。

但是，INNER JOIN有一个“缺点”：它会无情地丢弃掉那些在另一个表中找不到匹配的行。这在很多时候并不是我们想要的结果。

比如，老板想看一份所有顾客的列表，并在旁边列出他们各自的订单号。如果我们用INNER JOIN，那些从未下过单的顾客（比如赵云和司马懿）就会从这份报告中消失，这显然不符合“所有顾客”的要求。

为了解决这类需要“以一方为准，另一方为辅”的查询，SQL提供了外连接 (OUTER JOIN)。今天，我们就将学习LEFT JOIN和RIGHT JOIN，并探索一种特殊的连接技巧——自连接 (SELF JOIN)。

1. `LEFT JOIN`：左边的，一个都不能少

LEFT JOIN，全称为LEFT OUTER JOIN（OUTER关键字通常可以省略），是外连接中最常用的一种。

它的核心思想是：以左表（FROM子句后的第一个表）为基准，返回左表的所有行。

如果右表中有能与之匹配的行，就将这些行的数据组合起来。
如果右表中没有能与之匹配的行，右表对应的列将用NULL值填充。

示例1：查询所有顾客及其订单信息

这就是我们前面提到的那个经典场景。我们想看到所有顾客，无论他们是否下过单。

输入SQL：

SELECT
    c.cust_name,
    o.order_num
FROM
    Customers AS c
LEFT JOIN
    Orders AS o ON c.cust_id = o.cust_id;

执行逻辑：
数据库会遍历左表Customers的每一行。

当遍历到“刘备”时，它在Orders表中找到了两个匹配的cust_id，于是生成两行结果。
当遍历到“赵云”时，它在Orders表中找不到任何匹配的cust_id，但由于是LEFT JOIN，它仍然会为“赵云”生成一行结果，只是Orders表对应的o.order_num列被填充为NULL。

执行结果：

cust_name	order_num
刘备	1
刘备	5
关羽	4
张飞	2
赵云	NULL
曹操	3
司马懿	NULL

看，这次“赵云”和“司马懿”都出现在了结果里，order_num列为NULL，清晰地表明他们没有下过订单。

2. `RIGHT JOIN`：右边的，才是主角

RIGHT JOIN（或 RIGHT OUTER JOIN）的工作方式与LEFT JOIN完全相反。它以右表为基准，返回右表的所有行。如果在左表中找不到匹配，则左表对应的列会被填充为NULL。

实际上，RIGHT JOIN在实践中用得相对较少，因为任何一个RIGHT JOIN查询都可以通过调换两个表的位置，改写成一个逻辑更清晰的LEFT JOIN查询。保持使用LEFT JOIN的习惯，可以让你的代码风格更统一。

3. `SELF JOIN`：当“我”需要和“我”自己比较

SELF JOIN（自连接）不是一种新的JOIN类型，而是一种巧妙的**JOIN技巧**。它指的是一张表与它自身进行连接。

什么时候需要用到它呢？当你需要在一个表内部，比较不同行之间的数据时。
例如：

在员工表中，查找和“张三”在同一个部门的所有其他员工。
在商品表中，查找所有价格相同的不同商品。

要实现自连接，关键在于必须为同一张表使用两个不同的别名，让数据库把它当作两张独立的表来处理。

示例2：查找所有居住在同一城市的不同顾客

我们想知道哪些顾客是“老乡”。

输入SQL：

SELECT
    c1.cust_name AS customer1,
    c2.cust_name AS customer2,
    c1.cust_city
FROM
    Customers AS c1
INNER JOIN
    Customers AS c2 ON c1.cust_city = c2.cust_city;

这个查询会把Customers表当作两张独立的表c1和c2，然后根据cust_city相等这个条件进行内连接。

不完美的执行结果：

customer1	customer2	cust_city
刘备	刘备	北京
刘备	曹操	北京
曹操	刘备	北京
曹操	曹操	北京
…	…	…

这个结果有两个问题：

一个顾客和他自己被匹配了（刘备-刘备）。
匹配是重复的（刘备-曹操和曹操-刘备都出现了）。

我们可以通过在WHERE子句中添加一个条件来解决这个问题，确保只返回唯一的、有意义的配对。

示例3：优化后的自连接查询

输入SQL：

SELECT
    c1.cust_name AS customer1,
    c2.cust_name AS customer2,
    c1.cust_city
FROM
    Customers AS c1
INNER JOIN
    Customers AS c2 ON c1.cust_city = c2.cust_city
WHERE
    c1.cust_id < c2.cust_id;```
通过添加`WHERE c1.cust_id < c2.cust_id`这个条件，我们巧妙地实现了两个目的：
*   `c1.cust_id`不可能等于`c2.cust_id`，排除了自己和自己匹配的情况。
*   只保留了ID较小的顾客在前、ID较大的顾客在后的配对，从而消除了重复。

**最终执行结果：**
| customer1 | customer2 | cust_city |
| :-------- | :-------- | :-------- |
| 刘备      | 曹操      | 北京      |

现在，结果就变得清晰而准确了。

## 总结与展望

今天，我们补全了SQL连接查询的知识版图，让我们的多表查询能力更加全面。

我们学到了：
*   **`LEFT JOIN`**：当需要以一个表为基准，展示其所有数据及另一个表的匹配数据时使用，找不到匹配则为`NULL`。
*   **`RIGHT JOIN`**：与`LEFT JOIN`相反，但通常可以被`LEFT JOIN`替代。
*   **`SELF JOIN`**：一种将表与自身连接的技巧，通过使用**别名**，用于比较表内不同行之间的数据。

至此，我们已经深入学习了如何从数据库中**检索**数据。从简单的单表查询，到复杂的多表连接和聚合，你已经掌握了SQL查询的核心。

然而，一个完整的数据库应用不仅包括“查”，还包括**“增、删、改”**。如何向数据库中添加新数据？如何修改已有数据？又该如何安全地删除数据？

这将是我们下一部分要学习的内容：**数据操纵语言 (DML)**。

> **下一篇预告：【数据的增删改】管理你的数据：`INSERT`, `UPDATE`, `DELETE`**

准备好从数据的“消费者”转变为数据的“管理者”了吗？我们下一篇见！