多表关联查询

员工姓名在 emp，部门名称在 dept——要「姓名 + 部门名」就得把两张表拼起来。这就是关联查询（JOIN）。

系列：MySQL 入门到查询 · 第 14 / 17 篇（第 14 天 · 5 月 4 日）
上一篇：子查询
下一篇：约束
总目录：系列索引

环境说明

• 库： rain（SQL 中写 use train;）

use train;

drop table if exists emp;
drop table if exists dept;

create table dept(
    deptno int,
    dname varchar(20),
    loc varchar(20)
);
insert into dept values
    (10, '财务部', '北京'),
    (20, '研发部', '上海'),
    (30, '销售部', '广州'),
    (40, '运维部', '深圳'),
    (50, '后勤部', '成都');

create table emp(
    empno int,
    ename varchar(20),
    sex varchar(4),
    job varchar(20),
    sal double,
    comm double,
    deptno int
);
insert into emp values
    (1001,'马云','男','经理',20000, 5000, 10),
    (1002,'王明','男','员工', 8000, null, 20),
    (1003,'李梅','女','员工', 7500,  800, 20),
    (1004,'张强','男','总监',30000, null, 30),
    (1005,'赵静','女','经理',18000, 2000, 30),
    (1006,'钱伟','男','员工', 9000, null, 30),
    (1007,'孙丽','女','员工', 7200,  300, 30),
    (1008,'周杰','男','总监',35000, null, 40);

关键：dept.deptno 与 emp.deptno 是关联列（部门编号）。
注意：dept 里有 50 后勤部，但 emp 里没有人在 50 号部门——后面外连接要用到这一点。

说明：本篇 emp 不包含 hiredate 列（第 13 天用过的列）；请以本篇 seed 重建表。

1. 为什么要多表

需求	单表够吗
雇员姓名 + 部门编号	够，emp 里已有 deptno
雇员姓名 + 部门名称	不够，名称在 dept

范式（入门记结论）：一张表尽量只存一类业务数据，用关联列把表连起来，而不是每张表都抄一遍部门名。

2. 等值连接（旧写法）

select 列 from 表1, 表2 where 表1.关联列 = 表2.关联列;

select ename, dname
from emp, dept
where emp.deptno = dept.deptno;

select empno, ename, loc
from emp, dept
where emp.deptno = dept.deptno;

where 在这里同时负责「关联条件」和其他过滤；现代写法更推荐 JOIN ON（下一节）。

3. 内连接 JOIN ... ON（推荐）

select 列 from 表1 join 表2 on 表1.关联列 = 表2.关联列;

select ename, dname
from emp join dept on emp.deptno = dept.deptno;

select empno, ename, loc
from emp join dept on emp.deptno = dept.deptno;

概念	含义
内连接	只保留两边都匹配上的行
on	写关联条件

50 号部门在 dept 里有，但 emp 没人 → 内连接结果里不会出现后勤部。

表别名（强烈建议）

select empno, ename, loc
from emp e join dept d on e.deptno = d.deptno;

写法	说明
emp e	表 emp 的别名 e
e.deptno	明确是 emp 表的列

表名很长时别名能少打很多字，也更清晰。

4. 重名列：ambiguous（易错）

-- 错误示例：deptno 两表都有，不知道用哪个
select empno, ename, deptno, dname
from emp e join dept d on e.deptno = d.deptno
where deptno = 40;

可能报错：

ERROR 1052 (23000): Column 'deptno' in field list is ambiguous

解决：所有同名列都写 表别名.列名：

select empno, ename, e.deptno, dname
from emp e join dept d on e.deptno = d.deptno
where e.deptno = 40;

易错：select 里的重名列、where 里的重名列，都要加前缀。

5. 关联 + 聚合 / 分组

select e.deptno, dname, avg(sal)
from emp e join dept d on e.deptno = d.deptno
group by e.deptno, dname;

select e.deptno, dname, sum(sal), count(*)
from emp e join dept d on e.deptno = d.deptno
group by e.deptno, dname;

select e.deptno, dname, sex, count(*)
from emp e join dept d on e.deptno = d.deptno
where sex = '男'
group by e.deptno, dname, sex;

步骤（概念）	内容
join	先拼成雇员+部门名的大结果
where	行条件（如只看男性）
group by	按部门等分组
select	分组列 + 聚合函数

6. 外连接：一边全都要

左外连接

驱动表 LEFT JOIN 非驱动表 ON 关联条件

• 驱动表（left 左边）：每一行都显示
• 非驱动表：匹配不上的位置 补 NULL

select d.deptno, dname, loc, empno, ename
from dept d left join emp e on e.deptno = d.deptno;

部门	内连接	左外（dept 在左）
50 后勤部（无人）	不出现	出现，雇员列为 NULL

要点：驱动表上的列（如 d.deptno）才有部门编号；非驱动表可能 NULL，不要拿 e.deptno 当部门号显示。

右外连接

select d.deptno, dname, loc, empno, ename
from emp e right join dept d on e.deptno = d.deptno;

• 右侧 dept 是驱动表，50 号部门同样会出现
• 左连接与右连接可互换：换表的左右顺序即可

怎么选

需求	用法
只要能配上的人/事	join（内连接）
要「所有部门」，哪怕没人	dept left join emp
要「所有雇员」，哪怕部门信息缺失	emp left join dept

7. 关联 + 子查询 / 排序（综合）

-- 30 号部门工资最高的人 + 部门名（limit 一行）
select empno, ename, dname, sal
from emp e join dept d on e.deptno = d.deptno
where e.deptno = 30
order by sal desc
limit 1;

