SQL JOIN 避坑指南：ON/WHERE、NULL 与一对多放大

上个月帮同事看一个对账报表，跑出来的总金额比财务系统少了三十多万，折腾了一下午，最后发现问题出在一条LEFT JOIN上——他把右表的过滤条件写进了ON而不是WHERE，条件一挂上去，左表里那些匹配不上的行被悄悄过滤掉了，LEFT JOIN也就退化成了INNER JOIN。这不是什么高深的知识点，但几乎每个写过 SQL 的人都在这里栽过跟头。SQL 的各种 join 用法之所以值得单独画一张图讲清楚，就是因为它们在语法上长得太像，只在几个关键字上差了那么一点，而结果集却可能天差地别：有的保留左边，有的保留右边，有的两边都不丢，有的干脆做笛卡尔积把所有组合都摊开。

这篇内容我打算围绕 SQL 里最常用的几种 join 展开，从语义边界讲到执行引擎的实现方式，再落到真实项目里的踩坑场景。不管你是刚开始写 SQL 的初学者，还是已经写了一两年但每次遇到LEFT JOIN行数不对都要重新推一遍的老手，看完至少能拿到两样东西：一份可以直接对着抄的 join 结果对照表，以及一套遇到"行数不对""结果少了行"时能立刻上手的排查路径。文中涉及的示例我尽量用最常见的写法，MySQL、SQL Server、Oracle、Hive 上基本都能跑，差异点我会单独标出来。

1. 为什么 join 值得你单独花时间搞清楚

1.1 从一次"行数不对"的排查说起

先还原一下那个对账问题的完整现场。业务方给了两张表，一张是订单主表orders，一张是订单对应的收款流水payments。需求很简单：把所有订单查出来，顺便带上每笔订单的收款金额。同事写的是这样：

SELECT o.order_no, o.amount, p.pay_amount FROM orders o LEFT JOIN payments p ON o.order_no = p.order_no AND p.status = 'SUCCESS';

看起来没问题，LEFT JOIN保证订单不丢，右表的条件写在ON里，理论上不影响左表。但实际跑出来行数比订单总数少了两百多行。原因是ON后面加了p.status = 'SUCCESS'之后，对于收款失败的订单，o.order_no = p.order_no和p.status = 'SUCCESS'两个条件没法同时满足，这时 SQL 引擎仍然会保留左表这一行，右边补NULL——注意，这一步是对的。真正导致行数变少的是后面某个人在查询末尾又补了一句WHERE p.pay_amount > 0，而NULL > 0的结果是UNKNOWN，被WHERE直接筛掉了。

这个案例里其实藏着两个经典陷阱：一是ON和WHERE的过滤时机不同，二是NULL参与任何比较运算都不会返回真。两者叠加起来，就出现了"逻辑上应该保留的行莫名其妙消失"的现象。我在后面第 4 和第 6 节会把这两个点拆开讲透。

1.2 join 在真实项目里的出现密度

我统计过自己最近经手的三四个项目，一个中等复杂度的报表查询里，JOIN出现的次数通常在 5 到 15 次之间。多表关联几乎是所有关系型数据库查询的常态，因为范式设计本身就是把数据拆开到不同的表里，需要的时候再拼回来。这就意味着，join 的写法直接决定了三件事：

• 结果的正确性：少写一个ON条件，可能就变成了笛卡尔积，一千万行数据互相乘起来能把库拖死。
• 查询的性能：同一条语句，join 顺序换一下、驱动表换一下，执行时间可能从 200 毫秒变成 20 秒。
• 代码的可维护性：一堆嵌套的LEFT JOIN堆在一起，三个月后自己都看不懂哪张表是主表。

所以 join 不是"能用就行"的语法糖，它是 SQL 里少数几个值得反复琢磨的核心机制之一。把几种 join 的语义、执行方式和踩坑点都过一遍，比背十条八条零散的 SQL 技巧有用得多。

2. 五种 join 的语义边界，一次讲清

2.1 inner join：只留下两边都能对上的行

INNER JOIN是默认的 join 类型，JOIN两个字单独写的时候就是它。它的语义很干脆：左右两张表按照ON里的条件做匹配，只有匹配成功的组合才会出现在结果里，任何一边匹配不上的行都直接丢掉。它的写法有三种，效果完全一样：

