当前位置：首页 > news >正文

MySQL【表的内外连接】

news 2026/5/12 12:56:36

【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 模拟）

三、经典示例表（建议自己建表跑一遍）

-- 学生表CREATETABLEstudents(idINTPRIMARYKEY,nameVARCHAR(20));INSERTINTOstudentsVALUES(1,'小明'),(2,'小红'),(3,'小刚'),(4,'小李'),(5,'小王');-- 课程表（选课记录）CREATETABLEcourses(sidINT,-- 学生IDcnameVARCHAR(30),scoreTINYINT);INSERTINTOcoursesVALUES(1,'数学',95),(1,'英语',88),(2,'数学',76),(3,'英语',92),(3,'物理',85),(6,'化学',90);-- 注意：sid=6 学生不存在

四、各种 JOIN 实战演示

-- 1. INNER JOIN（最常用，只显示有交集的）SELECTs.name,c.cname,c.scoreFROMstudents sINNERJOINcourses cONs.id=c.sid;-- 结果：小明数学95、小明英语88、小红数学76、小刚英语92、小刚物理85-- （小李、小王、sid=6 的都不出现）-- 2. LEFT JOIN（保留左表全部）SELECTs.name,c.cname,c.scoreFROMstudents sLEFTJOINcourses cONs.id=c.sid;-- 结果：上面5行 + 小李 NULL NULL + 小王 NULL NULL-- 3. RIGHT JOIN（保留右表全部）SELECTs.name,c.cname,c.scoreFROMstudents sRIGHTJOINcourses cONs.id=c.sid;-- 结果：上面5行 + NULL 化学 90 （sid=6 没有对应学生）-- 4. MySQL 模拟 FULL JOIN（最常用写法）(SELECTs.name,c.cname,c.scoreFROMstudents sLEFTJOINcourses cONs.id=c.sid)UNION(SELECTs.name,c.cname,c.scoreFROMstudents sRIGHTJOINcourses cONs.id=c.sidWHEREs.idISNULL);-- 避免重复-- 结果：左表全部 + 右表独有的（sid=6 那行）-- 5. CROSS JOIN（笛卡尔积，慎用！）SELECTs.name,c.cnameFROMstudents sCROSSJOINcourses c;-- 结果：5学生 × 6条选课记录 = 30行（爆炸式增长）-- 6. SELF JOIN 示例（员工找经理）-- 假设 employees 表有 id, name, manager_idSELECTe.nameAS员工,m.nameAS经理FROMemployees eLEFTJOINemployees mONe.manager_id=m.id;

五、高频使用场景总结（面试+实战）

场景	推荐 JOIN 类型	为什么
订单 + 用户信息（必须有用户）	INNER JOIN	没用户的订单通常是脏数据
所有商品 + 销量（没卖出的显示0）	LEFT JOIN	商品表为主，销量表可能缺记录
所有课程 + 报名学生数	LEFT JOIN + GROUP BY	课程表为主，没人报名的也要显示
找出两表差异记录	FULL JOIN（模拟）或 LEFT+RIGHT+UNION	常用于数据对账、同步检查
生成测试数据、全组合	CROSS JOIN	很少用于生产，常见于 mock 数据
上下级、分类父子关系	SELF JOIN	表自己连自己