-- 子查询取 30 号最高工资（并列可能多行）
select empno, ename, dname, sal
from emp e join dept d on e.deptno = d.deptno
where e.deptno = 30
  and sal = (select max(sal) from emp where deptno = 30);

8. 三张表怎么写（语法）

select ...
from A
join B on A.关联列 = B.关联列
join C on A.关联列 = C.关联列;   -- 或 B 与 C 关联

示例结构（学生–教师–课程，示意）：

-- select sid, sname, tname, cname
-- from student s
-- join teacher t on s.tid = t.tid
-- join course c on s.cid = c.cid;

原则：每多一张表，就多一个 join ... on，把关联条件写全。

9. 笛卡尔积：忘写 ON 的灾难

-- 缺少关联条件：逗号写法 = 笛卡尔积（交叉连接）
select empno, ename, dname from emp, dept;

-- MySQL 8 中 INNER JOIN 必须带 ON/USING；去掉 on 会直接语法报错
-- select empno, ename, dname from emp join dept;   -- ERROR 1064
select empno, ename, dname from emp cross join dept;

表	行数（本篇 seed）
emp	8
dept	5
笛卡尔积	8 × 5 = 40 行（几乎全是错配）

工作要求：多表查询必须写关联条件，并确认 on 两边列的含义正确。
语法提醒：join 不写 on 在 MySQL 8 里会报错；要用笛卡尔积请写 from A, B 或 cross join。

10. 自连接（了解）

同一张表「自己连自己」，需要表内有关联列（如员工的领导编号 mgr）。本篇 seed 未建 mgr 列，仅记语法：

-- select e.empno, e.ename, m.empno as mgr_no, m.ename as mgr_name
-- from emp e left join emp m on e.mgr = m.empno;

没有领导的人用 left join 保留员工行，领导列为 NULL。

11. 完整跟练脚本

use train;

drop table if exists emp;
drop table if exists dept;
create table dept(deptno int, dname varchar(20), loc varchar(20));
insert into dept values
    (10,'财务部','北京'),(20,'研发部','上海'),(30,'销售部','广州'),
    (40,'运维部','深圳'),(50,'后勤部','成都');

create table emp(
    empno int, ename varchar(20), sex varchar(4), job varchar(20),
    sal double, comm double, deptno int
);
insert into emp values
    (1001,'马云','男','经理',20000,5000,10),
    (1002,'王明','男','员工',8000,null,20),
    (1003,'李梅','女','员工',7500,800,20),
    (1004,'张强','男','总监',30000,null,30),
    (1005,'赵静','女','经理',18000,2000,30),
    (1006,'钱伟','男','员工',9000,null,30),
    (1007,'孙丽','女','员工',7200,300,30),
    (1008,'周杰','男','总监',35000,null,40);

select ename, dname from emp, dept where emp.deptno=dept.deptno;
select ename, dname from emp e join dept d on e.deptno=d.deptno;

select empno,ename,e.deptno,dname from emp e join dept d
on e.deptno=d.deptno where e.deptno=40;

select e.deptno,dname,avg(sal) from emp e join dept d
on e.deptno=d.deptno group by e.deptno,dname;

select d.deptno,dname,loc,empno,ename
from dept d left join emp e on e.deptno=d.deptno;

select d.deptno,dname,loc,empno,ename
from emp e right join dept d on e.deptno=d.deptno;

select empno,ename,dname,sal from emp e join dept d
on e.deptno=d.deptno where e.deptno=30 order by sal desc limit 1;

12. 速查表

目标	写法
等值连接	from A, B where A.id=B.id
内连接	from A join B on A.id=B.id
表别名	from emp e join dept d on e.deptno=d.deptno
左外连接	A left join B on ...（A 全显示）
右外连接	A right join B on ...（B 全显示）
重名列	e.deptno、d.deptno 写全
聚合部门	join ... group by 分组列, dname
三表	join B on ... join C on ...
忘写 on	笛卡尔积，行数爆炸

13. 今天的练习清单

• 内连接：每个雇员的姓名 + 部门名 + 城市
• where e.deptno=40 只看 40 号部门；再对比不加 where 的内连接结果（仍无 50 号部门）
• 尝试 select deptno 不带前缀，观察 ambiguous 报错
• 按部门 group by 算平均工资/人数
• dept left join emp，找到没有雇员的部门
• 30 号部门工资最高（limit 与子查询两种写法）
• （选读）用 from emp, dept 或 cross join 看笛卡尔积行数；对比「join 不写 on」会报错

常见错误

易错：重名列未加 表别名.，报 ambiguous。
易错：外连接搞错驱动表，「全员/全部部门」没查全。
易错：left join 后还把 where 非驱动表.列=... 写死，把 NULL 行滤掉，变成像内连接。
易错：忘写 on，产生笛卡尔积。
易错：group by 只写了部分非聚合列，触发第 11 天讲过的行列平衡问题。
易错：关联列类型/含义不一致（如一个用部门号、一个用部门名）导致错配。

小结

1. 关联的条件是关联列相等（入门先练等值）。
2. 内连接只要两边都匹配；左/右外连接保留驱动表全量，缺的补 NULL。
3. 给表起别名，重名列写 别名.列名。
4. JOIN 可以和 where、group by、聚合、子查询、limit 组合。
5. 必须写 on，避免笛卡尔积。
6. 下一篇 约束：主键、唯一、非空、外键等。

---

系列导航：总目录 · 上一篇：子查询 · 下一篇：约束