SELECT * FROM a INNER JOIN b ON a.id = b.a_id; SELECT * FROM a JOIN b ON a.id = b.a_id; SELECT * FROM a, b WHERE a.id = b.a_id; -- 老写法，不推荐

第三种用逗号加WHERE的写法属于 ANSI-89 标准，能跑但可读性差，尤其是三五张表关联的时候，WHERE里会堆一大串关联条件，很容易漏掉一个变成笛卡尔积。我现在的习惯是一律用显式的JOIN ... ON，关联条件和过滤条件一眼就能分清。

这里有个容易被忽略的点：INNER JOIN的结果行数不一定小于等于左表行数。如果右表里同一关联键出现了多行，左表的一行就会被复制成多行，这叫"一对多放大"。很多人默认 join 之后行数只会减少，这个假设是错的，第 6 节我会专门讲放大怎么排查。

2.2 left join 与 right join：一侧为主，另一侧允许空

LEFT JOIN的含义是"以左表为准"，左表的每一行都要保留，去右表里找匹配；找到了就把右表字段填上，找不到就填NULL。RIGHT JOIN正好相反，以右表为准。实际写代码时RIGHT JOIN用得很少，因为把两张表换个位置就能改写成LEFT JOIN，而人类读 SQL 是从左往右读的，统一用LEFT JOIN能减少理解成本。

有个反直觉的结论值得记住：LEFT JOIN的结果行数也不一定等于左表行数。如果右表对同一个关联键有多行匹配，左表某一行会被展开成多行，结果可能比左表还多。一个常用的经验是：只有当关联键在右表里唯一（或者是主键、有唯一索引）时，LEFT JOIN才不会放大行数。所以在写LEFT JOIN之前，先确认一下右表的关联键是不是唯一的，这一步能省掉后面很多排查时间。

2.3 full outer join：两边都不丢

FULL OUTER JOIN返回左右两表所有的行，能对上的拼在一起，对不上的各自补NULL。它最典型的用途是双向比对，比如核对两份不同系统导出的数据，找出"只在 A 系统有的""只在 B 系统有的"和"两边都有但值不一致的"。MySQL 原生不支持FULL OUTER JOIN，需要用LEFT JOIN和RIGHT JOIN的结果做UNION来模拟：

SELECT a.id, a.val, b.val FROM a LEFT JOIN b ON a.id = b.id UNION SELECT b.id, a.val, b.val FROM a RIGHT JOIN b ON a.id = b.id;

注意这里用UNION而不是UNION ALL，因为两侧的匹配行会重复出现，需要去重。代价是UNION会触发一次排序或哈希去重，数据量大时开销不小。如果只是想知道差异行，用WHERE a.id IS NULL OR b.id IS NULL会更划算。

2.4 cross join：笛卡尔积不是错误，是一种工具

CROSS JOIN不做任何匹配条件，返回左右两表的全部组合，行数等于两表行数之积。绝大多数时候我们把它当成"写错了的 join"，因为漏写ON条件时数据库就会按笛卡尔积处理。但它在特定场景下很有用，比如生成日期或编号的完整组合：

-- 给每个商品生成未来 7 天的库存占位行 SELECT p.product_id, d.sale_date FROM products p CROSS JOIN ( SELECT DATE_ADD(CURDATE(), INTERVAL n DAY) AS sale_date FROM numbers WHERE n BETWEEN 0 AND 6 ) d;

这种"补齐缺失维度"的需求在很多报表里都会出现，手动写七条UNION ALL也能实现，但CROSS JOIN更简洁，日期范围变了只改一个参数。使用前提是其中一张表的行数必须足够小，两张大表做CROSS JOIN是妥妥的事故。

2.5 半连接与反连接：exists 和 not exists 的分工

严格来说，EXISTS和NOT EXISTS不是 join 语法，但它们在语义上对应"半连接"和"反连接"——只关心右表有没有匹配行，不关心匹配了几行，也不把右表字段带出来。这个特性在"一对多放大"的场景下非常关键：

-- 写法一：用 EXISTS，订单表行数不变 SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_no = o.order_no); -- 写法二：用 JOIN + DISTINCT，多一次去重开销 SELECT DISTINCT o.* FROM orders o JOIN payments p ON p.order_no = o.order_no;

