SQL JOIN 这个话题说难不难说简单也经常翻车。前几天有个同事写报表一个 LEFT JOIN 下去结果行数莫名其妙多了一倍排查了半天最后发现是右表关联字段出现了重复数据。JOIN 的问题十个里有八个不是栽在语法上而是栽在没真正理解它背后发生了什么。这篇文章我不打算再放一张谁都看不懂的英文韦恩图就算完事。我会用一组可以直接复制到本地跑通的示例数据配合逐行箭头匹配图把 INNER、LEFT、RIGHT、FULL、CROSS 这五种核心 JOIN 的行为一次讲透同时把 ON 条件与 WHERE 条件混用、NULL 匹配、一对多导致的行数膨胀、连接字段类型不一致这些高频坑也一并聊掉。不管你是刚学 SQL 的新手还是已经写过不少业务查询、但没系统梳理过 JOIN 内部逻辑的开发都适合读下去。1. JOIN要解决的本质问题两张表如何变成一张表1.1 为什么好端端的表要拆开存先别急着看语法先把“为什么要 JOIN”想明白。我们建表的时候几乎不会把所有信息塞进一张表。比如员工表里每个员工只存一个dept_id而不是直接存“研发部”“市场部”这样的文本。为什么要这么设计因为一个部门下面可能挂着几十上百个员工。如果员工表直接存部门名称哪天部门改名了你就得扫全表把这几百行的字符串全改一遍。拆成两张表之后部门名称只在departments表里出现一次想改名只需改一行。这个“拆开存、查询时再拼回去”的建模方式就是规范化。它避免了数据冗余也避免了更新异常。但代价是查询的时候需要一种机制把分布在两张表里的信息按某种关系重新拼接起来。这个机制就是 JOIN。理解这一点你就能明白为什么 JOIN 不是 SQL 里某个可有可无的语法糖而是关系型数据库最核心的操作之一。1.2 不写JOIN的两种笨办法如果不用 JOIN想查出“每个员工属于哪个部门”怎么办第一种是逐行子查询比如SELECT name, (SELECT dept_name FROM departments d WHERE d.id e.dept_id) AS dept_name FROM employees e;这种写法本质上每查一行员工都要执行一次子查询。SQL 读起来绕性能也好不到哪去数据量一大数据库要被这种“逐行关联”拖垮。第二种是在业务代码里先查员工表然后循环每个dept_id再查一次部门表。这就是经典的 N1 查询问题一次主查询再加 N 次附加查询。接口响应时间随数据量线性恶化前端看着转圈后端看着监控报警。JOIN 的价值就在这里一次 SQL、一个结果集把匹配和拼接逻辑全部交给数据库让数据库自己选最优的执行路径。程序员要做的只是把连接条件写清楚。1.3 JOIN执行的逻辑三部曲笛卡尔积、ON、保留规则JOIN 表面上是一句 SQL逻辑上其实分三步走。第一步先把两表做笛卡尔积。笛卡尔积是什么说白了就是 A 表里每一行和 B 表里每一行都配对一次。employees有 5 行departments有 3 行笛卡尔积就是 15 行每个员工都会和三个部门各组合一次。第二步用 ON 条件过滤把真正对得上的组合留下。第三步根据外连接类型决定哪些额外行要保留INNER 只保留第二步过滤成功的LEFT 还要把左边没匹配上的行也保留右列补 NULLRIGHT 反过来FULL 则两边都保留。我强调一下现代数据库执行 JOIN 时不会真的把完整笛卡尔积生成出来而是会用嵌套循环、哈希连接等算法直接跳过大量无效组合。但逻辑上“先配对、再筛选、再补行”这个模型是理解所有 JOIN 行为的关键。后面每一个案例你都可以往这三步里套。2. 用行匹配图看懂五种核心JOIN2.1 示例表结构与数据长什么样后面所有的例子都用这两张表。employees是员工表departments是部门表员工表的dept_id指向部门表的id。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), dept_id INT ); CREATE TABLE departments ( id INT PRIMARY KEY, dept_name VARCHAR(50) ); INSERT INTO employees VALUES (1, 小张, 10), (2, 小李, 20), (3, 小王, 30), (4, 小赵, NULL), (5, 小钱, 20); INSERT INTO departments VALUES (10, 研发部), (20, 市场部), (40, 财务部);数据里特意埋了两个极端情况小王的dept_id30部门表里根本没有 30 号部门小赵的dept_id是 NULL。这俩就是后面各种 JOIN 展示“补 NULL”的最佳素材。建议你把这些语句直接复制到本地跑一遍后面的结果都能亲手验证。2.2 INNER JOIN只留下两边都有的INNER JOIN 的匹配图长这样员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配丢弃 (4, 小赵, NULL) 无匹配丢弃 (5, 小钱, 20) ──────────▶ (20, 市场部)所谓内连接就是两个集合的交集只有员工能匹配上部门、且部门也能被员工匹配到的行才会出现在结果里。小王虽然属于“30 号部门”但departments里没有这一行他在这条查询里就查不到小赵因为dept_id是 NULL也匹配不上。INNER JOIN 不补 NULL 行两边只要有一边缺失整行直接过滤掉。SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.id;2.3 LEFT JOIN 与 RIGHT JOIN谁是大爷谁全保留LEFT JOIN 的匹配图员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配 → 右列补 NULL (4, 小赵, NULL) 无匹配 → 右列补 NULL (5, 小钱, 20) ──────────▶ (20, 市场部)LEFT JOIN 的规则一句话左表行全保留右表能匹配就填部门名不能匹配就补 NULL。在这个例子中结果一定是 5 行因为员工表就 5 行。哪怕小王、小赵匹配不上他们也会出现在结果里只是部门名称是 NULL。这就是为什么报表里常用 LEFT JOIN——你知道主表是哪个其他信息只是“带出来”的附属字段。RIGHT JOIN 只是把方向反过来员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (5, 小钱, 20) ──────────▶ (20, 市场部) 无匹配 → 左列补 NULL (40, 财务部)右表 3 行全保留财务部没有员工所以员工相关列补 NULL。实际开发中 RIGHT JOIN 用得相对少因为只要把表的顺序反过来RIGHT 就变成了 LEFT。但理解它有助于看懂别人写的存量代码面试也爱考。2.4 FULL JOIN两边都不丢FULL OUTER JOIN 可以理解为 LEFT JOIN 和 RIGHT JOIN 的并集员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配 → 右列补 NULL (4, 小赵, NULL) 无匹配 → 右列补 NULL (5, 小钱, 20) ──────────▶ (20, 市场部) 无匹配 → 左列补 NULL (40, 财务部)两边的“孤儿数据”都会出现只是各自缺失的一侧会被 NULL 填充。MySQL 很长一段时间都没有 FULL JOIN 的语法后续我会给一个替代方案。在支持 FULL JOIN 的数据库里OUTER 这个词通常可以省略直接写 FULL JOIN 就行。2.5 CROSS JOIN误用会比想象中更可怕CROSS JOIN 就是没有 ON 条件的笛卡尔积。对这个特性CROSS JOIN 很少被业务查询使用但一旦误用结果行数会爆炸。比如对这两张表执行 CROSS JOIN直接返回 5×315 行这还只是小数据。如果左表 10 万行、右表 10 万行CROSS JOIN 就是 100 亿行数据库基本当场出问题。写法有两种SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d; -- 或者更老的写法 SELECT e.name, d.dept_name FROM employees e, departments d;第二种老式写法在存量代码里很常见如果你看到一条 SQL 没有 WHERE 也没有 ON就要警惕它是不是在偷偷做 CROSS JOIN。2.6 五种核心JOIN速查对照表JOIN 类型返回行策略业务场景空值来源INNER JOIN只返回匹配成功的行查“确定存在关联”的数据无LEFT JOIN左表全保留右表补 NULL主表在左附属信息可有可无右表列RIGHT JOIN右表全保留左表补 NULL主表在右等价于反过来写 LEFT左表列FULL JOIN两表全保留缺失补 NULL两表独立需要完整对账两侧都有CROSS JOIN两表全组合排列组合、生成序列无这张表可以当速查卡用。但光背结论不够下一章我们把每种 JOIN 的输出结果实际跑出来眼见为实。3. 同一份数据跑一遍各JOIN的真实输出长这样3.1 INNER JOIN 的 SQL 与输出对照理论讲完了直接看结果。INNER JOIN 的 SQL 和执行结果如下SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小钱市场部输出只有 3 行。小王和小赵没有出现在结果里因为30号部门和 NULL 都匹配不上任何部门。这个输出再次验证了 INNER JOIN 的“交集”本质——两边都缺一不可。3.2 LEFT JOIN 和 RIGHT JOIN 的方向差异验证LEFT JOINSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小王NULL小赵NULL小钱市场部RIGHT JOINSELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小钱市场部NULL财务部对比两张表能发现LEFT JOIN 保留了所有员工部门列有 2 个 NULLRIGHT JOIN 保留了所有部门员工列多了 1 个 NULL。NULL 出现的位置恰好就是主表一侧缺失的关联。财务部没有员工所以在 RIGHT JOIN 里员工列就是 NULL。3.3 MySQL 没有 FULL JOIN怎么用 UNION 模拟MySQL 不支持 FULL OUTER JOIN但可以用 LEFT JOIN 和 RIGHT JOIN 的结果做 UNION 去重来模拟SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;结果正好是 6 行5 个员工全在再加上财务部这一行。UNION 会自动去重所以 LEFT 和 RIGHT 查询中重复的那三行小张、小李、小钱只会保留一份。如果你故意写成 UNION ALL会得到 9 行多出的 3 行就是重复数据。这个细节在做数据统计时很容易踩坑一定要记得。3.4 CROSS JOIN 的输出规模估算CROSS JOIN 前面说过直接硬拼SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d;结果共 15 行前几行是namedept_name小张研发部小张市场部小张财务部小李研发部小李市场部小李财务部......你还可以把 CROSS JOIN 加 WHERE 条件手动模拟 INNER JOINSELECT e.name, d.dept_name FROM employees e, departments d WHERE e.dept_id d.id;结果跟前面的 INNER JOIN 完全一样。这是老式 SQL 的写法看到别慌能看懂就行。4. JOIN最容易翻车的那些坑ON、NULL与重复行4.1 同样一个条件放ON里和WHERE里结果完全不同这是 JOIN 里最经典、出错率最高的一个坑。同样写“部门 ID 等于 20”放 ON 和放 WHERE 差别巨大。条件放 ONSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id AND d.id 20;小张dept_id10不匹配 → 部门列为 NULL小李dept_id20 且 id20匹配 → 市场部小王dept_id30不匹配 → NULL小赵dept_idNULL不匹配 → NULL小钱dept_id20 且 id20匹配 → 市场部结果 5 行namedept_name小张NULL小李市场部小王NULL小赵NULL小钱市场部条件放 WHERESELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id WHERE d.id 20;结果只有 2 行namedept_name小李市场部小钱市场部原因很简单SQL 的逻辑执行顺序里ON 在 JOIN 时生效WHERE 在 JOIN 完成之后才对结果集做过滤。WHERE d.id 20会把“小张、小王、小赵”这些因匹配不上而右列为 NULL 的行也删掉LEFT JOIN 的“左表全保留”就名存实亡了。所以在 LEFT JOIN 里想保留主表全量数据附加的筛选条件尽量写进 ON只有确定要过滤主表行时才写 WHERE。这个区别也是面试高频考点答错的人不在少数。4.2 NULL值在JOIN里不会等于NULL小赵的dept_id是 NULL。哪怕departments表里真的有一行id也是 NULL小赵还是匹配不上。因为在 SQL 里任何普通比较遇到 NULL 都返回“未知”NULL NULL的结果不是 true而是 NULL。ON 条件只有结果为真时才匹配所以 NULL 永远配不上 NULL。处理 NULL 只能用IS NULL或者使用 NULL 安全等值运算符。另一个容易混淆的点是外连接补出来的 NULL和业务数据里本来存在的 NULL在结果集里长得一模一样。比如 LEFT JOIN 里小赵的部门是 NULL是因为业务上他本来就没分配部门而如果某个员工匹配到了部门但部门名是 NULL那是数据本身的问题。报表里如果对 NULL 敏感建议查询阶段就用 CASE WHEN 或 COALESCE 显式转义别指望后续代码里再去猜。4.3 一对多JOIN导致行数膨胀怎么快速定位JOIN 之后行数比左表多是很多人遇到过的怪事。问题通常出在右表关联字段不唯一。假设departments表被导入了重复数据市场部有两行iddept_name10研发部20市场部A20市场部B40财务部再执行 LEFT JOIN小李和小钱因为dept_id20会分别和两行“市场部”匹配结果变成 7 行小张 1 行小李 2 行小王 1 行小赵 1 行小钱 2 行。LEFT JOIN 的“左表全保留”只保证左表每一行至少出现一次不保证只出现一次。排查方法很简单先数一下 JOIN 结果总数再distinct左表主键数。如果前者明显大于后者就去检查右表连接列是否有重复。可以用一条 SQL 秒查SELECT id, COUNT(*) FROM departments GROUP BY id HAVING COUNT(*) 1;这个步骤在数据质量不高的环境里尤其重要。我见过很多报表数字对不上的情况最后都查到这个原因上。4.4 连接字段的类型与字符集不一致的隐形坑字段类型不一致比如一边是 INT、另一边是 VARCHAR 里存的‘20’数据库经常做隐式转换。查询功能上可能还能跑但在 MySQL 里对连接列使用函数或隐式转换会导致索引失效变成全表扫描。表现就是“数据没问题SQL 也能出结果就是慢得离谱”。排查手段是 EXPLAINtable type key rows employees ALL NULL 5 departments ALL NULL 3看到 type 是 ALL就可以怀疑没走索引。先检查连接列是否有索引再检查两边字段类型是否完全一致最后看字符集。utf8mb4 和 latin1 连不上也是典型的“看得见摸不着”的问题统一之后通常立竿见影。别看这些细节不起眼生产环境里最耗费时间的就是这类问题。5. 自连接、非等值JOIN与连接性能要点5.1 自连接一张表拆成两张“假表”来用自连接是很实用的技巧典型场景是员工表里加一列manager_id表示直属上级。为了演示我们给employees表补上这个字段假设数据如下idnamedept_idmanager_id1小张10NULL2小李2013小王3014小赵NULL25小钱202要查“每个员工的上级是谁”就得让employees和employees自己 JOIN。别怕给表起两个别名同一张表瞬间变成“两张只有名字不同的假表”SELECT e.name AS 员工, m.name AS 上级 FROM employees e LEFT JOIN employees m ON e.manager_id m.id;输出员工上级小张NULL小李小张小王小张小赵小李小钱小李这里用 LEFT JOIN 是为了让没有上级的小张也别被丢掉。自连接唯一的要求是必须用别名否则两张假表的名字混在一起SQL 引擎根本分不清。5.2 非等值JOINON条件不是只有等号JOIN 的 ON 条件不一定非要等于大于、小于、BETWEEN 都可以。典型场景是分数区间映射。比如student_scores存学生成绩grade_ranges存等级区间student_idscore100185100262100394100447grademin_scoremax_score优90100良8089中7079及格6069不及格059SQL 写成这样SELECT s.student_id, s.score, g.grade FROM student_scores s JOIN grade_ranges g ON s.score BETWEEN g.min_score AND g.max_score;结果每条成绩精确落到一个等级。非等值 JOIN 有个前提区间设计必须“滴水不漏且互不重叠”。万一重叠了一个分数会命中多个区间直接复现前面说过的行数膨胀。写的时候先确认区间表的数据质量再看执行计划评估开销。5.3 让JOIN跑得快索引、驱动表与EXPLAINJOIN 性能的核心是索引。连接列必须有索引尤其是右表的连接列。比如employees.dept_id连接departments.iddepartments.id是主键天然有索引但employees.dept_id如果没索引每次匹配都要全表扫数据量大了就完蛋。建议补一个CREATE INDEX idx_dept_id ON employees(dept_id);然后是驱动表。EXPLAIN 输出里第一行的 table 就是驱动表优化器一般会选择“小表驱动大表”因为它决定了外层循环的次数。写 SQL 时不用刻意调整表顺序现代优化器大多会自己重排但了解这个概念能帮你看懂执行计划。连接算法也要心里有数。MySQL 8.0.18 之后对等值 JOIN 支持哈希连接小表被拉进内存构建哈希表大表一趟扫描就能完成匹配效率比传统嵌套循环好很多。非等值 JOIN 用不上哈希连接基本只能走嵌套循环所以能用等值就用等值。最后老生常谈但依然有用的两点只 SELECT 需要的列别图省事SELECT *尽量先缩小行数再 JOIN比如子查询先把大表过滤到很小再对外连接效果往往比裸 JOIN 一条大表好。5.4 USING和NATURAL JOIN能省事但别乱用USING 是 ON 的一种语法糖。当连接列在两边叫同一个名字且语义完全一致时可以简写。比如另一张部门表也有dept_id列就可以SELECT e.name, d.dept_name FROM employees e JOIN departments_bak d USING (dept_id);但 USING 的要求很严格两边列名必须一致否则直接语法报错。更要命的是如果两边刚好有个同名列但语义不同——比如都叫id一个是员工 ID一个是部门 ID——USING 会把它们当成连接条件得到一堆完全错误的数据。语法没错逻辑全错连报错都不给你。NATURAL JOIN 更极端它会自动把所有同名列当成连接条件。看起来“聪明”实际上表结构一旦调整连接条件会悄悄变化结果集就跟着变了。我在业务里从来不推荐 NATURAL JOIN它省下的那点打字时间远不够排查它埋的雷。最后说点个人体会。JOIN 看着条目多真正要记住的其实就三件事连接条件写对、想清楚保留哪一侧、别让 NULL 和重复值偷袭。我每次写 JOIN 之前都会先在草稿纸上把两张表的几行关键数据画出来箭头一连结果基本不会错写完再用 EXPLAIN 看一遍执行计划确认走了索引。这套习惯帮我挡掉过不少线上问题。如果看到这里你还有印象模糊的地方建议直接把第 2 章的数据复制到本地跑一遍把输出和文中表格对比一下比背任何图解都管用。
SQL JOIN 这个话题说难不难说简单也经常翻车。前几天有个同事写报表一个 LEFT JOIN 下去结果行数莫名其妙多了一倍排查了半天最后发现是右表关联字段出现了重复数据。JOIN 的问题十个里有八个不是栽在语法上而是栽在没真正理解它背后发生了什么。这篇文章我不打算再放一张谁都看不懂的英文韦恩图就算完事。我会用一组可以直接复制到本地跑通的示例数据配合逐行箭头匹配图把 INNER、LEFT、RIGHT、FULL、CROSS 这五种核心 JOIN 的行为一次讲透同时把 ON 条件与 WHERE 条件混用、NULL 匹配、一对多导致的行数膨胀、连接字段类型不一致这些高频坑也一并聊掉。不管你是刚学 SQL 的新手还是已经写过不少业务查询、但没系统梳理过 JOIN 内部逻辑的开发都适合读下去。1. JOIN要解决的本质问题两张表如何变成一张表1.1 为什么好端端的表要拆开存先别急着看语法先把“为什么要 JOIN”想明白。我们建表的时候几乎不会把所有信息塞进一张表。比如员工表里每个员工只存一个dept_id而不是直接存“研发部”“市场部”这样的文本。为什么要这么设计因为一个部门下面可能挂着几十上百个员工。如果员工表直接存部门名称哪天部门改名了你就得扫全表把这几百行的字符串全改一遍。拆成两张表之后部门名称只在departments表里出现一次想改名只需改一行。这个“拆开存、查询时再拼回去”的建模方式就是规范化。它避免了数据冗余也避免了更新异常。但代价是查询的时候需要一种机制把分布在两张表里的信息按某种关系重新拼接起来。这个机制就是 JOIN。理解这一点你就能明白为什么 JOIN 不是 SQL 里某个可有可无的语法糖而是关系型数据库最核心的操作之一。1.2 不写JOIN的两种笨办法如果不用 JOIN想查出“每个员工属于哪个部门”怎么办第一种是逐行子查询比如SELECT name, (SELECT dept_name FROM departments d WHERE d.id e.dept_id) AS dept_name FROM employees e;这种写法本质上每查一行员工都要执行一次子查询。SQL 读起来绕性能也好不到哪去数据量一大数据库要被这种“逐行关联”拖垮。第二种是在业务代码里先查员工表然后循环每个dept_id再查一次部门表。这就是经典的 N1 查询问题一次主查询再加 N 次附加查询。接口响应时间随数据量线性恶化前端看着转圈后端看着监控报警。JOIN 的价值就在这里一次 SQL、一个结果集把匹配和拼接逻辑全部交给数据库让数据库自己选最优的执行路径。程序员要做的只是把连接条件写清楚。1.3 JOIN执行的逻辑三部曲笛卡尔积、ON、保留规则JOIN 表面上是一句 SQL逻辑上其实分三步走。第一步先把两表做笛卡尔积。笛卡尔积是什么说白了就是 A 表里每一行和 B 表里每一行都配对一次。employees有 5 行departments有 3 行笛卡尔积就是 15 行每个员工都会和三个部门各组合一次。第二步用 ON 条件过滤把真正对得上的组合留下。第三步根据外连接类型决定哪些额外行要保留INNER 只保留第二步过滤成功的LEFT 还要把左边没匹配上的行也保留右列补 NULLRIGHT 反过来FULL 则两边都保留。我强调一下现代数据库执行 JOIN 时不会真的把完整笛卡尔积生成出来而是会用嵌套循环、哈希连接等算法直接跳过大量无效组合。但逻辑上“先配对、再筛选、再补行”这个模型是理解所有 JOIN 行为的关键。后面每一个案例你都可以往这三步里套。2. 用行匹配图看懂五种核心JOIN2.1 示例表结构与数据长什么样后面所有的例子都用这两张表。employees是员工表departments是部门表员工表的dept_id指向部门表的id。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), dept_id INT ); CREATE TABLE departments ( id INT PRIMARY KEY, dept_name VARCHAR(50) ); INSERT INTO employees VALUES (1, 小张, 10), (2, 小李, 20), (3, 小王, 30), (4, 小赵, NULL), (5, 小钱, 20); INSERT INTO departments VALUES (10, 研发部), (20, 市场部), (40, 财务部);数据里特意埋了两个极端情况小王的dept_id30部门表里根本没有 30 号部门小赵的dept_id是 NULL。这俩就是后面各种 JOIN 展示“补 NULL”的最佳素材。建议你把这些语句直接复制到本地跑一遍后面的结果都能亲手验证。2.2 INNER JOIN只留下两边都有的INNER JOIN 的匹配图长这样员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配丢弃 (4, 小赵, NULL) 无匹配丢弃 (5, 小钱, 20) ──────────▶ (20, 市场部)所谓内连接就是两个集合的交集只有员工能匹配上部门、且部门也能被员工匹配到的行才会出现在结果里。小王虽然属于“30 号部门”但departments里没有这一行他在这条查询里就查不到小赵因为dept_id是 NULL也匹配不上。INNER JOIN 不补 NULL 行两边只要有一边缺失整行直接过滤掉。SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.id;2.3 LEFT JOIN 与 RIGHT JOIN谁是大爷谁全保留LEFT JOIN 的匹配图员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配 → 右列补 NULL (4, 小赵, NULL) 无匹配 → 右列补 NULL (5, 小钱, 20) ──────────▶ (20, 市场部)LEFT JOIN 的规则一句话左表行全保留右表能匹配就填部门名不能匹配就补 NULL。在这个例子中结果一定是 5 行因为员工表就 5 行。哪怕小王、小赵匹配不上他们也会出现在结果里只是部门名称是 NULL。这就是为什么报表里常用 LEFT JOIN——你知道主表是哪个其他信息只是“带出来”的附属字段。RIGHT JOIN 只是把方向反过来员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (5, 小钱, 20) ──────────▶ (20, 市场部) 无匹配 → 左列补 NULL (40, 财务部)右表 3 行全保留财务部没有员工所以员工相关列补 NULL。实际开发中 RIGHT JOIN 用得相对少因为只要把表的顺序反过来RIGHT 就变成了 LEFT。但理解它有助于看懂别人写的存量代码面试也爱考。2.4 FULL JOIN两边都不丢FULL OUTER JOIN 可以理解为 LEFT JOIN 和 RIGHT JOIN 的并集员工表行 部门表行 (1, 小张, 10) ──────────▶ (10, 研发部) (2, 小李, 20) ──────────▶ (20, 市场部) (3, 小王, 30) 无匹配 → 右列补 NULL (4, 小赵, NULL) 无匹配 → 右列补 NULL (5, 小钱, 20) ──────────▶ (20, 市场部) 无匹配 → 左列补 NULL (40, 财务部)两边的“孤儿数据”都会出现只是各自缺失的一侧会被 NULL 填充。MySQL 很长一段时间都没有 FULL JOIN 的语法后续我会给一个替代方案。在支持 FULL JOIN 的数据库里OUTER 这个词通常可以省略直接写 FULL JOIN 就行。2.5 CROSS JOIN误用会比想象中更可怕CROSS JOIN 就是没有 ON 条件的笛卡尔积。对这个特性CROSS JOIN 很少被业务查询使用但一旦误用结果行数会爆炸。比如对这两张表执行 CROSS JOIN直接返回 5×315 行这还只是小数据。如果左表 10 万行、右表 10 万行CROSS JOIN 就是 100 亿行数据库基本当场出问题。写法有两种SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d; -- 或者更老的写法 SELECT e.name, d.dept_name FROM employees e, departments d;第二种老式写法在存量代码里很常见如果你看到一条 SQL 没有 WHERE 也没有 ON就要警惕它是不是在偷偷做 CROSS JOIN。2.6 五种核心JOIN速查对照表JOIN 类型返回行策略业务场景空值来源INNER JOIN只返回匹配成功的行查“确定存在关联”的数据无LEFT JOIN左表全保留右表补 NULL主表在左附属信息可有可无右表列RIGHT JOIN右表全保留左表补 NULL主表在右等价于反过来写 LEFT左表列FULL JOIN两表全保留缺失补 NULL两表独立需要完整对账两侧都有CROSS JOIN两表全组合排列组合、生成序列无这张表可以当速查卡用。但光背结论不够下一章我们把每种 JOIN 的输出结果实际跑出来眼见为实。3. 同一份数据跑一遍各JOIN的真实输出长这样3.1 INNER JOIN 的 SQL 与输出对照理论讲完了直接看结果。INNER JOIN 的 SQL 和执行结果如下SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小钱市场部输出只有 3 行。小王和小赵没有出现在结果里因为30号部门和 NULL 都匹配不上任何部门。这个输出再次验证了 INNER JOIN 的“交集”本质——两边都缺一不可。3.2 LEFT JOIN 和 RIGHT JOIN 的方向差异验证LEFT JOINSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小王NULL小赵NULL小钱市场部RIGHT JOINSELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;namedept_name小张研发部小李市场部小钱市场部NULL财务部对比两张表能发现LEFT JOIN 保留了所有员工部门列有 2 个 NULLRIGHT JOIN 保留了所有部门员工列多了 1 个 NULL。NULL 出现的位置恰好就是主表一侧缺失的关联。财务部没有员工所以在 RIGHT JOIN 里员工列就是 NULL。3.3 MySQL 没有 FULL JOIN怎么用 UNION 模拟MySQL 不支持 FULL OUTER JOIN但可以用 LEFT JOIN 和 RIGHT JOIN 的结果做 UNION 去重来模拟SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id UNION SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id d.id;结果正好是 6 行5 个员工全在再加上财务部这一行。UNION 会自动去重所以 LEFT 和 RIGHT 查询中重复的那三行小张、小李、小钱只会保留一份。如果你故意写成 UNION ALL会得到 9 行多出的 3 行就是重复数据。这个细节在做数据统计时很容易踩坑一定要记得。3.4 CROSS JOIN 的输出规模估算CROSS JOIN 前面说过直接硬拼SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d;结果共 15 行前几行是namedept_name小张研发部小张市场部小张财务部小李研发部小李市场部小李财务部......你还可以把 CROSS JOIN 加 WHERE 条件手动模拟 INNER JOINSELECT e.name, d.dept_name FROM employees e, departments d WHERE e.dept_id d.id;结果跟前面的 INNER JOIN 完全一样。这是老式 SQL 的写法看到别慌能看懂就行。4. JOIN最容易翻车的那些坑ON、NULL与重复行4.1 同样一个条件放ON里和WHERE里结果完全不同这是 JOIN 里最经典、出错率最高的一个坑。同样写“部门 ID 等于 20”放 ON 和放 WHERE 差别巨大。条件放 ONSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id AND d.id 20;小张dept_id10不匹配 → 部门列为 NULL小李dept_id20 且 id20匹配 → 市场部小王dept_id30不匹配 → NULL小赵dept_idNULL不匹配 → NULL小钱dept_id20 且 id20匹配 → 市场部结果 5 行namedept_name小张NULL小李市场部小王NULL小赵NULL小钱市场部条件放 WHERESELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id d.id WHERE d.id 20;结果只有 2 行namedept_name小李市场部小钱市场部原因很简单SQL 的逻辑执行顺序里ON 在 JOIN 时生效WHERE 在 JOIN 完成之后才对结果集做过滤。WHERE d.id 20会把“小张、小王、小赵”这些因匹配不上而右列为 NULL 的行也删掉LEFT JOIN 的“左表全保留”就名存实亡了。所以在 LEFT JOIN 里想保留主表全量数据附加的筛选条件尽量写进 ON只有确定要过滤主表行时才写 WHERE。这个区别也是面试高频考点答错的人不在少数。4.2 NULL值在JOIN里不会等于NULL小赵的dept_id是 NULL。哪怕departments表里真的有一行id也是 NULL小赵还是匹配不上。因为在 SQL 里任何普通比较遇到 NULL 都返回“未知”NULL NULL的结果不是 true而是 NULL。ON 条件只有结果为真时才匹配所以 NULL 永远配不上 NULL。处理 NULL 只能用IS NULL或者使用 NULL 安全等值运算符。另一个容易混淆的点是外连接补出来的 NULL和业务数据里本来存在的 NULL在结果集里长得一模一样。比如 LEFT JOIN 里小赵的部门是 NULL是因为业务上他本来就没分配部门而如果某个员工匹配到了部门但部门名是 NULL那是数据本身的问题。报表里如果对 NULL 敏感建议查询阶段就用 CASE WHEN 或 COALESCE 显式转义别指望后续代码里再去猜。4.3 一对多JOIN导致行数膨胀怎么快速定位JOIN 之后行数比左表多是很多人遇到过的怪事。问题通常出在右表关联字段不唯一。假设departments表被导入了重复数据市场部有两行iddept_name10研发部20市场部A20市场部B40财务部再执行 LEFT JOIN小李和小钱因为dept_id20会分别和两行“市场部”匹配结果变成 7 行小张 1 行小李 2 行小王 1 行小赵 1 行小钱 2 行。LEFT JOIN 的“左表全保留”只保证左表每一行至少出现一次不保证只出现一次。排查方法很简单先数一下 JOIN 结果总数再distinct左表主键数。如果前者明显大于后者就去检查右表连接列是否有重复。可以用一条 SQL 秒查SELECT id, COUNT(*) FROM departments GROUP BY id HAVING COUNT(*) 1;这个步骤在数据质量不高的环境里尤其重要。我见过很多报表数字对不上的情况最后都查到这个原因上。4.4 连接字段的类型与字符集不一致的隐形坑字段类型不一致比如一边是 INT、另一边是 VARCHAR 里存的‘20’数据库经常做隐式转换。查询功能上可能还能跑但在 MySQL 里对连接列使用函数或隐式转换会导致索引失效变成全表扫描。表现就是“数据没问题SQL 也能出结果就是慢得离谱”。排查手段是 EXPLAINtable type key rows employees ALL NULL 5 departments ALL NULL 3看到 type 是 ALL就可以怀疑没走索引。先检查连接列是否有索引再检查两边字段类型是否完全一致最后看字符集。utf8mb4 和 latin1 连不上也是典型的“看得见摸不着”的问题统一之后通常立竿见影。别看这些细节不起眼生产环境里最耗费时间的就是这类问题。5. 自连接、非等值JOIN与连接性能要点5.1 自连接一张表拆成两张“假表”来用自连接是很实用的技巧典型场景是员工表里加一列manager_id表示直属上级。为了演示我们给employees表补上这个字段假设数据如下idnamedept_idmanager_id1小张10NULL2小李2013小王3014小赵NULL25小钱202要查“每个员工的上级是谁”就得让employees和employees自己 JOIN。别怕给表起两个别名同一张表瞬间变成“两张只有名字不同的假表”SELECT e.name AS 员工, m.name AS 上级 FROM employees e LEFT JOIN employees m ON e.manager_id m.id;输出员工上级小张NULL小李小张小王小张小赵小李小钱小李这里用 LEFT JOIN 是为了让没有上级的小张也别被丢掉。自连接唯一的要求是必须用别名否则两张假表的名字混在一起SQL 引擎根本分不清。5.2 非等值JOINON条件不是只有等号JOIN 的 ON 条件不一定非要等于大于、小于、BETWEEN 都可以。典型场景是分数区间映射。比如student_scores存学生成绩grade_ranges存等级区间student_idscore100185100262100394100447grademin_scoremax_score优90100良8089中7079及格6069不及格059SQL 写成这样SELECT s.student_id, s.score, g.grade FROM student_scores s JOIN grade_ranges g ON s.score BETWEEN g.min_score AND g.max_score;结果每条成绩精确落到一个等级。非等值 JOIN 有个前提区间设计必须“滴水不漏且互不重叠”。万一重叠了一个分数会命中多个区间直接复现前面说过的行数膨胀。写的时候先确认区间表的数据质量再看执行计划评估开销。5.3 让JOIN跑得快索引、驱动表与EXPLAINJOIN 性能的核心是索引。连接列必须有索引尤其是右表的连接列。比如employees.dept_id连接departments.iddepartments.id是主键天然有索引但employees.dept_id如果没索引每次匹配都要全表扫数据量大了就完蛋。建议补一个CREATE INDEX idx_dept_id ON employees(dept_id);然后是驱动表。EXPLAIN 输出里第一行的 table 就是驱动表优化器一般会选择“小表驱动大表”因为它决定了外层循环的次数。写 SQL 时不用刻意调整表顺序现代优化器大多会自己重排但了解这个概念能帮你看懂执行计划。连接算法也要心里有数。MySQL 8.0.18 之后对等值 JOIN 支持哈希连接小表被拉进内存构建哈希表大表一趟扫描就能完成匹配效率比传统嵌套循环好很多。非等值 JOIN 用不上哈希连接基本只能走嵌套循环所以能用等值就用等值。最后老生常谈但依然有用的两点只 SELECT 需要的列别图省事SELECT *尽量先缩小行数再 JOIN比如子查询先把大表过滤到很小再对外连接效果往往比裸 JOIN 一条大表好。5.4 USING和NATURAL JOIN能省事但别乱用USING 是 ON 的一种语法糖。当连接列在两边叫同一个名字且语义完全一致时可以简写。比如另一张部门表也有dept_id列就可以SELECT e.name, d.dept_name FROM employees e JOIN departments_bak d USING (dept_id);但 USING 的要求很严格两边列名必须一致否则直接语法报错。更要命的是如果两边刚好有个同名列但语义不同——比如都叫id一个是员工 ID一个是部门 ID——USING 会把它们当成连接条件得到一堆完全错误的数据。语法没错逻辑全错连报错都不给你。NATURAL JOIN 更极端它会自动把所有同名列当成连接条件。看起来“聪明”实际上表结构一旦调整连接条件会悄悄变化结果集就跟着变了。我在业务里从来不推荐 NATURAL JOIN它省下的那点打字时间远不够排查它埋的雷。最后说点个人体会。JOIN 看着条目多真正要记住的其实就三件事连接条件写对、想清楚保留哪一侧、别让 NULL 和重复值偷袭。我每次写 JOIN 之前都会先在草稿纸上把两张表的几行关键数据画出来箭头一连结果基本不会错写完再用 EXPLAIN 看一遍执行计划确认走了索引。这套习惯帮我挡掉过不少线上问题。如果看到这里你还有印象模糊的地方建议直接把第 2 章的数据复制到本地跑一遍把输出和文中表格对比一下比背任何图解都管用。
![[实测可用 v2.7.5] 桌面端 Open Claw 搭建流程全程图文教程：把 settings 改到 TaoToken](http://pic.xiahunao.cn/yaotu/[实测可用 v2.7.5] 桌面端 Open Claw 搭建流程全程图文教程：把 settings 改到 TaoToken)
