做 MySQL 开发或者维护数据库的人迟早都会在“表连接”上栽个跟头。不管是面试题里被反复追问的内连接与外连接区别还是临时跑报表时要把跨表数据合并起来甚至是你正在维护几千万行的大表想从里面查点关联信息最终都会落到内连接 INNER JOIN、外连接 LEFT JOIN / RIGHT JOIN 这两类基础操作上。这篇文章不提花哨的框架直接以 MySQL 环境为准把表的内连接、外连接的语义、SQL 写法、执行过程差异和常见问题讲透同时附上一套可以照抄的建表与查询步骤。适合刚接触 SQL 的后端开发、数据分析新人也适合想系统梳理 JOIN 知识的同学参考。1. 连接查询到底在解决什么问题1.1 单表查询满足不了业务连接就来了写 SQL 的时候百分之八十的查询用一张表就能跑完SELECT * FROM users WHERE status 1再有百分之十几就是一张表加 GROUP BY 做统计。真正让人头疼的是剩余那些需求订单列表要带出用户名、商品要带出分类名、日报表要让员工和部门一一对应。这些信息往往分散在两张甚至多张表里。数据库为什么要把数据拆开存根源是规范化设计。拿订单举例如果每次下单都把用户姓名、电话、地址全量复制到订单表里用户改一次地址历史订单里的地址就全部变成旧地址数据冗余不说还容易对不上账。合理的做法是订单表只存一个 user_id需要姓名电话时去用户表里面查。也就是说拆表是为了解决数据一致性和更新成本的问题而跨表查询是为了解决“拆开之后怎么再合起来用”的问题。连接JOIN恰恰就是这个“合起来”的操作。它通过一条 SQL把两张表按照某一列通常是主键和外键建立起对应关系然后把匹配到的行组合成一条结果。我第一次学 JOIN 的时候对“行组合”这个概念毫无直觉直到我把两张表的数据手工抄了一遍才明白每条匹配其实就是一行新记录。想跑任何像样的业务报表跨表 JOIN 几乎绕不开。再往后你看 Flink 同步、ClickHouse 宽表拼接这些场景底层思路也都是一样的就是把散在各处的数据按某个键重新组装起来。1.2 笛卡尔积所有连接查询的底层本质要理解连接查询就必须先理解笛卡尔积。两张表做连接运算时数据库会先把表 A 的每一行和表 B 的每一行做组合。如果 A 有 3 行、B 有 4 行组合结果就是 12 行。这是所有 JOIN 的起点先做笛卡尔积再按条件过滤。举个例子用户表有三个人张三、李四、王五订单表有四条记录分别属于张三、张三、李四以及一条 user_id 在用户表里根本不存在的“孤儿订单”。如果直接写 SELECT * FROM users, orders就会得到 3×412 行里面绝大多数是毫无意义的错配数据。内连接要做的事情就是在这 12 行里挑出 users.id orders.user_id 的那些行。MySQL 执行 JOIN 的时候实际做法未必是先把笛卡尔积全部物化出来它更常见的执行方式是嵌套循环连接Nested Loop Join拿驱动表的每一行去被驱动表里按连接条件找匹配行。但“笛卡尔积 过滤”这个模型对理解结果、排查数据膨胀非常有帮助。很多线上事故说白了就是连接条件漏写导致一张小表 JOIN 一张大表时跑出来几百万行甚至更多数据库瞬间被拖垮。心算结果行数时先老老实实乘一遍再想过滤条件基本不会偏差太多。2. 内连接最常用的 JOIN 写法与匹配逻辑2.1 INNER JOIN 的标准语法和两种等价写法内连接返回的是两张表中“互相匹配得上的行”不匹配的行直接丢弃。它有两种等价写法第一种是显式写法SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id orders.user_id;第二种是隐式写法SELECT users.name, orders.amount FROM users, orders WHERE users.id orders.user_id;很多人问我这两种写法到底有什么差别。结论是逻辑结果完全一样MySQL 优化器也基本能生成同样的执行计划。但从可读性和可维护性来说我强烈推荐显式 INNER JOIN。原因有两条。第一连接条件和过滤条件在 ON 和 WHERE 里是分开的看 SQL 的时候能一眼分清“表与表怎么关联”和“结果集怎么过滤”第二后续改成 LEFT JOIN 时只需要把 INNER JOIN 换成 LEFT JOIN结构不用动。隐式写法把连接条件混在 WHERE 里本来只想改连接类型结果连过滤条件都要重新理一遍特别容易漏改。还有一种比较常见的写法是用 USING 简化同名列连接SELECT users.name, orders.amount FROM users INNER JOIN orders USING (id);不过 USING 要求两边的列名完全一致实际业务里用户表主键叫 id、订单表外键叫 user_id 的情况更多所以 USING 的使用频率并不高。下面这张表可以快速对比三种写法的适用场景写法适用场景注意事项INNER JOIN ... ON所有常规场景推荐连接条件与过滤条件分离易读性好FROM t1, t2 WHERE ...老项目或临时查询连接条件混在过滤里改造成 LEFT JOIN 时容易漏改INNER JOIN ... USING(列)两表连接列同名列名不同时无法使用2.2 内连接的匹配过程和结果特征从执行角度来看内连接的匹配过程大致是这样MySQL 选一张表做驱动表另一张做被驱动表驱动表每取一行就去被驱动表里按 ON 条件找匹配找到就拼接输出找不到就跳过。用什么顺序驱动通常由优化器根据表大小、索引情况决定日常开发不需要过度干预。结果特征上内连接有几个值得注意的地方。第一内连接的结果行数取决于“匹配次数”。如果用户表里的张三在订单表里有两条记录结果里就会出现两行张三。这符合业务直觉但也非常容易在统计时出错。经常有人写 JOIN 后对订单金额做 SUM发现数字比实际订单汇总大了一圈就是因为用户表的一行和订单表的多行发生了“一对多”膨胀。第二内连接只返回两边都匹配的行。继续用前面的例子王五如果从没下过单内连接结果里不会出现他订单表里 user_id 不存在的孤儿订单同样不会出现。这是内连接最主要的特征也是和外连接对比时最关键的差异。第三如果两张表里都有重复行内连接会做“重复行两两匹配”。例如用户表里有两个完全相同的张三主键设计有问题订单表里又有两条对应张三的订单最后会得到四行结果。这种膨胀隐蔽而且破坏性很强遇到结果数量异常放大时要首先想到检查主键约束或者视情况加 DISTINCT 兜底。2.3 内连接的两个进阶场景自连接与不等值连接内连接里还有两个容易被忽略的进阶场景自连接和不等值连接。自连接就是一张表和自己做 INNER JOIN。最经典的例子是员工表里存了 manager_id指向同一张表的 id想查“每个员工的姓名以及他上级的姓名”时就能让员工表和自己连接两次SELECT e.name AS employee_name, m.name AS manager_name FROM employee e INNER JOIN employee m ON e.manager_id m.id;这种写法在树形结构、层级关系、好友关系里非常常见。很多人第一次看到一张表 JOIN 自己会懵其实只要把它想象成两张内容相同但别名不同的表思路就通了。因为 MySQL 里 FROM 后出现两次同一张物理表是不允许的所以必须用别名区分。不等值连接则是 ON 条件里不用等号而是用 、、BETWEEN 这类范围条件。比如查“分数表里每一条记录之前有多少条更高分的记录”就可以用自连接加 COUNT 实现。不等值连接在业务报表里不如等值连接常见但一旦碰上类似“区间匹配”“排名区间”的需求它就是最直接的解法。唯一要注意的是不等式连接通常无法走索引大表上代价很高要时刻关注性能。3. 外连接LEFT JOIN 与 RIGHT JOIN 的语义细节3.1 LEFT JOIN 的“保留左表”特性外连接和内连接最大的区别在于内连接是“只保留匹配上的行”外连接则是“指定一侧的表必须全部保留另一张表能匹配就带值匹配不上就补 NULL”。LEFT JOIN 的语义可以拆成三句话左表FROM 后面的表的每一行都必须出现在结果里。对左表的每一行去右表找匹配行找到几行就拼接几行。找不到匹配时右表的列全部填 NULL。继续用前面的数据左表 users 有三个人即使王五从没下过单结果里也会出现一行“王五, NULL”订单表里那条没有对应用户的孤儿订单 104因为它在右表所以不会强行走入 LEFT JOIN 的结果。这个“以左表为主、右表补充信息”的模型是 LEFT JOIN 最典型的业务用法把主表的记录全部列出来能关联到附属表明细就带上关联不到就显示为空。实际写报表时LEFT JOIN 最常用的场景是“查主表所有数据 附带子表数据”。例如运营要导出“所有用户中哪些下过单、哪些没下单”一条 LEFT JOIN 就能解决SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id o.user_id;看结果里 order_id 是否为 NULL就能判断用户是否下单再配合 WHERE o.id IS NULL 就能精确筛出“从未下单的用户”。这类 SQL 本质上利用的是左连接保留语义加上右表主键为空的判断理解这一点比死记硬背“LEFT JOIN 后面加 IS NULL 可以找不在表里”要牢靠得多。3.2 为什么很少用 RIGHT JOIN语法上外连接有三个方向LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN。MySQL 支持前两个不支持 FULL OUTER JOIN。RIGHT JOIN 的语义和 LEFT JOIN 完全对称只是“保留右表”。比如SELECT users.name, orders.amount FROM users RIGHT JOIN orders ON users.id orders.user_id;这条 SQL 会保留右侧 orders 表的全部记录即便是孤儿订单也会输出对应不上用户时users.name 显示为 NULL。按理说RIGHT JOIN 是合法语法为什么实际项目里几乎见不到主要原因是可读性。绝大多数人的阅读习惯是“从左往右看”LEFT JOIN 的保留方向清晰直观而 RIGHT JOIN 总要把脑子绕一圈。所以团队里约定俗成的做法是只准用 LEFT JOIN如果需要右连接方向就把两张表的书写顺序换一下改写成 LEFT JOIN 的等价形式SELECT users.name, orders.amount FROM orders LEFT JOIN users ON users.id orders.user_id;这条 SQL 和上面 RIGHT JOIN 的结果集完全一样。统一使用 LEFT JOIN 之后整个团队维护 SQL 的心智负担会小很多。我还见过一些老项目因为混用 LEFT JOIN 和 RIGHT JOIN排查问题时被迫先去猜每张表的保留方向效率非常低。哪怕是临时跑数也建议遵循这个习惯。3.3 FULL OUTER JOIN 在 MySQL 里的替代方案MySQL 不支持 FULL OUTER JOIN但业务上确实会遇到“要把两张表的全部数据合并展示”的需求。例如用户表和订单表要导出一份全量清单用户没订单要显示 NULL订单没有对应用户也要显示 NULL。标准做法是用 UNION ALL 把两个方向的 LEFT JOIN 拼起来SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id orders.user_id UNION ALL SELECT users.name, orders.amount FROM orders LEFT JOIN users ON users.id orders.user_id;第一段选出所有用户及其订单第二段选出所有订单及其用户两边取并集之后正好覆盖了全外连接的效果。需要注意几个细节两段 SQL 的列数必须一致列类型要兼容如果担心有重复可以用 UNION 去重但代价是额外的排序和去重开销数据量很大时明显变慢。建议先确认业务到底需不需要去重再做取舍。4. 实操过程从建表到多表连接查询4.1 准备演示用的数据表理论讲再多不如实际跑一遍。下面这套数据模拟了一个非常简单的用户-订单场景两张表之间是典型的“用户一对多订单”关系特意加入了一条孤儿订单和一个没有订单的用户方便对比内连接和外连接。先建用户表CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); INSERT INTO users (id, name) VALUES (1, 张三), (2, 李四), (3, 王五);再建订单表订单表通过 user_id 指向用户表amount 是订单金额CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10, 2), item VARCHAR(50) ); INSERT INTO orders (id, user_id, amount, item) VALUES (101, 1, 99.00, 无线鼠标), (102, 1, 299.00, 机械键盘), (103, 2, 59.00, 鼠标垫), (104, 99, 1999.00, 显示器);能看到订单表里有一条 user_id 99 的孤儿订单用户表里没有 id 99 的人用户王五则没有任何订单。这两个特殊数据恰好能把内连接和外连接的差异完整展示出来。我建议你直接复制这些建表和插入语句到本地 MySQL 跑一遍后面的 SQL 也全部可以照抄执行。4.2 跑一遍内连接、LEFT JOIN 和 RIGHT JOIN先看内连接结果SELECT u.id, u.name, o.id AS order_id, o.amount, o.item FROM users u INNER JOIN orders o ON u.id o.user_id ORDER BY u.id;结果里只有张三的两条订单、李四的一条订单共 3 行。王五和孤儿订单 104 都被过滤掉了。这就是内连接的典型输出只保留两边匹配上的数据。再看 LEFT JOINSELECT u.id, u.name, o.id AS order_id, o.amount, o.item FROM users u LEFT JOIN orders o ON u.id o.user_id ORDER BY u.id;结果是 4 行张三两行、李四一行、王五一行王五那行的 order_id、amount、item 全部是 NULL。去掉 ORDER BY行顺序可能不稳定但行数是确定的。这正好对应了 LEFT JOIN“左表全部保留、右表对不上补 NULL”的语义。再看 RIGHT JOINSELECT u.id, u.name, o.id AS order_id, o.amount, o.item FROM users u RIGHT JOIN orders o ON u.id o.user_id ORDER BY o.id;结果还是 4 行张三两行、李四一行、孤儿订单 104 一行此时 u.id、u.name 为 NULL王五消失因为 RIGHT JOIN 保留的是右表 ordersusers 里多出来的王五不在结果里。三个查询跑完内连接、外连接的区别就非常直观了。内连接是“两边有交集才输出”LEFT JOIN 是“左表全集 右表对上就带、对不上补空”RIGHT JOIN 则反过来。看完结果之后建议再花一分钟执行一下 EXPLAINEXPLAIN SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id;查看输出里的 type 和 rows能直观感受到 MySQL 是怎么选择驱动表、怎么读取数据的这对后续优化 SQL 非常有帮助。4.3 多表连接JOIN 的顺序与 ON 条件组织真实业务很少只有两张表像“订单明细要带出下单用户和商品分类”这种需求往往要一次性连接三张表。多表连接的本质是把两张表的连接结果看作一张新表再和第三张表连接。还是用前面的表做演示。假设我想查询“每笔订单的用户名以及订单对应的商品名称”商品信息其实写在 orders.item 里更合理的做法是增加一张商品表 productsCREATE TABLE products ( item VARCHAR(50) PRIMARY KEY, category VARCHAR(50) ); INSERT INTO products (item, category) VALUES (无线鼠标, 外设), (机械键盘, 外设), (鼠标垫, 桌搭), (显示器, 显示设备);然后做三表连接查询SELECT u.name, o.item, p.category FROM users u INNER JOIN orders o ON u.id o.user_id INNER JOIN products p ON o.item p.item;执行时 MySQL 会先把 users 和 orders 做连接得到一个中间结果集再用这个中间结果集和 products 按 item 匹配。孤儿订单依旧不会出现因为第一步就被 INNER JOIN 过滤掉了。如果想让所有订单都展示出来同时带出用户和商品分类可以改成SELECT u.name, o.item, p.category FROM orders o LEFT JOIN users u ON u.id o.user_id LEFT JOIN products p ON o.item p.item;这个写法值得细品。第一驱动表换成了 orders以订单为主表所有订单都会保留第二最终结果里孤儿订单的用户名为 NULL未登记商品的类别为 NULL。多表连接时要注意“保留表的选择完全影响结果行数”这也是新人最容易翻车的地方一个需求明明要“所有订单”结果写了 FROM users LEFT JOIN orders直接把没下过单的用户也带出来了行数就会无端多出一堆。5. 常见问题与排查技巧实录5.1 笛卡尔积爆炸连接条件缺失是最常见的事故排查线上 SQL 慢查询时我见过最多的一个错误就是两张表 JOIN忘了写 ON 条件或者其中一张表的关联条件写错导致结果瞬间膨胀。举个真实例子有张 10 万行的订单表和一张 5 万行的用户表本来 JOIN 毫秒级就能完成结果因为多表连接时少写了 products 表的关联条件10万×5万一下子就变成 50 亿行。这种 SQL 一旦触发数据库 IO 和 CPU 直接飙满其他业务全部遭殃。排查思路很简单看到 JOIN 后先数一数 ON 条件是不是完整再估算结果行数如果比两张表的最大行数还大很多优先怀疑笛卡尔积。另外SELECT 阶段尽量只保留要用的列少写 SELECT *减少中间结果集的内存占用。对几千万行的大表来说这条建议尤其重要连接时多带一列内存和临时文件都可能成倍上涨。5.2 ON 和 WHERE 的过滤时机差异很多人写外连接时踩过一个坑把过滤条件放在 WHERE 里发现 LEFT JOIN 竟然丢数据了。看下面这条 SQLSELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 100;直觉上我只是在 LEFT JOIN 之后“再过滤金额大于 100 的订单”应该不会丢用户吧实际上这条 SQL 会把没下单的或者订单金额小于等于 100 的用户全部过滤掉结果等价于一条 INNER JOIN。原因是 WHERE 子句的过滤发生在连接完成之后右表补出来的 NULL 值参与过滤时o.amount 100 会变成 NULL 100结果为未知被判为不满足条件整行被丢弃。如果想保留左表全部用户同时只看金额大于 100 的订单应该把过滤条件放到 ON 里面SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.amount 100;此时连接阶段只保留右表中 amount 100 的行匹配不上的用户右表列照样补 NULL左表一行都不丢。这个差异几乎是外连接优化的核心要点面试官也特别喜欢拿这个问题考察候选人对连接执行过程的理解。自己写 SQL 的时候养成习惯外连接的“附属表过滤条件”优先放 ON主表的过滤条件放 WHERE。5.3 重复行膨胀与去重方案连接查询另一个高频问题就是结果集重复。常见场景订单明细表和多个维度表连接后某张维度表存在重复记录或者业务模型本身就是一对多都会导致输出行数被放大。处理方式分几种。如果业务上就是要唯一记录可以使用 DISTINCT 去重SELECT DISTINCT u.name, o.item FROM users u INNER JOIN orders o ON u.id o.user_id;但 DISTINCT 本质是对整个结果集做去重排序几千万行大表上代价不小。更推荐的做法是先搞清楚重复从哪来如果是维度表重复去修数据或者按主键限定如果是业务上的一对多统计时改用关联子查询或者先聚合再去连接。比如要“统计每个用户的订单数量”最稳妥的写法是先在子查询里按用户聚合再关联用户表SELECT u.name, IFNULL(t.order_cnt, 0) AS order_cnt FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) t ON u.id t.user_id;这样先聚合后连接既避免了每笔订单和用户逐行匹配造成的膨胀也保留了没下单的用户order_cnt 用 IFNULL 转成 0报表里看起来也更舒服。对于几千万行的大表这个思路特别重要能省下大量中间结果集的空间。5.4 几千万行大表连接的优化思路热搜词里出现了“几千万行大表”这里专门展开说一下。大表 JOIN 最怕的不是 SQL 写法复杂而是“被驱动表上没有索引”和“中间结果集过大”。第一连接字段必须建索引。JOIN 的本质是拿驱动表的每一行去被驱动表里查匹配数据如果被驱动表的连接列没有索引MySQL 只能全表扫描匹配一次 JOIN 就是几千万次扫描基本没法用。所以检查执行计划时先看被驱动表的 key 列索引缺失就补一个ALTER TABLE orders ADD INDEX idx_user_id (user_id);第二尽量小表驱动大表。MySQL 优化器通常会自动选择小表作为驱动表但多表连接、复杂子查询时不一定智能。手工干预时可以使用 STRAIGHT_JOIN 强制表连接顺序。不过生产环境不建议轻易使用除非你通过 EXPLAIN 确认优化器的选择确实不合理。第三适当拆分查询。几千万行大表 JOIN 几千万行大表哪怕有索引返回结果集本身就很庞大。如果业务只需要聚合结果可以先在各表上分别聚合最后再关联汇总如果只需要分页先用子查询取当前页的主键再回表 JOIN 补全信息能明显减少大表间的连接计算量。MySQL 里还有一条实用原则能用索引覆盖就尽量覆盖能不回表就不回表这和执行计划里的 Extra 字段直接相关。6. 关于连接查询我的一些个人体会内容到这里内连接、外连接的核心东西基本讲完了。最后说点题外话。我在实际工作中发现JOIN 写不好往往不是语法问题而是对数据模型不熟。拿到一个查询需求先问自己几个问题哪张表是主表主表和附属表是什么关系是一对多还是多对多保留方向是哪个心里有这三个答案再写 SQL基本不会错得离谱。另外一个经验是写完 SQL 别急着上线先看 EXPLAIN 输出里的 rows 和 Extra 字段。rows 接近实际返回行数说明过滤条件生效了看到 Using temporary 或者 Using filesort就要警惕大表上的排序和去重。如果返回的行数和 EXPLAIN 预估的行数差出几个数量级那大概率是连接条件有问题。连接查询可以说是 MySQL 里最基础也最值得反复琢磨的内容。把 INNER JOIN 和 LEFT JOIN 吃透再去看子查询、窗口函数、Flink 同步这类进阶话题会发现很多逻辑底层都是相通的。我建议你把这套建表和 SQL 自己敲一遍遇到问题再去对照上面的排查清单比单纯看文章有用得多。