【MySQL 笔记】多表查询（Multi-Table Query / JOIN）详解
多表查询是 MySQL 中最核心、最常用的技能之一，几乎所有真实业务场景（订单+用户、商品+分类、员工+部门等）都离不开它。
一、MySQL 多表查询核心方式对比（2026 年主流用法）
| 查询方式 | 关键字 / 写法 | 保留哪些行 | 结果行数特征 | 性能影响 | 典型业务场景 | 推荐指数 |
|---|---|---|---|---|---|---|
| 隐式连接（旧写法） | FROM A, B WHERE A.id = B.id | 只匹配的行（相当于 INNER JOIN） | ≤ min(A,B) | 中等 | 历史代码、简单查询 | ★★☆☆☆ |
| INNER JOIN | INNER JOIN / JOIN | 两表都匹配的行 | ≤ min(A,B) | 较好 | 必须双方都有数据的查询 | ★★★★★ |
| LEFT [OUTER] JOIN | LEFT JOIN | 左表全部 + 右表匹配（右缺→NULL） | = 左表行数 | 中等 | 查所有商品 + 是否有销量（没卖的也显示） | ★★★★★ |
| RIGHT [OUTER] JOIN | RIGHT JOIN | 右表全部 + 左表匹配（左缺→NULL） | = 右表行数 | 中等 | 查所有课程 + 报名情况 | ★★★★☆ |
| FULL [OUTER] JOIN | FULL JOIN（MySQL 不原生支持） | 两表全部（不匹配补 NULL） | ≈ A + B – 交集 | 较高 | 数据对账、找出差异记录 | ★★★★☆ |
| CROSS JOIN | CROSS JOIN / ,（逗号无条件） | 笛卡尔积（每行×每行） | = A行数 × B行数 | 高（爆炸） | 生成测试数据、全组合 | ★★☆☆☆ |
| SELF JOIN | 表自己 JOIN 自己（用别名） | 同表不同角色匹配 | 取决于 ON 条件 | 中等 | 员工-经理、分类父子、上下级 | ★★★★☆ |
二、经典示例表（建议自己建表练习）
-- 部门表
CREATE TABLE dept (
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);
INSERT INTO dept VALUES
(10, 'RESEARCH', 'BEIJING'),
(20, 'SALES', 'SHANGHAI'),
(30, 'ACCOUNTING','GUANGZHOU'),
(40, 'OPERATIONS','SHENZHEN'); -- 没人
-- 员工表
CREATE TABLE emp (
empno INT PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT, -- 领导编号（自关联）
sal DECIMAL(10,2),
deptno INT,
FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902, 800.00, 20),
(7499,'ALLEN','SALESMAN',7698,1600.00, 30),
(7521,'WARD', 'SALESMAN',7698,1250.00, 30),
(7566,'JONES','MANAGER',7839,2975.00, 20),
(7698,'BLAKE','MANAGER',7839,2850.00, 30),
(7782,'CLARK','MANAGER',7839,2450.00, 10),
(7839,'KING', 'PRESIDENT',NULL,5000.00,10);
三、各种 JOIN 实战写法（全部敲一遍最有感觉）
-- 1. INNER JOIN（最常用）
SELECT e.ename, e.job, d.dname, d.loc
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;
-- 结果：只显示有部门的员工（deptno=40 的部门没人，不显示）
-- 2. LEFT JOIN（保留左表全部）
SELECT e.ename, e.job, d.dname, d.loc
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;
-- 结果：所有员工 + 部门信息（即使 deptno 不匹配也显示员工，dname 为 NULL）
-- 3. RIGHT JOIN（保留右表全部）
SELECT e.ename, d.dname, d.loc
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;
-- 结果：所有部门 + 员工（OPERATIONS 部门没人，ename 为 NULL）
-- 4. MySQL 模拟 FULL JOIN（最常见写法）
(SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno)
UNION
(SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno IS NULL);
-- 结果：所有员工 + 所有部门，没匹配的补 NULL
-- 5. CROSS JOIN（笛卡尔积，慎用！）
SELECT e.ename, d.dname
FROM emp e CROSS JOIN dept d;
-- 结果：7员工 × 4部门 = 28 行（爆炸）
-- 6. SELF JOIN（员工找上级）
SELECT e.ename AS 员工, m.ename AS 上级
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno;
-- 结果：显示每个员工及其直接领导（KING 没有上级 → NULL）
-- 7. 三表联查示例（部门 → 员工 → 上级）
SELECT e.ename, e.job, d.dname, m.ename AS manager_name
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
LEFT JOIN emp m ON e.mgr = m.empno;
四、高频业务场景总结（面试 + 实战）
| 场景 | 推荐 JOIN 类型 | 关键写法技巧 |
|---|---|---|
| 订单详情 + 用户信息（必须有用户） | INNER JOIN | 脏数据过滤 |
| 所有商品 + 最近销量（没卖出显示0） | LEFT JOIN + COALESCE | COALESCE(sales, 0) |
| 所有课程 + 选课人数（没人选也显示） | LEFT JOIN + GROUP BY | COUNT(student_id) |
| 找出只在一张表有的记录 | FULL JOIN（模拟）或 LEFT+IS NULL | WHERE 右表.id IS NULL |
| 员工上下级关系 | SELF JOIN | 用别名区分角色 |
| 生成测试数据组合 | CROSS JOIN + LIMIT | 控制行数 |
五、写多表查询的实用技巧 & 2026 年注意点
- 永远写 ON 条件（别把连接条件写 WHERE，容易逻辑错乱）
- 小表驱动大表：LEFT JOIN 时把小表放左边（优化器通常会调整，但显式写好）
- JOIN 字段必须有索引（尤其是外键、主键、频繁 ON 的列）
- 用表别名（e、d、o、u 常用，清晰 + 避免歧义）
- NULL 判断：LEFT JOIN 后常用
WHERE xx IS NULL找“只有左表有的” - EXPLAIN 验证：写完复杂 JOIN 后先 EXPLAIN 看执行计划
- **避免 SELECT ***：明确列出需要的字段（减少 IO）
下一期预告建议：MySQL 子查询（Subquery）全家桶 + 相关子查询 vs 非相关 + IN / EXISTS 性能对比
有特别迷惑的多表查询写法（比如三表以上、复杂过滤、性能卡住等），欢迎留言，我们一起画图分析～
祝大家 JOIN 写得又准又快！🚀
