连接 — 留下哪一边

在 LabHub 中继续学习

一句话总结

连接是一种根据条件为两个集合中的行配对的操作，而实际工作中的大多数错误，都源于错误处理了没有匹配项的行。

概念图: INNER JOIN · LEFT JOIN · 反连接 · CROSS JOIN

为什么需要连接

连接是规范化需要付出的代价。客户信息与订单信息分别保存后，想要同时查看就必须重新把它们连接起来。问题在于如何处理“没有匹配项的行”：没有订单的客户应该保留在结果中，还是应该排除？这个决定就是连接类型。

它是如何工作的

INNER JOIN——只保留两边都有匹配项的行。
LEFT JOIN——保留左侧全部行，没有匹配项时，用 NULL 填充右侧列。
反连接——只保留没有匹配项的行。使用 NOT EXISTS 表达最清晰。
CROSS JOIN——生成所有组合。遗漏连接条件时，会意外得到这种结果。

这里必须指出最重要的陷阱：LEFT JOIN 之后，如果在 WHERE 中加入右表条件，它会立刻变成 INNER JOIN。

-- 모든 고객 + 그중 결제 완료 주문 (고객 400명 유지)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id;

-- 결제 완료 주문이 있는 고객만 (고객 수가 줄어든다)
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id;

没有匹配项的行，其右侧列为 NULL，而 o.status = 'paid' 对 NULL 返回 unknown，因此这些行会全部被过滤掉。规则是：左外连接中，右表过滤条件放在 ON 子句，左表过滤条件放在 WHERE 子句。

连接与聚合结合时还有一个陷阱。LEFT JOIN 后使用 count(*)，会把没有匹配项的行也计为 1。只有像 count(o.id) 这样统计被连接一侧的列，才能得到 0。这利用了聚合函数会忽略 NULL 的特性。

实际工作中会遇到的情况

人们也经常忘记连接会增加行数。一笔订单有三条明细时，连接结果会变成三行；此时执行 sum(o.total_amount)，订单金额就会变成三倍。这种扇出是报表数值悄然膨胀的典型原因。解决办法是先聚合再连接；不要使用 sum(DISTINCT ...)，而应通过子查询预先收拢数据。

从性能角度看，值得了解三种连接算法。如果外侧行数较少，而且内侧有索引，适合使用嵌套循环；如果是等值连接，且一侧能够放入内存，适合使用哈希连接；如果两边已经排序，则适合使用归并连接。虽然算法由优化器选择，但如果执行计划中的嵌套循环重复数超过数万次，就说明对外侧行数的估算可能有误。

用图表示连接类型

A (주문)          B (고객)
 ┌───┐            ┌───┐
 │ 1 │────────────│ 1 │      INNER   : 양쪽에 다 있는 것만
 │ 2 │────────────│ 2 │      LEFT    : A 는 전부, 짝 없으면 B 쪽이 NULL
 │ 3 │            │   │      RIGHT   : B 는 전부
 │   │            │ 4 │      FULL    : 양쪽 전부
 └───┘            └───┘

实际工作中，90% 的场景使用 INNER 与 LEFT。通常把 RIGHT 反转写成 LEFT 更容易阅读，而 FULL 用于数据核对，例如找出只存在于其中一侧的记录。

LEFT JOIN 中条件所处的位置会改变结果。 这是最常见的错误。

-- 취소되지 않은 결제가 있는 주문 + 결제가 아예 없는 주문
select o.*, p.amount
from orders o
left join payments p on p.order_id = o.id and p.status <> 'canceled';
                                            ↑ ON 절 — 짝을 찾는 조건

-- 사실상 INNER JOIN 이 된다. 짝이 없는 행은 p.status 가 NULL 이라 걸러진다
from orders o
left join payments p on p.order_id = o.id
where p.status <> 'canceled';
      ↑ WHERE 절 — 조인 결과를 거르는 조건

LEFT JOIN 之后，把右表条件写在 WHERE 中，就会失去 LEFT 的含义。 规则是把右表条件放在 ON 中，把左表条件放在 WHERE 中。

发现行数膨胀

如果存在多个匹配项，连接会把行数成倍增加。一笔订单对应三笔付款时，该订单会变成三行，此时执行 sum(o.amount)，金额就会变成三倍。

-- ❌ 주문 금액이 결제 건수만큼 부풀려진다
select sum(o.amount) from orders o join payments p on p.order_id = o.id;

-- ✅ 미리 접어서 조인한다
select sum(o.amount)
from orders o
join (select order_id from payments group by order_id) p on p.order_id = o.id;

-- ✅ 또는 존재 여부만 물을 때는 EXISTS
select sum(o.amount) from orders o
where exists (select 1 from payments p where p.order_id = o.id);

EXISTS 找到第一个匹配项就会停止，因此不会增加行数，而且通常更快。 如果只需要知道“是否存在”，它比连接更合适。

NOT IN 的 NULL 陷阱

-- 서브쿼리 결과에 NULL 이 하나라도 있으면 전체가 빈 결과가 된다
select * from orders where customer_id not in (select id from vip_customers);

-- 안전하다
select * from orders o
where not exists (select 1 from vip_customers v where v.id = o.customer_id);

x NOT IN (1, 2, NULL) 等价于 x <> 1 and x <> 2 and x <> NULL，但最后一项的结果是 UNKNOWN，所以整个条件不会为真。使用 NOT IN 的替代方案 NOT EXISTS 更加安全。

下一个实验要做什么

依次编写内连接、外连接、反连接与自连接，并亲自比较把同一个条件放在 ON 子句和 WHERE 子句时，结果会出现怎样的差异。