两种写法结果一样，但EXISTS在找到第一行匹配后就可以停止扫描，而且天然不会放大行数，在大数据量的存在性判断里通常更划算。NOT EXISTS对应的是"左表里没有匹配右表的行"，它和NOT IN有一个重要区别：如果子查询返回的结果里包含NULL，NOT IN会整体返回空集，而NOT EXISTS不受影响。这个坑我在第 6 节还会细说。

3. 用两张小表把每种 join 亲手跑一遍

3.1 造两张最简单的测试表

道理讲了一堆，不如自己动手跑一遍来得实在。我习惯用两张各四五行的迷你表来做实验，字段越少越好，方便肉眼核对结果。

CREATE TABLE t_a (id INT, name VARCHAR(10)); CREATE TABLE t_b (id INT, tag VARCHAR(10)); INSERT INTO t_a VALUES (1,'apple'),(2,'banana'),(3,'cherry'),(4,'durian'); INSERT INTO t_b VALUES (2,'red'),(3,'yellow'),(3,'green'),(5,'purple');

注意几个刻意的设计：t_a里的id=1、id=4在t_b里没有；t_b里的id=5在t_a里没有；t_b里id=3出现了两次。这几个"不对称"和"重复"正好能把 join 的所有边界情况覆盖到。

3.2 逐个跑，盯着结果行数看

先跑INNER JOIN：

SELECT a.id, a.name, b.id AS b_id, b.tag FROM t_a a JOIN t_b b ON a.id = b.id;

结果会有三行：(2,banana,2,red)、(3,cherry,3,yellow)、(3,cherry,3,green)。id=1和id=4因为右表没有被丢掉，id=5因为左表没有也被丢掉，而id=3因为右表有两行匹配，左表那一行被复制成了两行。这个"3 行"的结果就同时验证了 inner join 的两个特性：匹配不上就丢，一对多会放大。

再跑LEFT JOIN：

SELECT a.id, a.name, b.id AS b_id, b.tag FROM t_a a LEFT JOIN t_b b ON a.id = b.id;

结果四行：id=1和id=4保留下来，右表字段是NULL；id=2一行；id=3两行。总共 4 行，比t_a的 4 行一样多，只是因为id=3多了一行同时id=1、id=4各补了一行，正好抵消。这种巧合很容易让人误判"行数没变就是没问题"，所以核对LEFT JOIN不能只看总行数，得看主表每个 id 是不是至少出现一次。

RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN我建议你都照着跑一遍，尤其是CROSS JOIN，t_a4 行乘t_b4 行等于 16 行，肉眼扫一眼就能理解"笛卡尔积"到底是什么意思。

3.3 把结果固定成一张对照表

跑完之后，我一般会把结论整理成下面这张表，贴在团队文档里，新人来了直接看：

这张表最关键的一列是最后那列。很多人以为只有CROSS JOIN才会放大行数，事实上除了关联键在右表唯一的情况，所有 join 都会因为一对多而放大。这一列比前面几列更容易被忽略，也更容易在生产环境里造成数据翻倍。

3.4 自连接：把同一张表当成两张用

还有一种高频用法是自连接，也就是把一张表和它自己做 join，通常用来查"层级关系"或"同一组内的对比"。比如员工表里每行都有id和manager_id，要查出每个员工和他的上级：

SELECT e.name AS emp, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

自连接必须给两张"同一张表"起不同的别名，否则 SQL 引擎无法区分你指的是哪一份。用LEFT JOIN是为了让没有上级的那一行（通常是老板）也保留下来，右表补NULL。这个写法在处理组织架构、商品分类、评论回复这类树形数据时几乎每天都会用到。

4. on 与 where 的分工：left join 里最容易被误解的一步

4.1 条件写错位置，left join 会退化成 inner join

回到开头那个对账问题。ON和WHERE的区别可以这么理解：ON决定的是"两张表怎么匹配"，WHERE决定的是"匹配完之后留下哪些行"。对于INNER JOIN，这两个位置写条件的最终结果往往一样，因为匹配不上的行本来就会被丢掉。但换成LEFT JOIN，差别就出来了。

