尧图网页设计 
很多人学 SQL 的时候前面SELECT、WHERE、GROUP BY都学得挺好的一到JOIN就开始懵。面试被问“LEFT JOIN 和 INNER JOIN 有什么区别”能答上来但真到写业务查询的时候要么查出来的数据比预期多要么莫名其妙丢行要么一条 SQL 跑半天。问题出在哪大概率是没把“连接”这件事从底层逻辑上想清楚。这篇文章要把 SQL 中的连接讲透不是只背语法而是从“为什么需要连接”“连接时数据是怎么匹配的”讲起把INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN一次说明白再加上多表连接、自连接和聚合场景。读完你能判断什么时候该用哪种连接也知道查出来数据不对时往哪个方向排查。1. 这篇文章真正要解决的问题先看一个特别常见的场景。你有一张users表存用户基本信息一张orders表存订单记录两张表通过user_id关联。需求很简单查出每个用户名下的订单数量。很多新手第一反应是“能不能把两张表的数据拼在一起”然后就开始写SELECT * FROM users, orders WHERE users.id orders.user_id;这句 SQL 在 MySQL 里能跑结果看起来也对。但你要是追问一句“这个逗号是什么意思”对方可能会愣住。更麻烦的是当需求变成“把所有用户都列出来包括那些没下过单的用户”上面的写法就查不出来了因为WHERE已经把没匹配上的行过滤掉了。这其实就是 SQL 连接里最核心的问题你想要的到底是“两边都能匹配上的数据”还是“左边为主、右边能匹配就带上”的数据还是“左右各自保留”的数据没有想清楚这一点写出来的 JOIN 都是碰运气。这篇文章要解决的问题包括关系型数据库为什么要拆分表拆分之后为什么必须靠连接。连接时数据是怎么匹配的笛卡尔积在连接里扮演什么角色。五种连接类型分别解决什么业务问题。多表连接、自连接、聚合查询怎么组合使用。连接结果和预期不符时怎么排查怎么规避。不管你是正在学数据库原理的学生还是工作中经常写 SQL 的开发、数据分析师这篇文章都值得收藏。尤其是那些“能用但心里没底”的 JOIN 写法这次一次理清。2. 为什么需要连接从表拆分说起在讲连接之前必须先回答一个问题为什么不能把所有数据放在一张表里假设你做一个电商系统用户有姓名、手机号、注册时间订单有订单号、商品、金额、下单时间。如果都放一张表会出现什么情况一个用户下了 10 单他的姓名和手机号就要在表里重复 10 次。这不仅浪费存储空间还会带来严重的更新问题——用户改了一次手机号你得把所有相关记录一起改漏改一条数据就不一致了。这就是关系型数据库设计中最基本的规范化思想把数据按照实体拆分到不同表中每个事实只保存一份。用户信息放users订单信息放orders通过外键关联。但拆分之后又带来了一个新问题数据被拆开了查询的时候怎么把分散在多张表里的信息重新组合起来答案就是连接。这个思路用大白话说就是规范化解决的是“怎么存才不出乱子”。连接解决的是“怎么取才能拼回完整信息”。所以连接不是 SQL 里的一个可选功能它是关系型数据库“拆分存储、按需重组”模型下不可或缺的操作。这也是为什么连接会是数据库管理系统课程里的重点内容。3. 连接的本质笛卡尔积与匹配条件连接在数学上是什么它基于关系代数里的笛卡尔积运算只是在笛卡尔积之上加了一个匹配条件的过滤。3.1 什么是笛卡尔积笛卡尔积就是把两张表的数据做全组合。假如users表有 3 行数据orders表有 4 行数据它们的笛卡尔积就是 3 × 4 12 行数据每一行用户都会和每一行订单组合一次。这个操作在大部分业务场景里是没有意义的甚至是有害的因为组合出来的大部分行根本不存在对应关系。但理解笛卡尔积是理解 JOIN 的关键所有连接操作本质上都是“先做笛卡尔积再按条件筛选”或者“按条件做匹配”的过程。3.2 连接条件连接条件通常写在ON后面用来指定两张表怎么匹配。SELECT * FROM users u INNER JOIN orders o ON u.id o.user_id;这里u.id o.user_id就是连接条件。意思是只有当用户的 id 和订单的 user_id 相等时这两个表的行才被组合到一起。一个比较容易混淆的概念是ON和WHERE的区别连接类型ON 的作用WHERE 的作用INNER JOIN指定行如何匹配对连接后的结果进一步过滤LEFT JOIN指定行如何匹配左表不满足条件的行保留对连接后的结果进一步过滤但可能把保留的左表行过滤掉这条区别很重要后面讲 LEFT JOIN 时会具体说明。4. INNER JOIN只要两边都能匹配上的数据INNER JOIN是使用频率最高的连接类型也是最符合直觉的从两张表里找出满足连接条件的所有行组合不满足条件的直接丢弃。4.1 适用场景查出“有订单的用户”及其订单信息。查出“有部门归属的员工”。查出“有分类的商品”。核心特征只要有一方匹配不上这行数据就不会出现在结果集里。4.2 完整示例先建两张演示表并插入数据CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); INSERT INTO users (id, name) VALUES (1, 张三), (2, 李四), (3, 王五); INSERT INTO orders (id, user_id, amount) VALUES (101, 1, 99.00), (102, 1, 199.00), (103, 2, 299.00), (104, 4, 59.00);注意看数据王五没有订单orders表里有一条user_id 4的订单但users表里没有 id 为 4 的用户。执行 INNER JOINSELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id ORDER BY u.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.00王五没有订单不满足匹配条件被丢弃了。user_id 4的订单在users表里找不到对应用户也被丢弃了。4.3 一句话总结INNER JOIN的结果只包含两表交集部分业务表达是“都有才算数”。5. LEFT JOIN左表为主右表能配上就带LEFT JOIN全称LEFT OUTER JOINOUTER可以省略是 JOIN 家族里最容易出问题的成员。很多线上查询 bug 都出在它对WHERE条件的处理上。5.1 核心语义LEFT JOIN 以左表也就是FROM后面那张表为主左表的每一行都会保留在结果集里。右表能找到匹配行的就把右表数据带上找不到匹配行的右表列全部填 NULL。业务场景最典型的就是“所有用户及其订单没下过单的用户也要列出来订单显示为空。”5.2 完整示例SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id ORDER BY u.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.003王五NULLNULL这个结果符合预期王五虽然没下过单但仍然出现在了结果集里订单信息是 NULL。5.3 最容易踩的坑WHERE 把左表数据过滤掉了这是 LEFT JOIN 最常见的误用。很多人写SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 100 ORDER BY u.id;本意是“查所有用户只看金额大于 100 的订单”。但WHERE o.amount 100这个条件会把 NULL 过滤掉而王五的o.amount恰好是 NULL于是王五从结果集里消失了。结果user_idnameorder_idamount1张三102199.002李四103299.00说好的“所有用户”呢没有了。正确写法有两种写法一把过滤条件写进 ON 子句SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.amount 100 ORDER BY u.id;结果user_idnameorder_idamount1张三102199.002李四103299.003王五NULLNULL这里王五仍然保留订单信息为 NULL因为金额条件是在匹配阶段用的而不是在结果过滤阶段用的。写法二把 NULL 判断一起加进 WHERE表达“没订单也算”SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 100 OR o.amount IS NULL ORDER BY u.id;5.4 一句话总结LEFT JOIN 的语义是“左表全保留右表尽力匹配”。如果发现 LEFT JOIN 结果比预想的行数少第一反应应该检查 WHERE 里是不是有对右表字段的非空过滤。6. RIGHT JOIN 和 FULL OUTER JOIN低频但也要会6.1 RIGHT JOINRIGHT JOIN 是 LEFT JOIN 的镜像右表全保留左表尽力匹配。继续用上面两表数据SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id o.user_id ORDER BY o.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.00NULLNULL10459.00注意最后一行user_id 4的订单在users表里没有匹配用户但因为是 RIGHT JOIN右表订单数据全部保留左表用户字段填 NULL。在 MySQL 里之前版本不支持FULL OUTER JOIN也不能直接把 RIGHT JOIN 反过写成 LEFT JOIN 吗能。users RIGHT JOIN orders和orders LEFT JOIN users结果等价。实际开发中更常见的做法是统一用 LEFT JOIN把主表写在左边这样阅读起来更顺畅。6.2 FULL OUTER JOINFULL OUTER JOIN 是 LEFT JOIN 和 RIGHT JOIN 的并集左边匹配不上的保留右边匹配不上的也保留两边都没有匹配的行都用 NULL 填补。业务场景查两个表中所有数据的完整视图不去管是否匹配。SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u FULL OUTER JOIN orders o ON u.id o.user_id ORDER BY COALESCE(u.id, o.user_id);结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.003王五NULLNULLNULLNULL10459.00王五和user_id 4的订单都保留下来了。需要提醒的是MySQL 不原生支持FULL OUTER JOIN。需要模拟时可以用 LEFT JOIN 和 RIGHT JOIN 加 UNION 实现SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id UNION SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id o.user_id;6.3 一句话总结RIGHT JOIN、FULL OUTER JOIN 使用频率低于 LEFT JOIN但遇到“以某张表为主、另一张表兜底”的需求时知道有这些语法能少走弯路。7. CROSS JOIN、自连接与多表连接的实战形态前面的示例都是两张表连接实际业务里经常出现三张表甚至更多表的连接还经常需要一张表和自己连接。这些实战形态虽然语法上没有新东西但用法上很容易绕晕。7.1 CROSS JOIN显式笛卡尔积CROSS JOIN 会把两张表的行做全组合不需要ON条件。用的场景不多但有一种场景非常典型为“所有商品”搭配“所有促销活动”生成全量组合。SELECT p.product_name, p.price, a.activity_name FROM products p CROSS JOIN activities a;如果 products 有 100 行activities 有 5 行结果就有 500 行。CROSS JOIN 在数据量大的时候非常危险。两张百万级表做 CROSS JOIN结果会是万亿行查询基本就卡死了。写这类 SQL 之前一定要想清楚数据量级。7.2 自连接同一张表和自己连接自连接不是新的连接类型而是连接的两边都来自同一张表。最经典的场景是组织结构表一张员工表里面有员工 id 和上级 id要查出“每个员工的上级是谁”。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT ); INSERT INTO employees (id, name, manager_id) VALUES (1, 赵一, NULL), (2, 钱二, 1), (3, 孙三, 1), (4, 李四, 2);执行自连接SELECT e.name AS employee_name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id m.id ORDER BY e.id;结果employee_namemanager_name赵一NULL钱二赵一孙三赵一李四钱二这里把同一张表拆成了两个角色e代表员工m代表管理者。结构自关联类的数据组织架构、评论回复、分类层级都会用到自连接。7.3 三表连接示例多对多关系来看一个常见的多对多场景学生、课程、选课记录。学生和课程之间是多对多关系必须通过中间表student_courses关联。CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE courses ( id INT PRIMARY KEY, course_name VARCHAR(100) ); CREATE TABLE student_courses ( student_id INT, course_id INT ); INSERT INTO students (id, name) VALUES (1, 小明), (2, 小红); INSERT INTO courses (id, course_name) VALUES (1, MySQL 基础), (2, SQL 连接详解), (3, 数据库设计); INSERT INTO student_courses (student_id, course_id) VALUES (1, 1), (1, 2), (2, 2), (2, 3);查询每个学生选了哪些课SELECT s.name AS student_name, c.course_name FROM students s INNER JOIN student_courses sc ON s.id sc.student_id INNER JOIN courses c ON sc.course_id c.id ORDER BY s.id;结果student_namecourse_name小明MySQL 基础小明SQL 连接详解小红SQL 连接详解小红数据库设计多表连接的关键是理清楚表和表之间的关联路径。这里学生通过中间表找到课程中间是两跳students和student_courses通过s.id sc.student_id关联。student_courses和courses通过sc.course_id c.id关联。7.4 JOIN 配合 GROUP BY连接后再聚合连接最常用的组合玩法是先连表再分组聚合。比如按用户统计订单总额SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name ORDER BY total_amount DESC;结果nameorder_counttotal_amount张三2298.00李四1299.00王五00.00这里用 LEFT JOIN 而不是 INNER JOIN是因为要统计到没有订单的用户。COUNT(o.id)只统计非 NULL 的订单 id王五下单数为 0符合预期。顺便说一句这里GROUP BY里同时写了u.id和u.name是 MySQL 里比较稳妥的写法。如果只GROUP BY u.id在 MySQL 默认模式ONLY_FULL_GROUP_BY 关闭下能跑但换严格模式就会报错。少给自己留隐患SELECT里出现非聚合列GROUP BY就把这些都写上。7.5 JOIN 配合窗口函数用 ROW_NUMBER 去重在真实数据处理中经常遇到连接后产生重复行的情况。比如订单表里同一个订单有多条明细连接后会导致订单主表数据翻倍。这时候可以用窗口函数配合连接做去重。示例给每个用户的最新订单打上序号取第一条。SELECT u.name, o.id AS order_id, o.amount, ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.id DESC) AS rn FROM users u LEFT JOIN orders o ON u.id o.user_id;结果里rn 1的每一行就是每个用户的最新订单。外层包一层查询就能过滤出这些行。WITH ranked AS ( SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount, ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.id DESC) AS rn FROM users u LEFT JOIN orders o ON u.id o.user_id ) SELECT user_id, name, order_id, amount FROM ranked WHERE rn 1 ORDER BY user_id;结果user_idnameorder_idamount1张三102199.002李四103299.003王五NULLNULL对于没有订单的王五窗口函数会为它生成一行rn 1所以仍然保留在结果里。如果只想看有订单的用户把LEFT JOIN换成INNER JOIN即可。8. JOIN 结果与预期不符按这张排查表来定位连接写起来简单排错未必容易。下面这些问题是我在实际代码 review 和排障中经常遇到的列成表格方便对照排查。问题现象可能原因排查方式解决方案LEFT JOIN 结果行数比左表少WHERE 里用了右表字段做非空判断检查 WHERE 条件看是否过滤了 NULL把右表过滤条件移到 ON 子句或加上 OR 右表字段 IS NULLINNER JOIN 结果比预期多右表存在多条匹配记录对右表按关联字段做COUNT(*) GROUP BY验证是否有重复先对右表去重再连接或改用聚合查询连接后出现大量 NULL关联字段本身有 NULL 值检查关联字段的约束和插入逻辑业务上保证关联字段非空或使用 COALESCE 填补结果完全为空连接条件写反了检查 ON 条件中的字段归属确认左表字段和右表字段到底谁对谁查询极慢没有索引连接字段未建索引用EXPLAIN查看执行计划在连接字段和外键上建索引结果有重复行连接字段不是唯一键查看两表连接字段的基数确认业务关系是一对一还是一对多必要的时候去重同时使用多条件仍然乱ON 条件和 WHERE 条件混在一起分不清把过滤条件归类表间关联写 ON行级过滤写 WHERE先明确语义再改写 SQL用EXPLAIN看执行计划是排查连接问题的重要手段。以下是一个简单的用法EXPLAIN SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id o.user_id;重点看type字段和key字段。type如果出现ALL表示全表扫描连接字段上很可能没有索引key为 NULL 也说明没有用到索引。9. 深入理解 SQL 连接的最佳实践最后这部分是对前面的总结也是真正写生产环境 SQL 时应该长期遵守的几条原则。9.1 先定关系再写 JOIN写任何 JOIN 之前先回答三个问题主表是哪张结果集要保留谁的全部数据关联字段是什么两边是不是都有索引两表关系是一对一、一对多还是多对多这三个问题想清楚了连接类型基本就定了。主表要全保留用 LEFT JOIN两边都要匹配上用 INNER JOIN多对多场景一定先引入中间表。9.2 永远显式写出连接类型不要用逗号连接两表再在 WHERE 里写关联条件。逗号隐式连接在可读性和可维护性上都差很多。如果某天有人在WHERE里漏写了关联条件直接变成一个笛卡尔积数据量一大就是事故。推荐写法SELECT ... FROM users u INNER JOIN orders o ON u.id o.user_id;不推荐写法SELECT ... FROM users u, orders o WHERE u.id o.user_id;9.3 ON 条件做表间关联WHERE 条件做行级过滤区分这两者对 INNER JOIN 来说结果通常一样但对 OUTER JOIN 来说完全不同。这是一个原则性约定ON写两个表的匹配规则。WHERE写结果集的行级过滤。遵守这个约定SQL 的意图会清晰很多。9.4 连接字段一定要有索引连接的性能瓶颈通常不在连接本身而在没有索引时触发的全表扫描和临时表。两个规范外键字段必须有索引。多表连接时所有参与ON的字段都要有索引特别是右表的关联字段。在 MySQL 中创建索引CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_student_courses_student_id ON student_courses(student_id); CREATE INDEX idx_student_courses_course_id ON student_courses(course_id);9.5 生产环境变更前先确认数据量级CROSS JOIN 或忘写条件的笛卡尔积在测试环境可能几秒钟就出来了因为数据量小。到生产环境几千万行的两张表做一次无约束连接可以直接把数据库打满。写连接查询时心里要有数据量级的数。生产环境执行任何 JOIN 查询前建议先SELECT COUNT(*)确认数据规模或者用EXPLAIN确认执行计划没有全表扫描。9.6 多表连接别贪多一个查询连接 5、6 张表执行计划会变得很复杂排错也难。如果是为了展示和统计可以考虑拆分查询或者在应用层做组装。连接本身不是错但是一屏看不完 SQL 的时候就该想想是不是拆得太碎了。9.7 关注 NULL 带来的业务语义LEFT JOIN 之后右表字段为 NULL 同时意味着两种可能业务上没关联到数据或者数据本身是 NULL。写业务代码时如果统计结果和预期差几个数多半是 NULL 被过滤或者被COUNT漏掉了。这里有个通用建议统计行数时使用COUNT(*)而不是COUNT(具体字段)。COUNT(具体字段)会跳过 NULL而COUNT(*)会统计所有行两者的差异恰好就是右表 NULL 的行数。SQL 连接看起来只是语法问题实际上考验的是对数据关系和数据语义的理解。把“连接”这件事想清楚不只是会写 JOIN更是在写任何复杂查询时都不会跑偏。建议把这篇文章收藏起来下次写连接查询的时候翻出来对照一下省去很多试错的时间。 SQL JOIN 从原理到实践：彻底搞懂 INNER JOIN、LEFT JOIN 与多表连接
很多人学 SQL 的时候前面SELECT、WHERE、GROUP BY都学得挺好的一到JOIN就开始懵。面试被问“LEFT JOIN 和 INNER JOIN 有什么区别”能答上来但真到写业务查询的时候要么查出来的数据比预期多要么莫名其妙丢行要么一条 SQL 跑半天。问题出在哪大概率是没把“连接”这件事从底层逻辑上想清楚。这篇文章要把 SQL 中的连接讲透不是只背语法而是从“为什么需要连接”“连接时数据是怎么匹配的”讲起把INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN一次说明白再加上多表连接、自连接和聚合场景。读完你能判断什么时候该用哪种连接也知道查出来数据不对时往哪个方向排查。1. 这篇文章真正要解决的问题先看一个特别常见的场景。你有一张users表存用户基本信息一张orders表存订单记录两张表通过user_id关联。需求很简单查出每个用户名下的订单数量。很多新手第一反应是“能不能把两张表的数据拼在一起”然后就开始写SELECT * FROM users, orders WHERE users.id orders.user_id;这句 SQL 在 MySQL 里能跑结果看起来也对。但你要是追问一句“这个逗号是什么意思”对方可能会愣住。更麻烦的是当需求变成“把所有用户都列出来包括那些没下过单的用户”上面的写法就查不出来了因为WHERE已经把没匹配上的行过滤掉了。这其实就是 SQL 连接里最核心的问题你想要的到底是“两边都能匹配上的数据”还是“左边为主、右边能匹配就带上”的数据还是“左右各自保留”的数据没有想清楚这一点写出来的 JOIN 都是碰运气。这篇文章要解决的问题包括关系型数据库为什么要拆分表拆分之后为什么必须靠连接。连接时数据是怎么匹配的笛卡尔积在连接里扮演什么角色。五种连接类型分别解决什么业务问题。多表连接、自连接、聚合查询怎么组合使用。连接结果和预期不符时怎么排查怎么规避。不管你是正在学数据库原理的学生还是工作中经常写 SQL 的开发、数据分析师这篇文章都值得收藏。尤其是那些“能用但心里没底”的 JOIN 写法这次一次理清。2. 为什么需要连接从表拆分说起在讲连接之前必须先回答一个问题为什么不能把所有数据放在一张表里假设你做一个电商系统用户有姓名、手机号、注册时间订单有订单号、商品、金额、下单时间。如果都放一张表会出现什么情况一个用户下了 10 单他的姓名和手机号就要在表里重复 10 次。这不仅浪费存储空间还会带来严重的更新问题——用户改了一次手机号你得把所有相关记录一起改漏改一条数据就不一致了。这就是关系型数据库设计中最基本的规范化思想把数据按照实体拆分到不同表中每个事实只保存一份。用户信息放users订单信息放orders通过外键关联。但拆分之后又带来了一个新问题数据被拆开了查询的时候怎么把分散在多张表里的信息重新组合起来答案就是连接。这个思路用大白话说就是规范化解决的是“怎么存才不出乱子”。连接解决的是“怎么取才能拼回完整信息”。所以连接不是 SQL 里的一个可选功能它是关系型数据库“拆分存储、按需重组”模型下不可或缺的操作。这也是为什么连接会是数据库管理系统课程里的重点内容。3. 连接的本质笛卡尔积与匹配条件连接在数学上是什么它基于关系代数里的笛卡尔积运算只是在笛卡尔积之上加了一个匹配条件的过滤。3.1 什么是笛卡尔积笛卡尔积就是把两张表的数据做全组合。假如users表有 3 行数据orders表有 4 行数据它们的笛卡尔积就是 3 × 4 12 行数据每一行用户都会和每一行订单组合一次。这个操作在大部分业务场景里是没有意义的甚至是有害的因为组合出来的大部分行根本不存在对应关系。但理解笛卡尔积是理解 JOIN 的关键所有连接操作本质上都是“先做笛卡尔积再按条件筛选”或者“按条件做匹配”的过程。3.2 连接条件连接条件通常写在ON后面用来指定两张表怎么匹配。SELECT * FROM users u INNER JOIN orders o ON u.id o.user_id;这里u.id o.user_id就是连接条件。意思是只有当用户的 id 和订单的 user_id 相等时这两个表的行才被组合到一起。一个比较容易混淆的概念是ON和WHERE的区别连接类型ON 的作用WHERE 的作用INNER JOIN指定行如何匹配对连接后的结果进一步过滤LEFT JOIN指定行如何匹配左表不满足条件的行保留对连接后的结果进一步过滤但可能把保留的左表行过滤掉这条区别很重要后面讲 LEFT JOIN 时会具体说明。4. INNER JOIN只要两边都能匹配上的数据INNER JOIN是使用频率最高的连接类型也是最符合直觉的从两张表里找出满足连接条件的所有行组合不满足条件的直接丢弃。4.1 适用场景查出“有订单的用户”及其订单信息。查出“有部门归属的员工”。查出“有分类的商品”。核心特征只要有一方匹配不上这行数据就不会出现在结果集里。4.2 完整示例先建两张演示表并插入数据CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2) ); INSERT INTO users (id, name) VALUES (1, 张三), (2, 李四), (3, 王五); INSERT INTO orders (id, user_id, amount) VALUES (101, 1, 99.00), (102, 1, 199.00), (103, 2, 299.00), (104, 4, 59.00);注意看数据王五没有订单orders表里有一条user_id 4的订单但users表里没有 id 为 4 的用户。执行 INNER JOINSELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id ORDER BY u.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.00王五没有订单不满足匹配条件被丢弃了。user_id 4的订单在users表里找不到对应用户也被丢弃了。4.3 一句话总结INNER JOIN的结果只包含两表交集部分业务表达是“都有才算数”。5. LEFT JOIN左表为主右表能配上就带LEFT JOIN全称LEFT OUTER JOINOUTER可以省略是 JOIN 家族里最容易出问题的成员。很多线上查询 bug 都出在它对WHERE条件的处理上。5.1 核心语义LEFT JOIN 以左表也就是FROM后面那张表为主左表的每一行都会保留在结果集里。右表能找到匹配行的就把右表数据带上找不到匹配行的右表列全部填 NULL。业务场景最典型的就是“所有用户及其订单没下过单的用户也要列出来订单显示为空。”5.2 完整示例SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id ORDER BY u.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.003王五NULLNULL这个结果符合预期王五虽然没下过单但仍然出现在了结果集里订单信息是 NULL。5.3 最容易踩的坑WHERE 把左表数据过滤掉了这是 LEFT JOIN 最常见的误用。很多人写SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 100 ORDER BY u.id;本意是“查所有用户只看金额大于 100 的订单”。但WHERE o.amount 100这个条件会把 NULL 过滤掉而王五的o.amount恰好是 NULL于是王五从结果集里消失了。结果user_idnameorder_idamount1张三102199.002李四103299.00说好的“所有用户”呢没有了。正确写法有两种写法一把过滤条件写进 ON 子句SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.amount 100 ORDER BY u.id;结果user_idnameorder_idamount1张三102199.002李四103299.003王五NULLNULL这里王五仍然保留订单信息为 NULL因为金额条件是在匹配阶段用的而不是在结果过滤阶段用的。写法二把 NULL 判断一起加进 WHERE表达“没订单也算”SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.amount 100 OR o.amount IS NULL ORDER BY u.id;5.4 一句话总结LEFT JOIN 的语义是“左表全保留右表尽力匹配”。如果发现 LEFT JOIN 结果比预想的行数少第一反应应该检查 WHERE 里是不是有对右表字段的非空过滤。6. RIGHT JOIN 和 FULL OUTER JOIN低频但也要会6.1 RIGHT JOINRIGHT JOIN 是 LEFT JOIN 的镜像右表全保留左表尽力匹配。继续用上面两表数据SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id o.user_id ORDER BY o.id;结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.00NULLNULL10459.00注意最后一行user_id 4的订单在users表里没有匹配用户但因为是 RIGHT JOIN右表订单数据全部保留左表用户字段填 NULL。在 MySQL 里之前版本不支持FULL OUTER JOIN也不能直接把 RIGHT JOIN 反过写成 LEFT JOIN 吗能。users RIGHT JOIN orders和orders LEFT JOIN users结果等价。实际开发中更常见的做法是统一用 LEFT JOIN把主表写在左边这样阅读起来更顺畅。6.2 FULL OUTER JOINFULL OUTER JOIN 是 LEFT JOIN 和 RIGHT JOIN 的并集左边匹配不上的保留右边匹配不上的也保留两边都没有匹配的行都用 NULL 填补。业务场景查两个表中所有数据的完整视图不去管是否匹配。SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u FULL OUTER JOIN orders o ON u.id o.user_id ORDER BY COALESCE(u.id, o.user_id);结果user_idnameorder_idamount1张三10199.001张三102199.002李四103299.003王五NULLNULLNULLNULL10459.00王五和user_id 4的订单都保留下来了。需要提醒的是MySQL 不原生支持FULL OUTER JOIN。需要模拟时可以用 LEFT JOIN 和 RIGHT JOIN 加 UNION 实现SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id UNION SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount FROM users u RIGHT JOIN orders o ON u.id o.user_id;6.3 一句话总结RIGHT JOIN、FULL OUTER JOIN 使用频率低于 LEFT JOIN但遇到“以某张表为主、另一张表兜底”的需求时知道有这些语法能少走弯路。7. CROSS JOIN、自连接与多表连接的实战形态前面的示例都是两张表连接实际业务里经常出现三张表甚至更多表的连接还经常需要一张表和自己连接。这些实战形态虽然语法上没有新东西但用法上很容易绕晕。7.1 CROSS JOIN显式笛卡尔积CROSS JOIN 会把两张表的行做全组合不需要ON条件。用的场景不多但有一种场景非常典型为“所有商品”搭配“所有促销活动”生成全量组合。SELECT p.product_name, p.price, a.activity_name FROM products p CROSS JOIN activities a;如果 products 有 100 行activities 有 5 行结果就有 500 行。CROSS JOIN 在数据量大的时候非常危险。两张百万级表做 CROSS JOIN结果会是万亿行查询基本就卡死了。写这类 SQL 之前一定要想清楚数据量级。7.2 自连接同一张表和自己连接自连接不是新的连接类型而是连接的两边都来自同一张表。最经典的场景是组织结构表一张员工表里面有员工 id 和上级 id要查出“每个员工的上级是谁”。CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT ); INSERT INTO employees (id, name, manager_id) VALUES (1, 赵一, NULL), (2, 钱二, 1), (3, 孙三, 1), (4, 李四, 2);执行自连接SELECT e.name AS employee_name, m.name AS manager_name FROM employees e LEFT JOIN employees m ON e.manager_id m.id ORDER BY e.id;结果employee_namemanager_name赵一NULL钱二赵一孙三赵一李四钱二这里把同一张表拆成了两个角色e代表员工m代表管理者。结构自关联类的数据组织架构、评论回复、分类层级都会用到自连接。7.3 三表连接示例多对多关系来看一个常见的多对多场景学生、课程、选课记录。学生和课程之间是多对多关系必须通过中间表student_courses关联。CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE courses ( id INT PRIMARY KEY, course_name VARCHAR(100) ); CREATE TABLE student_courses ( student_id INT, course_id INT ); INSERT INTO students (id, name) VALUES (1, 小明), (2, 小红); INSERT INTO courses (id, course_name) VALUES (1, MySQL 基础), (2, SQL 连接详解), (3, 数据库设计); INSERT INTO student_courses (student_id, course_id) VALUES (1, 1), (1, 2), (2, 2), (2, 3);查询每个学生选了哪些课SELECT s.name AS student_name, c.course_name FROM students s INNER JOIN student_courses sc ON s.id sc.student_id INNER JOIN courses c ON sc.course_id c.id ORDER BY s.id;结果student_namecourse_name小明MySQL 基础小明SQL 连接详解小红SQL 连接详解小红数据库设计多表连接的关键是理清楚表和表之间的关联路径。这里学生通过中间表找到课程中间是两跳students和student_courses通过s.id sc.student_id关联。student_courses和courses通过sc.course_id c.id关联。7.4 JOIN 配合 GROUP BY连接后再聚合连接最常用的组合玩法是先连表再分组聚合。比如按用户统计订单总额SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.amount), 0) AS total_amount FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id, u.name ORDER BY total_amount DESC;结果nameorder_counttotal_amount张三2298.00李四1299.00王五00.00这里用 LEFT JOIN 而不是 INNER JOIN是因为要统计到没有订单的用户。COUNT(o.id)只统计非 NULL 的订单 id王五下单数为 0符合预期。顺便说一句这里GROUP BY里同时写了u.id和u.name是 MySQL 里比较稳妥的写法。如果只GROUP BY u.id在 MySQL 默认模式ONLY_FULL_GROUP_BY 关闭下能跑但换严格模式就会报错。少给自己留隐患SELECT里出现非聚合列GROUP BY就把这些都写上。7.5 JOIN 配合窗口函数用 ROW_NUMBER 去重在真实数据处理中经常遇到连接后产生重复行的情况。比如订单表里同一个订单有多条明细连接后会导致订单主表数据翻倍。这时候可以用窗口函数配合连接做去重。示例给每个用户的最新订单打上序号取第一条。SELECT u.name, o.id AS order_id, o.amount, ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.id DESC) AS rn FROM users u LEFT JOIN orders o ON u.id o.user_id;结果里rn 1的每一行就是每个用户的最新订单。外层包一层查询就能过滤出这些行。WITH ranked AS ( SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount, ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.id DESC) AS rn FROM users u LEFT JOIN orders o ON u.id o.user_id ) SELECT user_id, name, order_id, amount FROM ranked WHERE rn 1 ORDER BY user_id;结果user_idnameorder_idamount1张三102199.002李四103299.003王五NULLNULL对于没有订单的王五窗口函数会为它生成一行rn 1所以仍然保留在结果里。如果只想看有订单的用户把LEFT JOIN换成INNER JOIN即可。8. JOIN 结果与预期不符按这张排查表来定位连接写起来简单排错未必容易。下面这些问题是我在实际代码 review 和排障中经常遇到的列成表格方便对照排查。问题现象可能原因排查方式解决方案LEFT JOIN 结果行数比左表少WHERE 里用了右表字段做非空判断检查 WHERE 条件看是否过滤了 NULL把右表过滤条件移到 ON 子句或加上 OR 右表字段 IS NULLINNER JOIN 结果比预期多右表存在多条匹配记录对右表按关联字段做COUNT(*) GROUP BY验证是否有重复先对右表去重再连接或改用聚合查询连接后出现大量 NULL关联字段本身有 NULL 值检查关联字段的约束和插入逻辑业务上保证关联字段非空或使用 COALESCE 填补结果完全为空连接条件写反了检查 ON 条件中的字段归属确认左表字段和右表字段到底谁对谁查询极慢没有索引连接字段未建索引用EXPLAIN查看执行计划在连接字段和外键上建索引结果有重复行连接字段不是唯一键查看两表连接字段的基数确认业务关系是一对一还是一对多必要的时候去重同时使用多条件仍然乱ON 条件和 WHERE 条件混在一起分不清把过滤条件归类表间关联写 ON行级过滤写 WHERE先明确语义再改写 SQL用EXPLAIN看执行计划是排查连接问题的重要手段。以下是一个简单的用法EXPLAIN SELECT u.name, o.id AS order_id FROM users u LEFT JOIN orders o ON u.id o.user_id;重点看type字段和key字段。type如果出现ALL表示全表扫描连接字段上很可能没有索引key为 NULL 也说明没有用到索引。9. 深入理解 SQL 连接的最佳实践最后这部分是对前面的总结也是真正写生产环境 SQL 时应该长期遵守的几条原则。9.1 先定关系再写 JOIN写任何 JOIN 之前先回答三个问题主表是哪张结果集要保留谁的全部数据关联字段是什么两边是不是都有索引两表关系是一对一、一对多还是多对多这三个问题想清楚了连接类型基本就定了。主表要全保留用 LEFT JOIN两边都要匹配上用 INNER JOIN多对多场景一定先引入中间表。9.2 永远显式写出连接类型不要用逗号连接两表再在 WHERE 里写关联条件。逗号隐式连接在可读性和可维护性上都差很多。如果某天有人在WHERE里漏写了关联条件直接变成一个笛卡尔积数据量一大就是事故。推荐写法SELECT ... FROM users u INNER JOIN orders o ON u.id o.user_id;不推荐写法SELECT ... FROM users u, orders o WHERE u.id o.user_id;9.3 ON 条件做表间关联WHERE 条件做行级过滤区分这两者对 INNER JOIN 来说结果通常一样但对 OUTER JOIN 来说完全不同。这是一个原则性约定ON写两个表的匹配规则。WHERE写结果集的行级过滤。遵守这个约定SQL 的意图会清晰很多。9.4 连接字段一定要有索引连接的性能瓶颈通常不在连接本身而在没有索引时触发的全表扫描和临时表。两个规范外键字段必须有索引。多表连接时所有参与ON的字段都要有索引特别是右表的关联字段。在 MySQL 中创建索引CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_student_courses_student_id ON student_courses(student_id); CREATE INDEX idx_student_courses_course_id ON student_courses(course_id);9.5 生产环境变更前先确认数据量级CROSS JOIN 或忘写条件的笛卡尔积在测试环境可能几秒钟就出来了因为数据量小。到生产环境几千万行的两张表做一次无约束连接可以直接把数据库打满。写连接查询时心里要有数据量级的数。生产环境执行任何 JOIN 查询前建议先SELECT COUNT(*)确认数据规模或者用EXPLAIN确认执行计划没有全表扫描。9.6 多表连接别贪多一个查询连接 5、6 张表执行计划会变得很复杂排错也难。如果是为了展示和统计可以考虑拆分查询或者在应用层做组装。连接本身不是错但是一屏看不完 SQL 的时候就该想想是不是拆得太碎了。9.7 关注 NULL 带来的业务语义LEFT JOIN 之后右表字段为 NULL 同时意味着两种可能业务上没关联到数据或者数据本身是 NULL。写业务代码时如果统计结果和预期差几个数多半是 NULL 被过滤或者被COUNT漏掉了。这里有个通用建议统计行数时使用COUNT(*)而不是COUNT(具体字段)。COUNT(具体字段)会跳过 NULL而COUNT(*)会统计所有行两者的差异恰好就是右表 NULL 的行数。SQL 连接看起来只是语法问题实际上考验的是对数据关系和数据语义的理解。把“连接”这件事想清楚不只是会写 JOIN更是在写任何复杂查询时都不会跑偏。建议把这篇文章收藏起来下次写连接查询的时候翻出来对照一下省去很多试错的时间。