【MySQL 笔记】多表查询（Multi-Table Query / JOIN）详解

多表查询是 MySQL 中最核心、最常用的技能之一，几乎所有真实业务场景（订单+用户、商品+分类、员工+部门等）都离不开它。

一、MySQL 多表查询核心方式对比（2026 年主流用法）

二、经典示例表（建议自己建表练习）

-- 部门表
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;

四、高频业务场景总结（面试 + 实战）

五、写多表查询的实用技巧 & 2026 年注意点

1. 永远写 ON 条件（别把连接条件写 WHERE，容易逻辑错乱）
2. 小表驱动大表：LEFT JOIN 时把小表放左边（优化器通常会调整，但显式写好）
3. JOIN 字段必须有索引（尤其是外键、主键、频繁 ON 的列）
4. 用表别名（e、d、o、u 常用，清晰 + 避免歧义）
5. NULL 判断：LEFT JOIN 后常用 WHERE xx IS NULL 找“只有左表有的”
6. EXPLAIN 验证：写完复杂 JOIN 后先 EXPLAIN 看执行计划
7. **避免 SELECT ***：明确列出需要的字段（减少 IO）

下一期预告建议：MySQL 子查询（Subquery）全家桶 + 相关子查询 vs 非相关 + IN / EXISTS 性能对比

有特别迷惑的多表查询写法（比如三表以上、复杂过滤、性能卡住等），欢迎留言，我们一起画图分析～
祝大家 JOIN 写得又准又快！🚀