-- 写法 A：条件在 ON 里 SELECT * FROM orders o LEFT JOIN payments p ON o.order_no = p.order_no AND p.status = 'SUCCESS'; -- 写法 B：条件在 WHERE 里 SELECT * FROM orders o LEFT JOIN payments p ON o.order_no = p.order_no WHERE p.status = 'SUCCESS';

写法 A 里，收款失败的订单仍然会被保留，只是右表字段是NULL。写法 B 里，WHERE p.status = 'SUCCESS'会把右表为NULL的行直接筛掉，LEFT JOIN实际退化成了INNER JOIN。判断标准很简单：如果你要保留主表的所有行，那么针对从表的过滤条件必须放在ON里；如果过滤条件的目的是筛掉主表的行，才放在WHERE里。

4.2 想在右表加过滤，又不想丢左表的行

实际业务里的需求往往是这样的："查所有订单，如果这单已经成功收款，就显示收款金额，否则显示 0。" 这就是典型的写法 A 场景：

SELECT o.order_no, COALESCE(p.pay_amount, 0) AS pay_amount FROM orders o LEFT JOIN payments p ON o.order_no = p.order_no AND p.status = 'SUCCESS';

这里用COALESCE把NULL转成 0，比在应用层判断NULL更省事。注意不要写成IFNULL(p.pay_amount, 0)这种数据库方言，COALESCE是标准函数，换个数据库也能跑。

有一个更隐蔽的变体：如果过滤条件是写在子查询里，效果又不一样。

SELECT o.order_no, p.pay_amount FROM orders o LEFT JOIN ( SELECT order_no, pay_amount FROM payments WHERE status = 'SUCCESS' ) p ON o.order_no = p.order_no;

这种写法和写法 A 等价，但可读性更好，尤其在从表条件有三四条的时候，塞在ON里会显得很乱。代价是子查询可能被物化成临时表，数据量大时不一定有直接写ON高效。我的一般建议是：条件一两条直接放ON，超过三条或者需要复用就先写成子查询或 CTE。

4.3 多条件 join 的书写顺序会影响索引匹配吗

一个常见疑问是：ON a.id = b.a_id AND a.type = b.type和ON a.type = b.type AND a.id = b.a_id有区别吗？对于优化器来说，条件顺序基本不影响最终选择，代价模型会自己评估。但有一个例外值得注意：当ON里既有等值条件又有范围条件时，把等值条件写在前面有助于复合索引被更充分地利用。比如ON a.id = b.a_id AND b.create_time > '2024-01-01'，如果b表上建了(a_id, create_time)的复合索引，等值列a_id在前，范围列create_time在后，索引才能真正走到两个字段。反过来把范围条件放在第一个位置，索引可能只用得上一个字段。

这不是语法规定，而是索引结构决定的——B+ 树索引在等值匹配之后才能有序地范围扫描，这个原理后面第 7 节还会展开。

5. join 背后到底怎么执行：三种算法与选择逻辑

5.1 Nested Loop Join：小表驱动大表

嵌套循环是最直观的实现方式：从驱动表（通常是结果集较小的那张）取一行，然后到被驱动表里找匹配的行，找到就输出，找不到就取下一行。伪代码大概是这样：

for row_a in table_a: -- 驱动表 for row_b in table_b: -- 被驱动表 if row_a.id == row_b.a_id: output(row_a, row_b)

如果被驱动表在关联字段上有索引，内层循环就能走索引查找，复杂度接近O(N * log M)；如果没有索引，那就退化成全表扫描，复杂度是O(N * M)，数据量一大就彻底跑不动。这就是为什么关联字段一定要建索引，它不是锦上添花的优化，而是决定查询能不能在秒级返回的分水岭。

在 MySQL 5.6 之后还引入了 Block Nested-Loop Join（BNL）和 Batched Key Access（BKA），思路是把驱动表的一批行先缓存在 join buffer 里，再去被驱动表批量匹配，减少重复扫描的次数。这些优化是优化器自动做的，但前提是 join buffer 大小设置合理、被驱动表的关联列有合适索引。

5.2 Hash Join：等值连接的大杀器

哈希连接的思路完全不同：先把较小的表读进内存，按关联键建一张哈希表，然后逐行扫描较大的表，算哈希值去表里查，命中就输出。它的复杂度接近O(N + M)，在大数据量的等值连接上明显优于嵌套循环。缺点是只适用于等值条件，ON a.id > b.id这种范围连接用不了哈希。另外它需要内存，如果小表大到内存装不下，会退化成磁盘上的分区哈希，性能急剧下降。

