SQL 连接
连接(JOIN)是按条件把两张表的行配对,每一个满足条件的配对输出一行。客户表和订单表按客户 ID 连接,得到的就是「每个客户的每笔订单」。
连接写错时通常不报错,只是行数不对:没有订单的客户消失了,或者一个客户的金额被算了好几遍。外连接怎样给没匹配上的行补 NULL?条件写在 ON 和 WHERE 里有什么不同?一对多连接又会怎样放大明细?下文以 MySQL 8.0 为准。
1. 面试核心
先记住这几条,再读正文INNER JOIN只返回匹配上的行,LEFT JOIN还会保留没匹配上的左表行,右表列补 NULL。卡 · 面试核心- 一对多连接为每个匹配对输出一行,左表的一行会重复出现,直接对左表金额求和会被放大。
- 在
WHERE里对右表列写p.amount >= 50这类条件,会把LEFT JOIN补出来的 NULL 行过滤掉,外连接就退化成了内连接;IS NULL等保留补空行的条件不属于这种情况。卡 · 面试核心 EXISTS只判断有没有匹配,不会因为匹配多行而重复输出;NOT EXISTS选出完全没有匹配的行。卡 · 存在性与反连接- 连接多张明细表时,先把每张明细聚合到目标粒度再连接,才能避免重复求和。卡 · 多明细连接与重复统计
2. 匹配与补空
下面三个客户中,Lin 有两笔购买,Ming 有一笔,Yu 没有。用 LEFT JOIN 把客户和购买记录连起来:
CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR(20));
CREATE TABLE purchases (
id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER
);
INSERT INTO customers VALUES (1, 'Lin'), (2, 'Ming'), (3, 'Yu');
INSERT INTO purchases VALUES (10, 1, 50), (11, 1, 80), (12, 2, 20);
SELECT c.id, p.id AS purchase_id
FROM customers c
LEFT JOIN purchases p ON p.customer_id = c.id
ORDER BY c.id, p.id;
结果是 (1,10)、(1,11)、(2,12)、(3,NULL)。Lin 匹配到两笔购买,所以出现两行,这就是一对多连接让左表行重复的原因。Yu 没有任何匹配,LEFT JOIN 仍然保留他,右表的列全部补 NULL,这一行叫补空行。
换成 INNER JOIN,结果只剩前三行,没有匹配的 Yu 不会出现。
3. ON 与 WHERE
LEFT JOIN 的条件放在 ON 还是 WHERE,结果可能完全不同。关键在于两者生效的时机:ON 在连接时决定右表哪些行能和左表配对,配不上的左表行照样保留并补空;WHERE 在连接完成之后,对整个结果过滤,补空行也要接受检查。
先把金额条件放在 ON 里:
SELECT c.id, p.id AS purchase_id FROM customers c
LEFT JOIN purchases p
ON p.customer_id = c.id AND p.amount >= 50
ORDER BY c.id, p.id;
结果是 (1,10)、(1,11)、(2,NULL)、(3,NULL)。金额条件只限制了哪些购买记录能参与配对:Ming 唯一的一笔 20 配不上,于是 Ming 和 Yu 一样变成补空行,三个客户仍然全部保留。
如果把条件移到连接之后,写成 WHERE p.amount >= 50,结果只剩 Lin 的两行。Ming 的购买金额 20 不满足条件;Yu 的补空行里 p.amount 是 NULL,NULL >= 50 的结果是 UNKNOWN,WHERE 只保留结果为 TRUE 的行,所以也被去掉。这时 LEFT JOIN 的效果和 INNER JOIN 一样了。
所以规则是:想限制「哪些右表行参与匹配」,条件写在 ON;想过滤「最终输出哪些行」,才写在 WHERE。
4. 存在性与反连接
有时只想知道客户有没有购买记录,不需要购买的明细。这时用 EXISTS 更直接:它对每个客户检查子查询是否至少返回一行,有就保留这个客户,不关心有几行。
SELECT c.id FROM customers c
WHERE EXISTS (SELECT 1 FROM purchases p WHERE p.customer_id=c.id)
ORDER BY c.id;
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM purchases p WHERE p.customer_id=c.id)
ORDER BY c.id;
第一条返回 1、2,Lin 虽然有两笔购买,也只出现一次。第二条只返回 3。和 JOIN 不同,右表匹配多少行都不会让 EXISTS 的结果变多。
第二条这种「选出没有匹配的行」的查询叫反连接。它也可以写成 LEFT JOIN 再加 WHERE p.id IS NULL,利用的正是补空行。这时要检查右表中保证非空的列,比如主键;如果检查一个本来就可能为 NULL 的列,真实数据里的 NULL 会被误当成「没有匹配」。用 NOT IN 写反连接也有空值问题,见 NOT IN 与 NULL。
5. 多明细连接与重复统计
一对多连接会让左表行重复,同时连接两张一对多的明细表,问题会更严重。假设一个客户有 2 个订单和 3 个标签,两张明细表都按客户 ID 连接,这个客户会得到 2 × 3 = 6 行,每个订单和每个标签都配对一次。每笔订单金额重复出现 3 次,直接 SUM 就把总额放大了 3 倍。
解决办法是先把每张明细表聚合到客户粒度,一个客户一行,再拿去连接。例如购买总额可以先算好:
SELECT c.id, COALESCE(p.total, 0) AS total
FROM customers c
LEFT JOIN (
SELECT customer_id, SUM(amount) AS total
FROM purchases GROUP BY customer_id
) p ON p.customer_id=c.id
ORDER BY c.id;
结果为 (1,130)、(2,20)、(3,0)。子查询先把购买记录压成每个客户一行,再连接时就不会有重复;Yu 没有购买,COALESCE 把补空的 NULL 换成 0。标签等其他明细也同样先聚合到一人一行,再加入连接。
用 SUM(DISTINCT amount) 去掉重复看起来也行,但它去重的依据是金额的值,不是订单。两笔不同的订单金额恰好相同时,也会被合并成一笔,结果反而少算。所以它修不了粒度错误。
6. 连接算法
前面讲的是连接的结果,这一节讲数据库怎样算出这个结果。
嵌套循环连接是最基本的算法:对外层表(驱动表)的每一行,到内层表里找匹配的行。内层表的连接列有索引时,每次查找只需要一次索引定位;没有索引就要扫描内层表。所以成本大致是「外层行数 × 每次内层查找的成本」。选驱动表时要看的是过滤之后剩下的行数,而不是表本身有多少行:一张大表经过 WHERE 过滤后只剩几行,就适合放在外层。
MySQL 8.0.18 起,适合的连接可以使用 hash join:先用其中一张表的连接键建一个内存哈希表,再逐行扫描另一张表,用键去哈希表里查匹配。它不依赖内层索引,适合等值连接;哈希表放不进内存时,要分批写到磁盘上处理,成本会上升。实际用了哪种算法、内层循环了多少次,见执行计划。
7. 使用边界
ON 里的普通等值比较匹配不了两个 NULL,因为 NULL = NULL 的结果是 UNKNOWN。确实需要把空值当成相等时,MySQL 提供了 NULL 安全的比较运算符 <=>。但这会让所有空值互相匹配,用之前要确认业务上确实是这个意思。
几种特殊的连接也值得知道:CROSS JOIN 不加条件时,产生两张表所有行的组合,也就是笛卡尔积;自连接是同一张表用两个别名参与匹配,比如员工表连接自己找到每个人的上级。另外,内连接的顺序可以由优化器重排,SQL 里先写哪张表,不代表执行时先读哪张表。
8. 面试题卡片
9. 参考资料
- MySQL:JOIN Clause
- MySQL 8.0:Outer Join Simplification
- MySQL 8.0:EXISTS and NOT EXISTS
- MySQL 8.0:Hash Join Optimization
- MySQL 8.0:Comparison Operators
来源:Coding 101 原文 · 免费阅读,引用请保留出处。内容使用说明