高级联结

自联结 / 自然联结 / 带聚合的联结 / 多条件联结

上一章讲了 INNER JOIN 和 LEFT JOIN 的基本用法，这一章我们深入几个实战中经常遇到、但容易被绕晕的联结场景。

自联结：一张表自己连自己

什么场景会用到？

假设你要找出"和郑十住在同一个城市的所有人"。

用子查询，分两步——先查郑十的城市，再查这个城市的人：

SELECT name, city
FROM users
WHERE city = (SELECT city FROM users WHERE name = '郑十');

这没问题。但如果你想同时看到郑十和其他人的信息并排对比呢？子查询做不到——它只能给你最终结果，没法把"参照人"的字段也列出来。

这时就需要自联结——把同一张表当成两张表来 JOIN。

写法

SELECT u1.name AS 姓名,
       u1.city AS 城市,
       u2.name AS 参照人
FROM users AS u1
INNER JOIN users AS u2 ON u1.city = u2.city
WHERE u2.name = '郑十'
  AND u1.name <> '郑十';   -- 排除郑十自己

+------+------+--------+
| 姓名 | 城市 | 参照人 |
+------+------+--------+
| 张三 | 北京 | 郑十   |
| 孙七 | 北京 | 郑十   |
+------+------+--------+

拆解执行过程

把 users 当成两张独立的表来看：

u1（当作"待查人"）         u2（当作"参照人"）
+------+------+        +------+------+
| 张三 | 北京 |        | 张三 | 北京 |
| 李四 | 上海 |        | 李四 | 上海 |
| ...  | ...  |        | ...  | ...  |
| 郑十 | 北京 |        | 郑十 | 北京 |  ← WHERE 先锁定这一行
+------+------+        +------+------+

执行流程：

1. WHERE u2.name = '郑十' → 在 u2 里锁定郑十（城市 = 北京）
2. ON u1.city = u2.city → 在 u1 里找所有城市 = 北京的行
3. AND u1.name <> '郑十' → 排除郑十本人

💡 自联结的关键：别名。同一张表给两个不同的别名（u1、u2），MySQL 就把它们当两张独立的表处理。

另一个实战场景：找工资比领导高的人

假设 users 表加一列 leader_id 指向直属领导（暂时还用现有数据演示不了，但写法是一样的）：

SELECT e.name AS 员工,
       e.salary AS 员工工资,
       m.name AS 领导,
       m.salary AS 领导工资
FROM users AS e
INNER JOIN users AS m ON e.leader_id = m.id
WHERE e.salary > m.salary;

自联结的应用场景基本都长这样——同一张表里的数据需要互相对比。

自然联结：让 MySQL 自动匹配

什么是自然联结

到目前为止，我们每次 JOIN 都要手动写 ON 条件。但如果两张表刚好有同名的列，可以偷懒：

-- 普通写法：手动指定 ON
SELECT u.name, o.product
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

-- 自然联结：MySQL 自动找同名列匹配
SELECT u.name, o.product
FROM users AS u
NATURAL JOIN orders AS o;

NATURAL JOIN 会自动找出两表中所有同名的列，并用它们作为联结条件。

但有个坑

在我们的例子里，users 和 orders 都有 id 列——但 users.id 是用户 ID，orders.id 是订单 ID，两个根本不是一回事。自然联结会把它们也当成联结条件，导致结果为空。

所以自然联结的要求很苛刻：同名列必须确实是同一回事。现实中的表设计很难满足这个条件。

⚠️ 生产环境建议：少用 NATURAL JOIN。省了 ON 三个字母，但隐蔽的匹配错误可能让你排查半天。老老实实写 ON，清晰可控。

带聚合的联结

联结最实用的进阶玩法——JOIN + GROUP BY 一起上。

统计每人下了多少单

SELECT u.name, COUNT(o.id) AS 订单数
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY 订单数 DESC;