MySQL 直到 8.0.18 才在 InnoDB 里正式支持哈希连接，之前版本只有 BNL。SQL Server 和 Oracle 很早就支持了，所以在这些库上跑同样的 SQL，执行计划可能完全不同。这也是为什么跨数据库做性能对比时不能只看 SQL 语句本身。

5.3 Merge Join：两侧有序时的线性扫描

归并连接要求两张表在关联键上都已经排好序（要么依赖索引天然有序，要么显式ORDER BY排序）。做法是两边各拿一个指针从头开始扫，谁的值小谁往前走，值相等就输出。整个过程只扫一遍，复杂度也是O(N + M)。它的优势在于输出结果天然有序，如果外层查询正好需要按这个键排序，就能省掉一次排序操作。缺点是排序本身的成本可能很高，如果是临时排序，那还不如用哈希连接。

5.4 优化器怎么选：代价模型与统计信息

三种算法由优化器根据代价模型自动选择，核心输入是表的统计信息——行数、列的唯一值个数（基数）、数据分布直方图等。这带来一个非常实际的问题：统计信息过期会让优化器做出离谱的选择。比如一张表实际有 1000 万行，统计信息还停留在两周前的 1000 行，优化器就会误以为它是张小表，选择它当驱动表，实际执行时循环一千万次，慢得让人怀疑人生。

所以遇到"昨天还快今天就慢"的查询，第一反应除了看执行计划，还要检查一下统计信息是不是该更新了。SQL Server 上可以看sys.dm_db_stats_properties，MySQL 上可以查information_schema.TABLES里的TABLE_ROWS大致判断，Oracle 则有DBMS_STATS包来手动收集。

6. 一对多放大与 null 陷阱：join 结果异常的根因

6.1 为什么 join 之后行数突然暴涨

行数放大的原因几乎只有一个：关联键在右表里不唯一。举一个真实例子，一张用户表users关联一张订单表orders，一个人可以下多单，orders.user_id上重复很正常。如果你写SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id，结果行数等于订单总数加上没下单的用户数——完全不是用户数。

要判断是不是被放大了，最快的办法是先对两边分别做个行数统计：

SELECT COUNT(*) AS user_cnt, COUNT(DISTINCT id) AS user_distinct FROM users; SELECT COUNT(*) AS order_cnt, COUNT(DISTINCT user_id) AS order_user_distinct FROM orders;

如果order_cnt远大于order_user_distinct，说明订单表里存在一对多，join 之后必然放大。这时候就得想清楚：你要的到底是"每个用户一条汇总记录"还是"每个用户每条订单一条记录"。前者需要先对订单表按user_id聚合，再 join。

6.2 先聚合再 join，还是先 join 再聚合

这个顺序对性能和正确性都有影响。先 join 再聚合，中间结果会被放大，聚合的数据量是全量组合行；先聚合再 join，中间结果只有聚合后的行数，join 的量级小得多。绝大多数情况下应该先聚合再 join：

SELECT u.id, u.name, COALESCE(t.total, 0) AS total FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t ON u.id = t.user_id;

这里把右表先按user_id聚合，聚合后user_id天然唯一，再LEFT JOIN就不会放大，COALESCE处理没下单用户的NULL。相比之下，先 join 再SUM得到的数字也许是对的，但中间过程扫描的行数可能多出几十倍，在订单表上千万行的时候差距非常明显。

6.3 null 参与比较永远不成立

NULL在 SQL 里表示"未知"，不是"空字符串"，也不是"0"。所以NULL = NULL的结果是UNKNOWN而不是TRUE，NULL <> 1也是UNKNOWN，而WHERE只保留结果为TRUE的行。这一条带来的坑主要有三个：

• WHERE p.pay_amount > 0会把NULL行筛掉，LEFT JOIN效果被破坏。
• NOT IN (SELECT ...)里如果子查询结果含NULL，整个条件返回UNKNOWN，查询结果为空。
• ON a.code = b.code在code为NULL时匹配不上，而"两个都是NULL应该算匹配"这种语义需求得用IS NOT DISTINCT FROM（部分数据库支持）或COALESCE包一层来实现。

