★ FEATURED ARTICLE 做数据这行十来年被问得最多的问题里这几个 join 到底啥区别绝对排得上前三。尤其是刚入行的同学面试前背了八百遍 left join 保留左表、right join 保留右表真到了写 SQL 的时候一遇到多表关联、条件过滤、NULL 值处理立马就懵。我自己早年也踩过不少坑最典型的一次是写报表统计明明用的是 left join结果数据量比预期少了一大截排查了大半天才发现是把右表的过滤条件写进了 where 里硬生生把外连接变成了内连接。这种错误光看文字定义是看不出来的必须把表画出来、把数据摆出来一眼就能明白问题出在哪。这篇内容就是想把 left join、right join、inner join 这三个最常用的连接方式用看图摆数据的方式彻底讲透。我不会只给你背定义而是把每种的执行逻辑、结果集长什么样、什么时候该用哪种、以及那些文档里不会写的坑全部摊开来讲。适合刚学 SQL 的新手打基础也适合工作几年但对外连接理解还停留在表面的朋友查漏补缺。看完你至少能做到拿到一个需求能立刻判断该用哪种 join并且知道怎么写条件才不会翻车。1. 三种连接的本质区别与选型逻辑1.1 用两张名单的生活场景理解连接很多人学 join 卡壳是因为一上来就盯着 SQL 语法看脑子里没有画面。我习惯用生活场景来类比理解起来会快很多。假设你有两张名单一张是员工花名册记录了公司所有在职员工的名字和部门另一张是本月考勤表只记录了打过卡的员工名字和打卡次数。现在你要做统计不同的需求对应不同的连接方式。inner join内连接就像是把两张名单叠在一起只保留两边都出现的人。花名册里有、考勤表里也有的员工才会出现在结果里。那些没打卡的员工、以及考勤表里误记录的已离职人员统统不要。这就是交集的概念。left join左连接是以左边的花名册为准把考勤信息贴上去。花名册里每个员工都要出现打过卡的显示打卡次数没打过卡的打卡次数显示为空NULL。考勤表里有但花名册里没有的人不会出现在结果里。这就是以左表为基准右表有就匹配没有就补空。right join右连接反过来以右边的考勤表为准把花名册信息贴上去。考勤表里每个人都出现能匹配到花名册的就显示部门匹配不到的部门显示为空。花名册里有但没打卡的人不会出现。这个类比的关键在于谁是基准表谁就决定结果集的行数下限。左连接左表是基准右连接右表是基准内连接则是两边共同决定。你把这句话刻在脑子里后面所有的细节都是围绕它展开的。1.2 一张对照表看清结果集差异光说概念还是虚我直接上一组具体数据把三种连接的结果摆出来对比。假设有两张表表 A左表idname1张三2李四3王五表 B右表idscore290385470用A.id B.id作为连接条件三种连接的结果如下连接方式结果行数结果内容inner join2 行id2张三,90、id3王五,85left join3 行id1张三,NULL、id2李四,90、id3王五,85right join3 行id2李四,90、id3王五,85、id4NULL,70从这张表能直接读出几个关键信息。inner join 只保留两边 id 都能对上的记录A 表的 id1 和 B 表的 id4 都被丢弃了。left join 保留了 A 表全部三行B 表对不上的 id1 补 NULL。right join 保留了 B 表全部三行A 表对不上的 id4 补 NULL。这里有个容易被忽略的细节left join 和 right join 的结果行数不一定等于基准表的行数。如果右表存在一对多的关系比如一个员工对应多条考勤记录那 left join 之后行数会膨胀。这一点后面讲笛卡尔积陷阱的时候会详细说先埋个伏笔。1.3 选型时先问自己三个问题实际工作中怎么快速判断该用哪种我总结了一个三步判断法基本能覆盖 90% 的场景。第一个问题结果集必须以哪张表为准如果业务要求所有员工都要列出来哪怕没数据那基准就是员工表用 left join把员工表放左边或者 right join把员工表放右边。如果只关心两边都有数据的匹配项那就 inner join。第二个问题需不需要保留没有匹配的记录这是区分内连接和外连接的核心。inner join 会无情地丢掉所有匹配不上的行而 left/right join 会保留基准表的所有行。很多统计需求比如找出没有下过单的用户列出所有部门包括没有员工的部门本质都是在找没有匹配的记录这时候必须用外连接。第三个问题过滤条件该放 on 还是 where这个问题是外连接翻车的重灾区我在第 3 节会专门展开。这里先给结论针对基准表的过滤放 where针对非基准表的过滤放 on放错位置结果天差地别。把这三个问题过一遍选型基本就不会错了。我见过太多人上来就写 left join问他为什么答不上来纯粹是习惯性动作。这种肌肉记忆在简单查询里不出错一旦涉及复杂条件就会埋雷。2. 执行原理拆解数据库到底怎么算的2.1 嵌套循环最直观的连接算法要真正理解 join 的行为得知道数据库底层是怎么执行的。虽然实际数据库会用各种优化算法但最基础、最好理解的模型是嵌套循环连接Nested Loop Join。拿 left join 举例逻辑大概是这样从左边基准表取第一行然后扫描右边表找所有满足连接条件的行匹配上就拼成结果行输出如果右边一行都没匹配上就用 NULL 补齐右表的字段照样输出一行。然后取左表第二行重复上述过程直到左表遍历完。这个模型解释了很多现象。比如为什么 left join 的结果一定包含左表所有行——因为外层循环就是遍历左表每行至少输出一次。为什么右表一对多会导致行数膨胀——因为内层扫描可能匹配到多行每匹配一行就输出一条。inner join 的嵌套循环稍微不同左表每取一行扫描右表找匹配找到了才输出找不到就跳过。所以两边匹配不上的行都不会出现在结果里。right join 可以理解成把 left join 的左右表对调执行逻辑完全对称。实际上很多数据库优化器会把 right join 改写成 left join 来处理因为统一成一种形式更好优化。2.2 为什么 right join 用得少说到这我得插一句实操经验在真实项目里right join 的出现频率远低于 left join。这不是因为它没用而是因为人的阅读习惯是从左往右把基准表放在左边、用 left join 表达逻辑上更顺。我带的几个新人一开始总爱用 right join理由是我要以这张表为准所以把它放右边。结果代码 review 的时候别人读起来要在大脑里做一次左右翻转很费劲。后来我统一要求能用 left join 表达的一律用 left join把基准表放左边。right join 只在极少数情况下用比如接手别人的老代码不想大改或者某些特定优化场景。这个习惯带来的好处是代码可读性大幅提升。你看到 left join立刻知道左边的表是主角右边的表是来补充信息的一目了然。团队协作里这种一致性比省几个字符重要得多。2.3 连接条件与过滤条件的执行时机差异这是理解 join 的关键中的关键我必须掰开揉碎讲。SQL 的执行顺序里on 子句是在连接过程中生效的where 子句是在连接完成后生效的。这个差异在内连接里看不出来因为内连接反正只保留匹配行条件放哪结果都一样。但在外连接里这个差异是致命的。拿 left join 举例。on 条件的作用是决定右表哪些行能匹配上左表当前行。如果右表某行不满足 on 条件它就不参与匹配左表这行就按没匹配上处理右表字段补 NULL但这行左表数据依然保留。where 条件的作用是对连接完成后的结果集做过滤。如果某行结果不满足 where 条件整行被删除包括左表的数据。举个具体例子。还是员工表和考勤表我想查所有员工以及他们 3 月份的打卡次数。正确写法是把月份条件放 on 里SELECT e.name, a.count FROM employee e LEFT JOIN attendance a ON e.id a.emp_id AND a.month 2024-03;这样每个员工都会出现3 月打过卡的显示次数没打过的显示 NULL。因为月份条件在匹配阶段就把非 3 月的考勤记录排除了左表员工不受影响。如果写成这样SELECT e.name, a.count FROM employee e LEFT JOIN attendance a ON e.id a.emp_id WHERE a.month 2024-03;结果就完全变了。连接阶段先把所有月份的考勤都匹配上然后 where 过滤掉非 3 月的行。对于那些 3 月没打卡的员工他们匹配出来的行 month 是 NULLNULL 2024-03结果为假整行被删掉。最终结果里只剩下 3 月打过卡的员工left join 退化成了 inner join。这个坑我踩过不止一次也见过无数人踩。记住一句话外连接里对非基准表的过滤条件想清楚是匹配前筛还是匹配后筛前者放 on后者放 where。3. 实操演示从建表到结果验证的完整流程3.1 建表与造数据光看图不够得自己动手跑一遍才有体感。我用一段可以直接复制执行的 SQL 把环境搭起来MySQL、PostgreSQL、SQLite 都能跑语法基本通用。-- 建左表部门表 CREATE TABLE dept ( id INT PRIMARY KEY, dept_name VARCHAR(50) ); -- 建右表员工表 CREATE TABLE emp ( id INT PRIMARY KEY, emp_name VARCHAR(50), dept_id INT, salary INT ); -- 插入部门数据 INSERT INTO dept VALUES (1, 研发部), (2, 市场部), (3, 财务部), (4, 行政部); -- 插入员工数据 INSERT INTO emp VALUES (101, 张三, 1, 15000), (102, 李四, 1, 18000), (103, 王五, 2, 12000), (104, 赵六, NULL, 9000), (105, 钱七, 5, 11000);注意我故意设计了几个特殊数据部门表里行政部id4没有员工员工表里赵六的 dept_id 是 NULL钱七的 dept_id 是 5一个不存在的部门。这几个脏数据就是用来验证各种连接行为的试金石。3.2 三种连接的实测结果对比先跑 inner join查每个部门及其员工SELECT d.dept_name, e.emp_name FROM dept d INNER JOIN emp e ON d.id e.dept_id;结果只有 3 行研发部-张三、研发部-李四、市场部-王五。行政部没员工被丢弃赵六 dept_id 为 NULL 匹配不上被丢弃钱七的部门 5 不存在也被丢弃。这就是内连接的严格。再跑 left join同样以部门为基准SELECT d.dept_name, e.emp_name FROM dept d LEFT JOIN emp e ON d.id e.dept_id;结果 4 行研发部两行、市场部一行、行政部一行emp_name 为 NULL。部门表四行全部保留行政部因为没员工所以补 NULL。但赵六和钱七依然不出现因为他们不在左表基准里。最后跑 right join以员工为基准SELECT d.dept_name, e.emp_name FROM dept d RIGHT JOIN emp e ON d.id e.dept_id;结果 5 行张三、李四、王五正常匹配赵六dept_name 为 NULL、钱七dept_name 为 NULL都出现了。员工表五行全部保留匹配不上部门的补 NULL。把这三个结果并排看基准表的概念就非常清晰了。inner join 谁都不偏袒只留匹配的left join 偏袒左表right join 偏袒右表。3.3 用 left join 模拟 right join 验证对称性前面说过 right join 可以改写成 left join我实际验证一下。把上面 right join 的查询改成 left join只需把两张表位置对调SELECT d.dept_name, e.emp_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id;跑出来的结果和 right join 完全一致5 行赵六和钱七的 dept_name 为 NULL。这就印证了那句话right join 就是把 left join 的左右表换个位置。理解了这一点你完全可以只用 left join 走天下right join 当作知识储备即可。这里顺便提一个验证技巧写完外连接后先数一下结果行数对不对。如果基准表有 N 行且右表对基准表是一对一或一对零的关系那结果应该正好 N 行。如果多了说明右表有一对多产生了行膨胀如果少了八成是过滤条件放错了位置。这个数行数的习惯帮我抓出过很多隐蔽的 bug。4. 高频翻车场景与排查实录4.1 一对多导致的重复行陷阱这是外连接最容易出问题的地方也是我见过最多的线上事故来源。假设员工表和一个项目参与表关联一个员工可能参与多个项目。现在要查每个员工的姓名和部门有人图省事直接写SELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN project p ON e.id p.emp_id LEFT JOIN dept d ON e.dept_id d.id;如果张三参与了 3 个项目结果里张三就会出现 3 次部门信息重复 3 遍。如果后面还要做聚合统计比如COUNT(e.id)数字直接翻倍报表全错。这个问题的根源在于left join 的行数由右表的匹配数量决定右表一对多就会放大左表的行。解决办法有两个。一是如果不需要项目信息就别 join 项目表只 join 部门表。二是如果确实需要项目信息又要统计员工数用COUNT(DISTINCT e.id)去重或者先用子查询把项目信息聚合好再 join。我个人的习惯是每次写多表 join 之前先想清楚每张表之间的基数关系一对一、一对多、多对多。一对多的关联要么明确知道会膨胀并做好去重要么就拆成多个查询分别处理。这个前置思考能省掉大量事后排查的时间。4.2 NULL 值参与比较的隐蔽问题NULL 在 SQL 里是个特殊存在它不等于任何值包括它自己。NULL NULL的结果是 NULL在条件判断里当作假处理NULL ! 5的结果也是 NULL。这个特性在外连接里会引发很多意外。比如你想查没有分配部门的员工直觉上会写SELECT * FROM emp WHERE dept_id ! 1;但 dept_id 为 NULL 的赵六不会被查出来因为NULL ! 1结果是 NULL不满足条件。正确写法要用IS NULL或者dept_id ! 1 OR dept_id IS NULL。再比如 left join 之后右表字段全是 NULL 的那些行代表没匹配上。想筛选这些行必须用WHERE 右表字段 IS NULL不能用 NULL。这个点看似基础但在复杂查询里尤其是多层嵌套 join 之后很容易忘记某列可能为 NULL 而写出错误的条件。我整理了一个 NULL 相关的速查表贴在工位上那种表达式结果说明NULL NULLNULL假不能用等号判断 NULLNULL ! 5NULL假任何与 NULL 的比较都是 NULLNULL IS NULL真判断 NULL 必须用 ISNULL 5NULL算术运算遇 NULL 结果为 NULLCOUNT(列)跳过 NULL聚合函数通常忽略 NULLCOUNT(*)包含 NULL 行统计所有行最后两行值得单独说。COUNT(列名)会跳过该列为 NULL 的行而COUNT(*)统计所有行。做统计时用错这两个数字会差很多。我见过有人用COUNT(右表.id)统计左表行数结果因为右表没匹配上导致 NULL 被跳过数字偏小排查了半天。4.3 常见问题速查与排查思路把上面这些坑和排查方法整理成一张表遇到问题按图索骥现象可能原因排查方向结果行数比基准表少过滤条件放错位置外连接退化成内连接检查 where 里是否有非基准表字段的条件移到 on 里结果行数比基准表多右表一对多导致行膨胀检查关联表的基数关系考虑去重或拆分查询该出现的 NULL 行没出现where 条件过滤掉了 NULL检查条件是否涉及 NULL 比较改用 IS NULL 或调整位置统计数字偏大一对多 join 后聚合未去重用 COUNT(DISTINCT) 或先聚合再 join统计数字偏小COUNT(列) 跳过了 NULL 行改用 COUNT(*) 或确认统计口径关联不上但数据明明存在关联字段类型或格式不一致检查字段类型、是否有空格、大小写、编码差异最后一行那个关联不上但数据明明存在我遇到过好几次。有一次是两张表的关联字段一个是 INT 一个是 VARCHAR数据库做了隐式转换某些值转换后对不上还有一次是字符串末尾有不可见空格肉眼看不出来。这类问题排查起来很折磨建议关联前先SELECT出来对比一下两边的值或者用TRIM、CAST统一格式。4.4 几个能省时间的实操心得分享几个我这些年攒下来的小技巧都是文档里不会写的。第一写复杂 join 前先用小数据验证。别一上来就在生产大表上跑先LIMIT 10或者用临时小表把逻辑跑通确认结果符合预期再放开。我习惯先构造几行边界数据NULL、不存在的关联 id、重复关联跑一遍看结果对不对这比事后排查快得多。第二善用 EXPLAIN 看执行计划。虽然本文重点不是性能优化但了解一下 join 的执行顺序对理解结果有帮助。EXPLAIN能告诉你哪张表是驱动表、用了什么连接算法、有没有走索引。有时候结果不符合预期看一眼执行计划就明白了。第三给关联字段建索引。这个严格说不算 join 语义问题但实际影响巨大。两张几十万行的表做 join关联字段没索引查询可能跑几分钟加上索引后秒级返回。尤其是外连接右表被反复扫描索引的作用更明显。第四养成先想基准表的习惯。每次写 join 之前先在纸上或注释里写一句本次以 XX 表为基准需要保留它的全部记录然后再决定用哪种 join、条件放哪。这个动作花不了几秒钟但能避免大部分选型和条件位置的错误。5. 进阶场景多表连接与条件组合5.1 三表以上连接的顺序问题实际业务里两张表搞定的情况不多经常是三张、四张表串起来。多表连接时join 的书写顺序会影响结果吗答案是对 inner join 不影响对外连接影响很大。因为 inner join 满足交换律和结合律A join B join C 和 C join B join A 结果一样。但外连接不满足left join 的基准表一旦确定后续的 join 都是在这个结果集基础上继续操作。所以多表外连接时基准表的确定和 join 的先后顺序必须想清楚。举个典型场景部门表 left join 员工表 left join 考勤表。这个链条的含义是以部门为基准关联员工再关联考勤。如果写成部门 left join 考勤 left join 员工含义就变成了以部门为基准先关联考勤再关联员工虽然最终可能碰巧结果相似但逻辑上完全不同一旦数据有缺失就会出问题。我的建议是多表外连接时把基准表放最左边然后按照业务逻辑的从属关系依次 join。部门包含员工员工产生考勤那就按部门→员工→考勤的顺序写读起来和业务逻辑一致不容易出错。5.2 on 条件里写多个条件的组合逻辑on 子句里可以写多个条件用 AND 或 OR 连接这在外连接里特别有用。前面讲的月份过滤放 on就是一个例子。再补充一个常见场景关联条件不只是 id 相等还要求状态匹配。比如查每个部门当前在职的员工SELECT d.dept_name, e.emp_name FROM dept d LEFT JOIN emp e ON d.id e.dept_id AND e.status active;这样离职员工不会匹配上但部门依然全部保留离职员工多的部门显示为 NULL 而不是消失。如果把e.status active放到 where 里那些只有离职员工的部门就会整个消失又退化成内连接了。这里有个判断口诀on 里的条件决定能不能匹配上where 里的条件决定匹配上的行要不要。想清楚你要的是哪种效果就知道条件该放哪了。5.3 用 coalesce 处理 NULL 让结果更友好外连接产生的 NULL 在展示层往往不友好用户看到空白不知道是没数据还是出错了。这时候可以用COALESCE或IFNULL给个默认值。SELECT d.dept_name, COALESCE(e.emp_name, 暂无员工) AS emp_name FROM dept d LEFT JOIN emp e ON d.id e.dept_id;COALESCE返回参数列表中第一个非 NULL 的值上面这个查询会把 NULL 的 emp_name 显示成暂无员工。MySQL 里也可以用IFNULL(e.emp_name, 暂无员工)效果一样。这个技巧在生成报表时特别实用能让结果更易读。不过要注意COALESCE 只影响展示不改变连接逻辑。如果你后续还要基于这列做判断或统计得小心默认值会不会干扰计算。比如把 NULL 转成 0 再求和和直接对 NULL 求和NULL 被忽略结果可能不同。这种地方要结合业务口径想清楚。6. 一张图记住所有要点6.1 文氏图之外的记忆锚点网上讲 join 最爱用文氏图两个圆圈的交集、并集直观是直观但有个问题文氏图只画了匹配和不匹配没画一对多和条件位置。所以光看图容易产生我懂了的错觉一写就错。我建议在文氏图之外再记三个锚点。锚点一基准表决定行数下限。left join 左表是基准right join 右表是基准inner join 两边共同决定。锚点二on 管匹配where 管过滤。外连接里条件放错位置结果天差地别。锚点三一对多会放大行数。join 之前先想清楚基数关系该去重去重该拆分拆分。这三个锚点覆盖了外连接 90% 的坑。文氏图帮你建立直觉锚点帮你避免翻车两者结合才算真懂。6.2 不同数据库的语法差异提醒最后提一下跨数据库的兼容性。left join、right join、inner join 是 SQL 标准主流数据库都支持语法基本一致。但有几个细节要注意。MySQL 不支持FULL OUTER JOIN全外连接需要用 left join 和 right join 做 union 来模拟。PostgreSQL、SQL Server、Oracle 都支持全外连接。另外IFNULL是 MySQL 特有标准写法是COALESCE跨库迁移时记得替换。还有一点某些数据库对 right join 的优化不如 left join 充分执行计划可能不同。这也是我建议统一用 left join 的另一个理由——不仅可读性好性能上也更稳妥。写 SQL 这件事语法只是皮毛真正决定水平的是对数据关系和执行逻辑的理解。left join、right join、inner join 这三个看似简单的东西背后牵扯到基准表选择、条件位置、基数关系、NULL 处理一整套思维。我自己的体会是把这些想透了后面学窗口函数、子查询、CTE 都会顺很多因为它们本质上都是在处理数据集之间的关系。下次写 join 之前不妨先停下来问自己那三个问题基准表是谁、要不要保留没匹配的、条件该放哪。养成这个习惯翻车的概率会直线下降。
阅读完成 · 觉得有帮助？