+------+--------+
| name | 订单数 |
+------+--------+
| 王五 |      5 |
| 张三 |      3 |
| 李四 |      1 |
| 孙七 |      1 |
| 赵六 |      0 |
| 周八 |      0 |
| 吴九 |      0 |
| 郑十 |      0 |
+------+--------+

几个要点：

• LEFT JOIN 而不是 INNER JOIN——没下过单的人也要显示（订单数为 0）
• COUNT(o.id) 而不是 COUNT(*)——COUNT(*) 会把 NULL 行也算 1
• GROUP BY u.id, u.name——按人分组

统计每人的消费总额

假设 orders 表加一列 price（暂时没有，用 COUNT 演示原理）：

-- 如果有 price 列
SELECT u.name, SUM(o.price) AS 消费总额
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name;

查下单超过 2 次的人

SELECT u.name, COUNT(o.id) AS 订单数
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 2;

+------+--------+
| name | 订单数 |
+------+--------+
| 张三 |      3 |
| 王五 |      5 |
+------+--------+

📝 执行顺序：FROM → JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。理解这个顺序，WHERE 和 HAVING 放哪就清楚了——分组前过滤用 WHERE，分组后过滤用 HAVING。

多条件联结

有时候一个条件不够，ON 后面可以接多个条件：

-- 联结条件：用户 ID 匹配，且订单日期在 2024 年 3 月之后
SELECT u.name, o.product, o.order_date
FROM users AS u
INNER JOIN orders AS o
    ON u.id = o.user_id
    AND o.order_date > '2024-03-01';

这和把条件放在 WHERE 里的效果一样：

-- 等价写法
SELECT u.name, o.product, o.order_date
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id
WHERE o.order_date > '2024-03-01';

但 LEFT JOIN 时两者有区别：

-- 条件放 ON 里：先筛选右表，再左联结
SELECT u.name, o.product
FROM users AS u
LEFT JOIN orders AS o
    ON u.id = o.user_id
    AND o.product = '鼠标';     -- 只看"鼠标"的订单，但所有人保留

-- 条件放 WHERE 里：联结完后，整体过滤
SELECT u.name, o.product
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
WHERE o.product = '鼠标';        -- 没有鼠标订单的人会被整行删掉

第一条 LEFT JOIN：赵六、周八、吴九、郑十还在（product 是 NULL），因为 ON 只限制右表匹配条件，左表行全部保留。

第二条 WHERE：赵六、周八、吴九、郑十消失了——他们的 o.product 是 NULL，WHERE o.product = '鼠标' 直接过滤掉了。

💡 经验：INNER JOIN 时 ON 和 WHERE 没区别。LEFT JOIN 时，想保留左表所有行，过滤右表的条件放 ON 里；想过滤最终结果，放 WHERE 里。

联结小结

到现在为止，联结的核心知识全在这两章了：

联结类型	一句话	什么时候用
INNER JOIN	两表都有才返回	绝大多数情况
LEFT JOIN	左表全保留	要包含"没匹配上"的行
自联结	一张表当两张用	行与行之间需要对比
自然联结	自动匹配同名列	了解即可，不建议用

小结

这章把联结推到了实战级——不再是简单的 A JOIN B，而是"一张表怎么和自己连、联结怎么和聚合配合"：

1. 自联结 — 同一张表里行与行之间做比较（找同城的人、找父子关系）
2. 自然联结知道就行 — 自动匹配同名列省事，但不可控，不推荐生产环境用
3. 联结 + 聚合 — 统计每个城市下了多少单、每人买了多少产品
4. 多条件联结 — 有些关系不只靠一个列匹配，可能需要多个条件

💡 自联结和 JOIN + 聚合是面试高频考点，值得多花点时间跑练习。

自主练习

1. 用自联结找出和"李四"住在同一个城市的人（排除李四自己）
2. 统计每个城市的下单总次数（提示：users + orders + GROUP BY city）
3. 用 LEFT JOIN 找出哪些产品从来没被人买过（假设有 products 表，思考写法即可）
4. 查出"下单数超过该城市平均下单数的人"（有点难，提示：子查询 + 联结组合用）