第三个坑在数据清洗里特别常见。比如两张表都有"手机号"字段，允许为空，你希望空对空的记录也算对上，那就得写成：

SELECT * FROM a JOIN b ON COALESCE(a.phone, '') = COALESCE(b.phone, '');

代价是COALESCE会阻止索引使用，数据量大时性能很差。更划算的做法是在入库阶段就给这类字段填默认值，让NULL尽量不出现。

6.4 数据类型不一致导致的隐式转换

比NULL更隐蔽的是字段类型不匹配。左边是VARCHAR的手机号，右边是BIGINT的用户 id，SQL 引擎会做隐式类型转换，结果通常是索引失效加潜在的精度问题。我遇到过一个很典型的场景：从 Oracle 导出身份证号时，导出的列是NUMBER类型，客户端直接把 18 位数字显示成了科学计数法，看着像数据被破坏了，其实是显示问题。解决办法是用TO_CHAR(id_no)显式转成字符串再导出，或者在导出工具里把这一列的格式设成文本。

同样的道理，做 join 之前两边的关联键类型最好完全一致。VARCHAR对VARCHAR、BIGINT对BIGINT，别指望数据库帮你兜底，它兜了底你也拿不到索引。

7. 看执行计划定位慢 join 的实际路径

7.1 三种常见数据库里怎么看计划

SQL 写完之后，判断它跑得快不快，唯一可靠的办法是看执行计划。几种常见数据库的入口不太一样：

MySQL 的EXPLAIN是最常用的，type列能直接反映访问方式。按效率从好到差大致是system > const > eq_ref > ref > range > index > ALL。看到ALL基本就是全表扫描，得考虑加索引了。rows列是优化器估算的扫描行数，注意是估算值，跟实际可能差很远，这也是为什么推荐用EXPLAIN ANALYZE——它会真正执行语句并给出实际行数，对比估算和实际能快速定位统计信息问题。

7.2 计划里最该盯的几个信号

我一般按这个顺序扫一遍计划：

• type = ALL出现在大表上：基本可以确定缺索引，或者索引没被用上。
• rows估算值和大表实际行数接近：说明优化器选它当驱动表了，如果这张表很大，查询会很慢。
• Extra里出现Using temporary或Using filesort：说明有临时表或额外排序，通常由GROUP BY、ORDER BY或DISTINCT引起，能优化就优化。
• Extra里出现Using join buffer (Block Nested Loop)：说明被驱动表的关联列没有索引，走了 BNL，大表上非常慢。
• 关联顺序：多表 join 时，排在前面的是驱动表，看它是不是小表。

这里有个经验：如果一个 join 查询里有Using join buffer和Using temporary同时出现，基本可以断定是索引设计有问题，先别急着改 SQL，去补索引往往一招见效。

7.3 索引怎么建才吃得上下推

关联字段上的索引要按"被驱动表的关联列"来建。比如A JOIN B ON A.id = B.a_id AND B.status = 'X'，如果B是被驱动表，那B(a_id, status)这样的复合索引通常是最优的：先按a_id定位，再按status过滤。如果status的选择性很高（比如 99% 的数据都是X），那把status放在前面反而不好。

还有个常被忽略的点是"谓词下推"。优化器会尽量把过滤条件下推到数据扫描阶段，减少进入 join 的行数。但下推有时候受限于函数包裹、类型转换或子查询结构。子查询里如果套了LIMIT、ORDER BY或者聚合函数，下推就可能失败，导致先全量 join 再过滤。所以看到慢查询时，把子查询展开手动改写一遍，往往能验证是不是下推没生效。

7.4 分页加 join 的慢查询套路

最后说一个特别高频的慢查询：分页列表带多表 join。

SELECT o.*, u.name FROM orders o LEFT JOIN users u ON o.user_id = u.id ORDER BY o.create_time DESC LIMIT 20 OFFSET 100000;

OFFSET越大越慢，因为数据库要先扫描并丢弃前面 10 万行。join 又让每行的处理成本更高。优化思路是先用主表定位到 20 行的 id，再拿这 20 个 id 去关联其他表：

SELECT o.*, u.name FROM ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 100000 ) t JOIN orders o ON o.id = t.id LEFT JOIN users u ON o.user_id = u.id;

