做后端开发的朋友估计都见过那张经典的两圆相交图——SQL 里的各种 join 用法被压缩成几个彩色区域一眼扫过去觉得懂了真到写语句的时候又拿不准。我从最早的手写原生 SQL到后来用 ORM 框架、再回到手写复杂查询做报表和慢 SQL 优化join 这个词跟了我十来年踩过的坑能装一箩筐明明想保留左边的全部数据结果一过滤就变成了内连接明明以为一对一跑出来行数翻了三倍。这篇就把 join 从语义、写法、执行顺序到性能排查完整捋一遍用两张测试表把每种 join 的结果实实在在跑出来对照。不管你是刚学 SQL 语句的新人还是天天写复杂查询、偶尔要处理慢 SQL 的老手看完都能直接拿去用。1. 先想清楚join 到底在解决什么问题1.1 那张经典图为什么总让人误解两圆相交图本身没毛病坏就坏在它只画了结果集没画过程。图上告诉你「中间那块是 inner join」但没告诉你数据库是先做笛卡尔积再过滤还是先按索引定位再拼装没告诉你 on 后面的条件和 where 后面的条件在处理顺序上有什么区别。于是很多人记住了图案却没记住语义。我见过一个挺典型的场景业务要统计「所有用户的下单金额没下单的显示 0」。有人上来就写users join orders然后拿where orders.amount 0去过滤跑完发现没下单的用户全消失了。问题的根不在 join 语法而在于他没意识到过滤条件和连接条件放的位置不一样语义就完全变了。那张图能帮你记住形状但帮不了你记住这条规则。所以这篇我不打算再画一遍圆而是把 join 拆成三个层次来看语义层我要什么结果、写法层on 和 where 怎么写、执行层数据库怎么把它跑出来。三层对齐了join 就不会再写错。1.2 join 的本质按条件把两个集合拼起来抛开语法看本质join 做的就是一件事——根据一个布尔条件把左表和右表的行两两配对配上的留下配不上的按 join 类型决定要不要用空值补位。inner join只保留配上的组合配不上的两边都丢掉。left join左表每一行都必须出现至少一次右表配不上就用 null 填。right join反过来右表每行必须出现左表配不上用 null 填。full outer join两边都是「必须出现」谁配不上谁补 null。理解了「配不上怎么处理」这一条你就理解了全部 join 类型。图只是把这个规则可视化了而已。很多人记不住是因为把「保留哪边」当成了需要死记的东西其实只要问自己一句左边那行我丢不丢右表没找到对应行时我要不要看它答案自然就出来了。顺带提一句命名习惯实际项目里我很少见到有人用 right join绝大多数场景把表顺序调一下用 left join 就行。理由是人对「主表放左边」这件事有天然的心理惯性混用 left 和 right 会让后来维护的人反复对照语句和需求。团队里如果有规范通常也是一条统一用 left join禁止 right join。1.3 搞清楚这几种日常 90% 的场景就够用了按我的经验真实业务里的 join 分布大概是这样的left join 占一半以上inner join 占三成左右剩下的被 cross join多为误用、full outer join多为报表、以及各种 join 加聚合的组合瓜分。这个分布其实很说明问题。业务查询往往以某张主表为中心向外扩展信息比如以用户为中心看订单、看积分、看登录记录天然就是 left join 的形态。而 inner join 通常出现在「必须有对应数据才算数」的场景比如统计有效订单、关联明细表做汇总。所以如果你刚开始学 SQL优先级建议是inner join 和 left join 吃透反连接left join 加 is null会写其他几种知道语义、用到时查一下即可。别在一开始就纠结 full outer join 在不同数据库里的兼容性那不是现阶段的主要矛盾。2. 手把手建两张表把每种 join 跑一遍2.1 测试表结构和造数脚本光看语法不动手等于没学。下面这段脚本你可以在 MySQL、SQL Server、PostgreSQL 里基本通用日期类型和自增语法略有差异这里用的都是标准写法通用性最好。CREATE TABLE users ( user_id INT PRIMARY KEY, user_name VARCHAR(32), city VARCHAR(32) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), status VARCHAR(16) ); INSERT INTO users VALUES (1, 张三, 北京), (2, 李四, 上海), (3, 王五, 广州), (4, 赵六, 深圳); INSERT INTO orders VALUES (101, 1, 99.00, paid), (102, 1, 50.00, paid), (103, 2, 200.00, unpaid), (104, 5, 30.00, paid);这份数据我特意设计了几个「不干净」的地方因为真实数据库里从来不干净。赵六user_id4一单都没有属于左表有右表没有订单 104 的 user_id5 在 users 里根本不存在属于右表有左表没有业务上叫「孤儿数据」一般是删用户没清订单或者数据同步出错留下的张三有两单属于典型的一对多。这三类情况覆盖了 join 里所有容易出问题的边界。提示造测试数据时一定要主动制造「对不上的行」和「一对多的行」只用整齐的一对一数据做验证等于没验证。2.2 六种 join 的语句和结果对照先上 inner join也就是最直观的一种SELECT u.user_name, o.order_id, o.amount FROM users u INNER JOIN orders o ON u.user_id o.user_id;结果三行张三配 101、张三配 102、李四配 103。王五和赵六被丢掉因为没订单订单 104 也被丢掉因为找不到用户。注意张三出现了两次因为他在 orders 侧有两行——这就是一对多导致的「行数放大」后面讲性能问题时会专门说这个。left join 把左表兜住SELECT u.user_name, o.order_id, o.amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id;结果五行张三两行、李四一行、王五一行order_id 和 amount 全是 null、赵六一行同样全 null。判断一行是「真的没订单」还是「订单字段本身就是空」靠 order_id 是否为 null 来判断因为主键理论上不该为空。right join 就是把上面的表顺序换一下结果集是订单 101、102、103、104 四行其中 104 对应的用户字段全是 null。实际业务里写 right join 的收益很低还是那句话调换表顺序用 left join 更清楚。full outer join 在 MySQL 里没有原生支持标准写法是这样SELECT u.user_name, o.order_id FROM users u FULL OUTER JOIN orders o ON u.user_id o.user_id;结果六行五条正常配对加一条孤儿订单。MySQL 里要模拟它用left join和right join的结果做unionSELECT u.user_name, o.order_id FROM users u LEFT JOIN orders o ON u.user_id o.user_id UNION SELECT u.user_name, o.order_id FROM users u RIGHT JOIN orders o ON u.user_id o.user_id;这里必须用union而不是union all否则中间那些两边都配上的行会出现两次。这个坑我亲眼见过有人写错报表数据直接翻倍。把六种 join 的关键差异整理成一张表方便你对着看join 类型左表未匹配行右表未匹配行典型用途inner join丢弃丢弃只统计有关联的记录left join保留右表补 null丢弃以左表为主体扩展信息right join丢弃保留左表补 null同上习惯上少用full outer join保留补 null保留补 null双向对账、找差异cross join全部组合全部组合生成笛卡尔积、造序列left join is null保留且右侧一定为空不涉及找出「没有关联数据」的主体2.3 反连接找出「没有订单的用户」反连接这个说法听起来高级写起来其实就一行SELECT u.user_name FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.user_id IS NULL;结果是王五和赵六。这里的关键在于IS NULL的判断列必须选右表中「因为配不上而被补 null」的那个字段而且这个字段在真实数据里要保证不会本身就有 null否则会误伤。所以通常选右表的主键或非空列来判空订单表这里选o.order_id比o.user_id更稳妥。反连接在业务里的使用频率非常高找出没有登录过的用户、找出没有配置权限的账号、找出没有关联明细的主单。用NOT EXISTS也能实现同样效果语义更直白SELECT u.user_name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );两种写法在不同数据库里的执行效率可能有差异老版本 MySQL 里NOT IN遇到子查询结果有 null 时会返回空集这个坑非常经典而NOT EXISTS和左连接反写相对安全。所以我的建议是子查询里可能出现 null 的场合避开NOT IN用NOT EXISTS或者左连接判空。2.4 cross join 不是不能用是不能乱用cross join生成的是笛卡尔积四行 users 乘四行 orders 等于十六行。很多教程把它描述成「危险操作」其实它本身没罪问题在于很多人是不小心写出笛卡尔积的——比如 join 条件写漏了、或者写成了恒真条件-- 危险条件恒成立等价于笛卡尔积 SELECT * FROM users u JOIN orders o ON 1 1;四行对四行是十六行如果是四万行对四万行就是十六亿行数据库轻则卡死重则 OOM。我在线上见过一次因为 join 条件里字段类型不一致一边 int 一边 varchar导致隐式转换、索引失效最终执行计划退化成笛卡尔积直接把一个查询从毫秒级拖到分钟级。但正经用途也有生成日期序列、做商品规格的笛卡尔组合颜色乘尺码、构造测试数据。这种时候用 cross join 是明确意图一点问题没有。区别就在于——你是故意要全部组合还是不小心得到了全部组合。3. on 和 where 的区别left join 最大的坑在这里3.1 一条语句两种结果这是我觉得比 join 类型本身更值得讲清楚的一件事。看下面两条语句只差一个条件位置-- 写法 A条件在 on SELECT u.user_name, o.order_id FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.status paid; -- 写法 B条件在 where SELECT u.user_name, o.order_id FROM users u LEFT JOIN orders o ON u.user_id o.user_id WHERE o.status paid;写法 A 的结果是张三两行都是 paid、李四一行但订单字段为 null、王五一行 null、赵六一行 null总共五行。因为o.status paid只是连接条件的一部分李四的订单不满足它但不影响李四这行出现在结果里——只是右表按 null 补。写法 B 的结果是只有张三两行总共两行。因为 where 是在 join 完成之后才执行的过滤李四那行的o.status已经是 null 了null paid结果为未知直接被过滤掉王五、赵六同样没了。写法 B 的 left join 事实上退化成了 inner join。这个差异在实际项目里造成的 bug 太多了而且往往不容易发现因为数据量小的时候你可能根本没注意到少了几行。我自己的习惯是写 left join 时只要条件涉及右表先问一句「这个条件是想筛选关联结果还是想筛选主表数据」。前者写 on后者写 where。3.2 一个简单的判断口诀我总结过一个判断方法用起来挺顺手条件用来限制两边怎么配对写在ON后面。条件用来限制最终结果集写在WHERE后面。条件作用在主表保留全部的那一侧写WHERE后面基本没错。条件作用在从表可能被补 null 的那一侧写ON后面除非你确实想过滤掉主表的行。还有个小技巧如果你不确定某个条件该放哪把语句跑两遍对比行数。行数变化了说明这个条件对结果集有实质影响行数没变但 null 变多了说明它影响的是配对过程。注意有些数据库的优化器会把WHERE里针对从表的IS NULL条件识别为反连接并做等价改写但这属于优化层面的行为不能作为你写错位置的理由。语义正确永远优先于依赖优化器。4. 多表 join 时怎么理清表和条件4.1 表顺序其实影响可读性也影响性能三张表以上 join 的时候最容易乱。我一般遵循两条原则主表放最前面从表按业务从属关系依次排开每个 join 只关联它前面已经出现过的表之一尽量不要跨表跳着关联。比如用户、订单、订单明细三张表SELECT u.user_name, o.order_id, d.product_name, d.qty FROM users u LEFT JOIN orders o ON u.user_id o.user_id LEFT JOIN order_detail d ON o.order_id d.order_id;这样写每一步的连接关系都是线性的读的时候顺着往下走就行。如果写成users直接 joinorder_detail跳过 orders虽然语法上可以但逻辑上跳了一层条件维护起来容易出错尤其是后面要加过滤的时候。至于「left join 之后再 left join前一个 join 产生的 null 会不会影响后一个」答案是会的。如果o.order_id因为没匹配到而是 null那么d.order_id o.order_id也匹配不上任何东西明细字段同样是 null。这是符合语义的但你要清楚结果里每一列可能是被「传播」过来的 null。4.2 混合 join 类型时的语义叠加真实查询里经常出现 inner join 和 left join 混用。这时候要小心只要链条中出现一个 inner join前面的 left join 保住的行可能就被它砍掉了。SELECT u.user_name, o.order_id, p.pay_no FROM users u LEFT JOIN orders o ON u.user_id o.user_id INNER JOIN payment p ON o.order_id p.order_id;这里的 inner join 要求o.order_id必须有对应支付记录而没订单的用户o.order_id是 null必然匹配不上所以赵六和王五全被过滤掉了。整条语句的实际效果接近于以支付记录为中心的内连接。这种写法不算错但意图容易误读。如果本意是「保留所有用户有支付信息就带上」应该改成LEFT JOIN payment p。我现在的习惯是写完之后把语句在脑子里按「从内到外、从连接到过滤」跑一遍检查每一步丢掉了哪些行尤其是那些被补上 null 的行还能不能活到最后。5. join 写对了为什么还是慢5.1 三种物理连接算法知道名字就够用了语法层只告诉数据库「我要什么」具体怎么跑是优化器的事。目前主流的物理连接算法就三种算法大致思路适合场景嵌套循环外层每行去内层找匹配外层结果集小、内层连接列有索引哈希连接小表建哈希表大表探测大表对大表、等值连接、无合适索引排序合并两边先排序再归并数据已有序、范围连接理解这三种算法的意义在于你会知道为什么「小表驱动大表」在嵌套循环下有优势外层循环次数少也会知道为什么给连接列建索引能救命把内层的全表扫描变成索引查找。嵌套循环的代价大致可以这样估外层行数乘以内层每次查找的代价。假设外层一万行内层每次查找走索引大概若干次磁盘读或内存读乘起来就是总代价。如果内层没索引每次查找变成全表扫描假设十万行那就是一万乘十万等于十亿次比较这就不是一个量级的差别了。5.2 索引怎么建才对连接列的索引是重中之重。经验规则被驱动表的连接列一定要有索引。以FROM users u JOIN orders o ON u.user_id o.user_id为例如果优化器选择 users 做外层那么 orders.user_id 上必须有索引反过来也要考虑。另外两个容易忽略的点第一联合索引的顺序。如果 orders 表上经常这样查先按 user_id 关联再按 status 过滤那么(user_id, status)的联合索引比单独两个索引更有效因为它能让过滤直接在索引里完成不用回表。第二索引列上的函数和隐式转换会废掉索引。WHERE DATE(o.created_at) 2025-01-01这种写法函数作用在列上索引基本用不上要改成范围查询o.created_at 2025-01-01 AND o.created_at 2025-01-02。字段类型不一致导致的隐式转换同理int 列和字符串比、字符集不同的两列相比都可能让索引失效。5.3 慢 SQL 排查的一份清单遇到 join 查询慢我一般按这个顺序看看执行计划。MySQL 用EXPLAIN需要真实耗时用EXPLAIN ANALYZE8.0 以上SQL Server 里看实际执行计划注意有没有出现大量的行数估算偏差Oracle 用EXPLAIN PLAN配合计划表。重点看 type 那一列MySQLALL是全表扫描index是全索引扫描range、ref、eq_ref、const依次更优。看估算行数和实际行数差多少。差一个数量级以上说明统计信息过期了先更新统计信息再看。看连接顺序。驱动表是不是小表被驱动表的连接列有没有走索引。看有没有排序和临时表。Using filesort和Using temporary是常见性能杀手通常和 group by、order by 有关。缩小结果集再 join。有时候把过滤提前到子查询里让参与 join 的数据量先降下来效果立竿见影。这里额外提一句EXPLAIN给出的行数只是估算值不要把它当成精确值来推理。真要看实际执行情况得用能输出运行时统计的工具比如 MySQL 的EXPLAIN ANALYZE会真的执行语句并给出每一步的实际耗时和实际行数。6. 几个实战里绕不开的场景6.1 一对多 join 导致行数膨胀怎么处理张三有两单users left join orders出来两行这是预期行为。但如果你的意图是「统计每个用户的订单总额」直接 join 再 sum 是对的SELECT u.user_id, u.user_name, SUM(o.amount) AS total FROM users u LEFT JOIN orders o ON u.user_id o.user_id GROUP BY u.user_id, u.user_name;注意这里用SUM(o.amount)没订单的用户求和结果会是 null因为所有被加的值都是 null如果业务要求显示 0得写COALESCE(SUM(o.amount), 0)或者某些数据库里的IFNULL。这个细节在报表里特别常见不加处理前端就会显示一片空白。但如果三张表连环 join问题会放大。users 一对多 ordersorders 一对多 order_detail那么 join 完之后每个订单会按明细条数再翻倍最后 sum 订单金额就会重复累加。这种时候正确的做法是先聚合再 join把明细聚合到订单粒度再和用户表连SELECT u.user_name, o.order_id, d.total_qty FROM users u JOIN orders o ON u.user_id o.user_id JOIN ( SELECT order_id, SUM(qty) AS total_qty FROM order_detail GROUP BY order_id ) d ON o.order_id d.order_id;这个模式我用了很多年凡是「多对多链条」的统计先想办法把中间层聚合掉再往上层 join。6.2 join、in、exists 到底选哪个这三者的选择经常被争论。我的经验是结果需要右表的字段用join。只需要「左表是否存在匹配」这个布尔判断用exists。右表是小而固定的集合比如状态码列表用in更直观。in里如果来自子查询且可能有 null一定要小心改写成exists或 join 更安全。现代优化器的能力已经很强很多时候这三种写法会被改写成相同的执行计划所以优先级排序应该是语义清晰度 性能。写完先看执行计划如果计划一样就直接选最好读的那个版本。6.3 有些 join 其实可以用窗口函数替代SQL 窗口函数普及之后一部分「自连接」场景可以省掉。典型的是「取每个用户的最近一单」-- 传统写法自连接找最大值 SELECT u.user_name, o.order_id, o.amount FROM users u JOIN orders o ON u.user_id o.user_id JOIN ( SELECT user_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id ) m ON o.user_id m.user_id AND o.amount m.max_amount;用窗口函数会清爽很多SELECT user_name, order_id, amount FROM ( SELECT u.user_name, o.order_id, o.amount, ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) AS rn FROM users u JOIN orders o ON u.user_id o.user_id ) t WHERE rn 1;而且上面那个自连接版本有个隐蔽问题如果某个用户有两单金额并列最高结果会出现两行而窗口函数版本可以通过ROW_NUMBER强制只留一行用RANK则保留并列。取一条还是取所有并列这是个业务问题得根据需求选函数。关于窗口函数和 join 的取舍我的判断标准是只要涉及「分组内排名」「分组内取前 N」优先考虑窗口函数可读性和可控性都更好。7. 常见问题速查表和我的使用习惯7.1 join 问题速查表现象大概率原因处理办法left join 后行数莫名减少过滤条件写在了 where 里作用在从表上把条件挪到 on或确认是否本该用 inner join结果行数比预期多存在一对多关系或者连接条件不唯一先聚合再 join或检查连接键是否唯一结果行数爆炸性增长连接条件写漏或恒真产生笛卡尔积检查 on 条件确认字段类型和字符集一致关联字段全为 null连接键类型不一致导致隐式转换或字符集不同统一字段类型和字符集NOT IN查不出任何数据子查询结果里包含 null改用NOT EXISTS或左连接判空查询突然变慢统计信息过期、索引失效、数据量增长更新统计信息、重新查看执行计划排序字段是右表的列null 排在最前不同数据库 null 排序规则不同显式指定排序或把 null 转成特定值用UNION合并时数据重复应该用UNION ALL却用了UNION或反之明确是否需要去重关于 null 的排序这个点特别容易被忽略MySQL 里 null 默认排在最前某些数据库里 null 默认排在最后同一个查询换数据库跑结果顺序就不一样。如果你的业务依赖排序结果一定要显式处理别依赖默认行为。7.2 我自己的几条使用习惯写了这么多年 join有几点是我一直坚持的分享出来供参考。第一永远给表起短别名并且别名要有意义。users u、orders o这种比t1、t2强太多尤其在七八张表的报表查询里别名是唯一能让你三分钟后还看得懂语句的线索。第二生产环境执行的查询尽量不要用SELECT *。join 场景下SELECT *的危害加倍因为多表的同名字段会被全部返回前端或者代码取值时经常取错列而且一旦加了新字段查询的返回结构和网络传输量都会变。明确列出需要的列顺带还能让优化器有机会用上覆盖索引。第三写完复杂 join 一定要拿边界数据验证。至少测三种情况两边都匹配上的、左边有右边没有的、右边有左边没有的。我习惯在造数据时就把这三类行准备好跑完对着结果数一数行数比事后在线上排查便宜得多。第四养成看执行计划的习惯哪怕这次查询不慢。看多了你对「什么写法会走什么计划」会有直觉等到真出问题时排查速度完全不一样。第五join 的字段类型和字符集在建表阶段就对齐。这类问题排查起来最费时间因为语句看起来完全正确只有对比表结构才能发现差异。与其事后救火不如建表时统一规范。第六关于安全性写查询时涉及用户输入的过滤条件一律走参数化传参不要用字符串拼接的方式把用户输入拼进 SQL 语句里。这不只是规范问题拼接方式很容易让数据被当成语句的一部分执行参数化绑定则天然把数据和语句分开是最省事的防御手段。最后再补一个我最近才想明白的点。很多人学 join 卡住是因为一开始就想把那张图和所有类型全背下来。我的建议反过来先把SELECT、FROM、WHERE、GROUP BY的逻辑执行顺序彻底搞明白再去理解 join 只是FROM阶段里的一件事而且发生在WHERE之前。顺序一清楚on 和 where 的区别、null 为什么会传递、什么时候会退化成内连接全都能自己推出来不需要死记。至于那条EXPLAIN语句我在处理慢 SQL 的时候基本是本能反应先看计划再说别的比上来就改语句高效得多。这个习惯大概是从一次线上事故之后养成的——当时折腾了两个小时改写法最后发现问题只是统计信息太旧导致优化器选错了连接顺序更新一下就好了。