【MySQL 笔记】表的内外连接（JOIN）详解
MySQL 中多表查询最核心的操作就是 JOIN（连接），它决定了如何把两张（或多张）表的数据根据某个条件“拼”在一起。
一、MySQL 支持的 JOIN 类型一览表（2026 年现状）
| JOIN 类型 | 写法（常用） | 保留哪些数据 | MySQL 原生支持 | 结果行数特征 | 典型业务场景 |
|---|---|---|---|---|---|
| INNER JOIN | INNER JOIN / JOIN | 两表都匹配上的行（交集） | 是 | ≤ 最小表行数 | 必须双方都有数据的查询（如订单+用户） |
| LEFT [OUTER] JOIN | LEFT JOIN | 左表全部 + 右表匹配的（右表缺→NULL） | 是 | = 左表行数 | 查所有学生 + 是否选课（没选的也显示） |
| RIGHT [OUTER] JOIN | RIGHT JOIN | 右表全部 + 左表匹配的（左表缺→NULL） | 是 | = 右表行数 | 查所有课程 + 选课学生（没人选的也显示） |
| FULL [OUTER] JOIN | FULL JOIN / FULL OUTER JOIN | 两表全部（不匹配处补 NULL） | 否（需模拟） | ≈ 左表 + 右表 – 交集 | 对比两表差异、合并所有记录 |
| CROSS JOIN | CROSS JOIN / ,（逗号） | 笛卡尔积（每行×每行） | 是 | = 左表行数 × 右表行数 | 生成测试数据、全组合场景 |
| SELF JOIN | 表自己 JOIN 自己 | 同表不同别名 | 是（语法支持） | 取决于 ON 条件 | 员工-经理关系、上下级、分类树 |
二、最核心的四种连接对比（带 Venn 图思维）
想象两张表：学生表（左） 和 选课表（右）
- INNER JOIN → 两个圆重叠的部分
- LEFT JOIN → 左圆全部 + 重叠部分
- RIGHT JOIN → 右圆全部 + 重叠部分
- FULL JOIN → 两个圆的并集（MySQL 要用 UNION 模拟）
三、经典示例表（建议自己建表跑一遍）
-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20)
);
INSERT INTO students VALUES
(1, '小明'), (2, '小红'), (3, '小刚'), (4, '小李'), (5, '小王');
-- 课程表（选课记录）
CREATE TABLE courses (
sid INT, -- 学生ID
cname VARCHAR(30),
score TINYINT
);
INSERT INTO courses VALUES
(1, '数学', 95), (1, '英语', 88),
(2, '数学', 76),
(3, '英语', 92), (3, '物理', 85),
(6, '化学', 90); -- 注意：sid=6 学生不存在
四、各种 JOIN 实战演示
-- 1. INNER JOIN（最常用，只显示有交集的）
SELECT s.name, c.cname, c.score
FROM students s
INNER JOIN courses c ON s.id = c.sid;
-- 结果：小明数学95、小明英语88、小红数学76、小刚英语92、小刚物理85
-- （小李、小王、sid=6 的都不出现）
-- 2. LEFT JOIN（保留左表全部）
SELECT s.name, c.cname, c.score
FROM students s
LEFT JOIN courses c ON s.id = c.sid;
-- 结果：上面5行 + 小李 NULL NULL + 小王 NULL NULL
-- 3. RIGHT JOIN（保留右表全部）
SELECT s.name, c.cname, c.score
FROM students s
RIGHT JOIN courses c ON s.id = c.sid;
-- 结果：上面5行 + NULL 化学 90 （sid=6 没有对应学生）
-- 4. MySQL 模拟 FULL JOIN（最常用写法）
(SELECT s.name, c.cname, c.score
FROM students s
LEFT JOIN courses c ON s.id = c.sid)
UNION
(SELECT s.name, c.cname, c.score
FROM students s
RIGHT JOIN courses c ON s.id = c.sid
WHERE s.id IS NULL); -- 避免重复
-- 结果：左表全部 + 右表独有的（sid=6 那行）
-- 5. CROSS JOIN（笛卡尔积，慎用！）
SELECT s.name, c.cname
FROM students s
CROSS JOIN courses c;
-- 结果：5学生 × 6条选课记录 = 30行（爆炸式增长）
-- 6. SELF JOIN 示例（员工找经理）
-- 假设 employees 表有 id, name, manager_id
SELECT e.name AS 员工, m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
五、高频使用场景总结（面试+实战）
| 场景 | 推荐 JOIN 类型 | 为什么 |
|---|---|---|
| 订单 + 用户信息（必须有用户） | INNER JOIN | 没用户的订单通常是脏数据 |
| 所有商品 + 销量（没卖出的显示0） | LEFT JOIN | 商品表为主，销量表可能缺记录 |
| 所有课程 + 报名学生数 | LEFT JOIN + GROUP BY | 课程表为主，没人报名的也要显示 |
| 找出两表差异记录 | FULL JOIN（模拟）或 LEFT+RIGHT+UNION | 常用于数据对账、同步检查 |
| 生成测试数据、全组合 | CROSS JOIN | 很少用于生产，常见于 mock 数据 |
| 上下级、分类父子关系 | SELF JOIN | 表自己连自己 |
六、写 JOIN 的小技巧 & 注意点
- 永远写 ON 条件（别用 WHERE 写连接条件，容易逻辑混乱）
- 小表驱动大表：把数据量小的表放左边（尤其是 LEFT JOIN）
- 索引：JOIN 字段必须有索引（尤其是 WHERE / ON / GROUP BY 涉及的列）
- 用别名：表名长时必须起别名（s、c、u、o 常用）
- NULL 判断：LEFT/RIGHT 后常用
WHERE xx IS NULL找“只有左/右表有的” - FULL JOIN 替代方案：LEFT JOIN + UNION + RIGHT JOIN（WHERE 左表 IS NULL）
下一期预告建议：MySQL 多表 JOIN 性能优化 + EXPLAIN 分析 + 索引使用技巧
有 JOIN 写法特别迷惑的业务场景，欢迎留言，我们一起画图分析～
祝大家 JOIN 写得又快又准！🚀