这样 join 的输入只有 20 行，成本从"全量 join 后再丢弃"变成"先丢弃再 join"。如果业务允许，用游标分页（记录上一页最后一条的create_time，下一页从它之后取）效果更好，可以完全避免OFFSET。

8. 业务里怎么选 join：几个真实场景的取舍

8.1 订单与明细：一对多的正确姿势

订单主表和订单明细是一对多的典型结构。要"查订单列表并带上明细条数"，正确做法是先对明细聚合：

SELECT o.order_no, o.amount, COALESCE(d.cnt, 0) AS item_cnt FROM orders o LEFT JOIN ( SELECT order_no, COUNT(*) AS cnt FROM order_items GROUP BY order_no ) d ON o.order_no = d.order_no;

如果直接LEFT JOIN order_items再COUNT，主表的amount会被重复累加，金额统计直接错掉。这类"金额对不上"的问题，十次里有八次是这个原因。

8.2 主表补维度：left join 的经典用法

列表页展示时，主表往往只有编码没有名称，需要LEFT JOIN各种字典表补全。比如订单表里存的是channel_code，要展示渠道名称：

SELECT o.order_no, c.channel_name FROM orders o LEFT JOIN channels c ON o.channel_code = c.code;

这里用LEFT JOIN而不是INNER JOIN是刻意的：字典表可能缺某条记录，如果用了INNER JOIN，这一整条订单在列表里就消失了，业务方看到的是"订单不见了"，比"渠道显示为空"严重得多。补维度一律用LEFT JOIN，这是我给自己定的规矩。

8.3 存在性判断：exists 往往比 join 更合适

"查所有下过单的用户"这种需求，如果只关心用户本身、不关心订单信息，用EXISTS更直接：

SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

EXISTS的语义就是"存在即返回"，一旦找到匹配行就短路，不需要处理右表的多行，也不需要DISTINCT去重。反过来"查所有没下过单的用户"用NOT EXISTS，注意别用NOT IN，前面提到的NULL陷阱会让结果直接为空。

8.4 Hive 场景下 join 的几个特别之处

如果数据量到了需要离线计算的程度，Hive SQL 的 join 有些和关系型数据库不一样的脾气要提前知道。首先是LEFT JOIN的左表不能是子查询里带WHERE的大表，容易触发数据倾斜；其次是 Hive 默认不支持ON里的非等值条件，a.id > b.id这种写法会走笛卡尔积，必须换成CROSS JOIN加WHERE或者改写逻辑。另外，Hive 里小表可以走 Map Join，把整个小表广播到每个节点，效率远高于普通 Reduce Join，前提是小表真的够小，一般控制在几十兆以内。

还有一点，Hive 的LEFT SEMI JOIN就是半连接的语法糖，作用和EXISTS类似，但只能用在支持EXISTS还不够高效的场景里。我个人在离线任务里更倾向显式写LEFT JOIN加聚合，因为语义更直观，出问题也更容易排查。

8.5 一个小的判断流程

最后把我自己选 join 类型的判断流程贴出来，基本覆盖了日常八九成的情况：

1. 需要右表的信息，且只关心两边都有的行？用INNER JOIN。
2. 需要保留左表所有行，右表信息可有可无？用LEFT JOIN，右表的过滤条件放ON。
3. 只关心右表存不存在，不需要右表字段？用EXISTS或NOT EXISTS。
4. 需要双向比对找出差异行？用FULL OUTER JOIN或左右 join 加UNION。
5. 需要补齐缺失的维度组合？用CROSS JOIN，确认有一侧足够小。
6. 关联后行数变多且不符合预期？先查右表关联键是否唯一，考虑先聚合再 join。

这套流程不复杂，但每次写 join 之前过一遍，能避掉绝大多数"结果不对"的问题。剩下那些真正棘手的慢查询，就得靠执行计划和索引慢慢磨了。

我自己的体会是，刚开始写 SQL 时总觉得 join 就是"把两张表拼起来"，写得多了才发现，ON和WHERE的位置、关联键的唯一性、NULL的传播规则这三件事，才是决定一条 join 语句对不对的关键。至于性能，先保证索引建在了被驱动表的关联列上，再去看执行计划里有没有Using join buffer，这个顺序能帮你省下大量无效的调优尝试。