【数据库基础|第6篇】多表查询：inner join、left join、right join 详解

前言

上一篇我们学习了聚合函数和分组查询，可以完成员工数量统计、平均工资统计、部门人数统计等需求。

但是上一篇中有一个问题还没有解决：我们按部门统计时，只能查到 dept_id，看不到部门名称。

比如：

select dept_id, count(*) as total
from emp
group by dept_id;

查询结果可能是：

这个结果对后端来说能看懂，但对前端或用户来说不够直观。

我们更希望看到：

这就需要把员工表和部门表关联起来查询。

这一篇我们就来学习 SQL 中非常重要的内容：多表查询。

一、为什么需要多表查询？

在数据库设计中，我们一般不会把所有信息都放在一张表里。

比如员工信息和部门信息，如果全部放在员工表中，可能会这样：

这样看起来简单，但是会有问题。

如果“学工部”改名为“学生工作部”，那么所有属于学工部的员工记录都要修改。

这就容易造成数据冗余和维护困难。

更合理的设计是拆成两张表。

部门表 dept：

员工表 emp：

员工表中只保存部门 id，不重复保存部门名称。

当需要展示员工所属部门名称时，再通过多表查询把两张表关联起来。

二、准备表结构

1. 部门表

create table dept (
id int unsigned primary key auto_increment comment '部门ID',
name varchar(20) not null comment '部门名称',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '部门表';

2. 员工表

create table emp (
id int unsigned primary key auto_increment comment '员工ID',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
gender tinyint unsigned comment '性别：1男，2女',
dept_id int unsigned comment '部门ID',
salary decimal(10, 2) comment '工资',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';

这两张表通过下面这个关系关联：

emp.dept_id = dept.id

员工表中的 dept_id 保存部门表中的 id。

三、准备测试数据

1. 部门数据

insert into dept(name, create_time, update_time)
values
('学工部', now(), now()),
('教研部', now(), now()),
('咨询部', now(), now()),
('就业部', now(), now());

2. 员工数据

insert into emp(username, name, gender, dept_id, salary, entry_date, create_time, update_time)
values
('zhangsan', '张三', 1, 1, 8000.00, '2024-03-01', now(), now()),
('lisi', '李四', 1, 1, 9500.00, '2024-04-10', now(), now()),
('wangwu', '王五', 2, 2, 7200.00, '2024-05-15', now(), now()),
('zhaoliu', '赵六', 1, 2, 12000.00, '2024-06-01', now(), now()),
('xiaohong', '小红', 2, 3, 8800.00, '2024-06-20', now(), now()),
('sunqi', '孙七', 1, null, 7000.00, '2024-07-01', now(), now());

这里故意加了一个员工：

孙七，dept_id 为 null

也就是说，孙七暂时没有分配部门。

同时部门表中有一个“就业部”，但是没有员工属于这个部门。

这样后面讲 inner join、left join、right join 时，效果会更明显。

四、多表查询的核心关系

员工表中有：

dept_id

部门表中有：

id

两张表的关联条件是：

emp.dept_id = dept.id

也就是说：

员工表中的部门 id，要等于部门表中的 id，才能找到员工所属部门。

如果不用 join，也可以先写一个简单的关联查询：

select emp.name, dept.name
from emp, dept
where emp.dept_id = dept.id;

但是这种写法不够清晰，后面项目中更推荐使用显式 join 写法。

五、inner join：内连接

inner join 表示内连接。

它只会查询两张表中能够匹配上的数据。

1. 查询员工姓名和部门名称

select
emp.id,
emp.name as emp_name,
dept.name as dept_name
from emp
inner join dept on emp.dept_id = dept.id;

查询结果类似：

注意，孙七没有出现在结果中。

因为孙七的 dept_id 是 null，无法和部门表中的 id 匹配。

同时，就业部也没有出现在结果中。

因为没有员工属于就业部。

所以可以简单理解：

inner join 只保留两边都能匹配上的数据。

2. inner 可以省略

下面这两种写法效果一样：

select emp.name, dept.name
from emp
inner join dept on emp.dept_id = dept.id;

select emp.name, dept.name
from emp
join dept on emp.dept_id = dept.id;

join 默认就是 inner join。

不过初学阶段建议先写完整：

inner join

这样更容易理解。

六、使用表别名简化 SQL

上面的 SQL 中反复写：

emp.name
dept.name

如果表名很长，SQL 会显得很啰嗦。

可以使用表别名：

select
e.id,
e.name as emp_name,
d.name as dept_name
from emp e
inner join dept d on e.dept_id = d.id;

这里：

emp e

表示给 emp 表起别名为 e。

dept d

表示给 dept 表起别名为 d。

后面就可以用 e 和 d 代替完整表名。

在实际开发中，多表查询基本都会使用表别名。

七、字段名冲突问题

员工表和部门表中都有 name 字段：

emp.name
dept.name

如果直接写：

select name
from emp e
inner join dept d on e.dept_id = d.id;

数据库会不知道你要查的是员工姓名，还是部门名称。

所以多表查询时，如果字段名可能重复，一定要写表别名：

select
e.name as emp_name,
d.name as dept_name
from emp e
inner join dept d on e.dept_id = d.id;

这里还使用了字段别名：

as emp_name
as dept_name

让查询结果更清楚。

八、left join：左外连接

left join 表示左外连接。

它会保留左表的全部数据，右表能匹配上就显示右表数据，匹配不上就显示 null。

1. 查询所有员工及其部门名称

select
e.id,
e.name as emp_name,
d.name as dept_name
from emp e
left join dept d on e.dept_id = d.id;

查询结果类似：

这里孙七也被查询出来了。

因为 emp 是左表，left join 会保留左表所有员工。

孙七没有部门，所以部门名称显示为 null。

2. left join 的理解方式

from emp e
left join dept d on e.dept_id = d.id;

可以理解为：

以员工表为主，查出所有员工；如果员工能匹配到部门，就带出部门信息；如果匹配不到，部门信息为 null。

所以如果业务需求是“查询所有员工，不管有没有部门”，就应该使用 left join。

九、right join：右外连接

right join 表示右外连接。

它会保留右表的全部数据，左表能匹配上就显示左表数据，匹配不上就显示 null。

1. 查询所有部门及其员工

select
e.name as emp_name,
d.name as dept_name
from emp e
right join dept d on e.dept_id = d.id;

查询结果类似：

这里就业部也被查询出来了。

因为 dept 是右表，right join 会保留右表所有部门。

就业部没有员工，所以员工姓名显示为 null。

2. right join 可以用 left join 改写

实际开发中，right join 用得相对少一些，因为大多数情况下可以调整表顺序，用 left join 表达。

比如上面的 SQL：

select
e.name as emp_name,
d.name as dept_name
from emp e
right join dept d on e.dept_id = d.id;

可以改写成：

select
e.name as emp_name,
d.name as dept_name
from dept d
left join emp e on e.dept_id = d.id;

这样也是以部门表为主，查询所有部门及其员工。

所以实际开发中，很多人更习惯使用 left join。

十、inner join、left join、right join 对比

可以用一张表简单总结：

结合员工表和部门表，可以这样理解：

所以关键不是死记连接方式，而是要先想清楚：

这次查询要以哪张表为主？

十一、多表条件查询

多表查询也可以配合 where。

1. 查询学工部的员工

select
e.id,
e.name as emp_name,
d.name as dept_name
from emp e
inner join dept d on e.dept_id = d.id
where d.name = '学工部';

这里先关联员工表和部门表，再通过部门名称筛选。

2. 查询工资大于 9000 的员工及部门

select
e.id,
e.name as emp_name,
e.salary,
d.name as dept_name
from emp e
left join dept d on e.dept_id = d.id
where e.salary > 9000;

3. 查询姓名中包含“张”的员工及部门

select
e.id,
e.name as emp_name,
d.name as dept_name
from emp e
left join dept d on e.dept_id = d.id
where e.name like '%张%';

可以看到，多表查询和普通条件查询并不冲突。

只是在 from 后面先把表关联起来，再继续写 where 条件。

十二、多表排序和分页

多表查询同样可以使用 order by 和 limit。

比如查询员工列表，带部门名称，并按更新时间倒序分页：

select
e.id,
e.username,
e.name as emp_name,
e.gender,
e.salary,
d.name as dept_name,
e.update_time
from emp e
left join dept d on e.dept_id = d.id
order by e.update_time desc
limit 0, 10;

这类 SQL 在后台管理系统中非常常见。

员工列表页面往往不仅要显示员工自己的字段，还要显示部门名称。

如果只查员工表，拿到的是：

dept_id

如果要显示：

部门名称

就必须关联部门表。

十三、分组统计结合多表查询

上一篇我们写过按部门统计员工数量：

select dept_id, count(*) as total
from emp
group by dept_id;

但是这个 SQL 只能查出部门 id。

现在我们可以结合部门表，查出部门名称。

select
d.name as dept_name,
count(e.id) as total
from dept d
left join emp e on e.dept_id = d.id
group by d.id, d.name;

查询结果类似：

这里使用 dept 作为左表，是为了保留所有部门。

即使就业部没有员工，也会显示出来，统计数量为 0。

为什么用 count(e.id)？

这里不建议写：

count(*)

因为 left join 后，即使部门没有员工，也可能会产生一行部门数据。

如果写 count(*)，没有员工的部门可能统计成 1。

所以这里写：

count(e.id)

表示只统计员工 id 不为空的记录。

这是一个很重要的小细节。

十四、对应到 Spring Boot + MyBatis 项目

假设我们要实现员工列表查询，返回员工信息和部门名称。

1. VO 类设计

因为返回结果中多了部门名称，和 Emp 实体类不完全一样，所以可以定义一个 VO。

public class EmpVO {
private Integer id;
private String username;
private String name;
private Integer gender;
private BigDecimal salary;
private String deptName;
private LocalDateTime updateTime;

// getter、setter 省略
}

2. Controller 层

@GetMapping
public Result list(String name, Integer gender) {
List<EmpVO> list = empService.list(name, gender);
return Result.success(list);
}

3. Service 层

@Override
public List<EmpVO> list(String name, Integer gender) {
return empMapper.list(name, gender);
}

4. Mapper XML

<select id="list" resultType="com.example.vo.EmpVO">
select
e.id,
e.username,
e.name,
e.gender,
e.salary,
d.name as deptName,
e.update_time as updateTime
from emp e
left join dept d on e.dept_id = d.id
<where>
<if test="name != null and name != ''">
e.name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and e.gender = #{gender}
</if>
</where>
order by e.update_time desc
</select>

文字说明

这里使用了 left join，因为员工即使暂时没有部门，也应该能在员工列表中展示。

查询结果中：

d.name as deptName

表示把部门表中的 name 字段作为 deptName 返回。

这样 MyBatis 就可以把它映射到 Java 中的：

private String deptName;

这也是为什么多表查询时经常要使用字段别名。

十五、常见问题总结

1. 多表查询时为什么要写表别名？

因为多表查询中字段可能重名。

比如员工表和部门表都有 name 字段。

使用别名可以让 SQL 更清楚：

e.name as emp_name
d.name as dept_name

2. inner join 和 left join 怎么选？

如果只想要两边都匹配的数据，用 inner join。

如果想保留主表所有数据，用 left join。

比如查询员工列表，通常以员工表为主：

from emp e
left join dept d on e.dept_id = d.id

3. 为什么有时候部门名称是 null？

因为员工的 dept_id 没有匹配到部门表中的 id。

可能原因有：

• 员工没有分配部门
• dept_id 为空
• 部门数据被删除了
• 数据本身不一致

4. 多表查询结果用实体类接收还是 VO 接收？

如果查询结果和数据库表完全一致，可以用实体类。

如果查询结果包含额外字段，比如 deptName，建议使用 VO。

比如：

EmpVO
DeptCountVO
StudentClazzVO

这样结构更清楚。

5. group by 多表统计时为什么用 count(e.id)？

因为 left join 场景下，使用 count(*) 可能会把没有员工的部门也统计成 1。

使用：

count(e.id)

只统计员工 id 不为空的数据，更符合“员工数量”的含义。

十六、实际开发建议

写多表查询时，可以注意下面几点：

十七、总结

这一篇主要学习了 SQL 中的多表查询。

多表查询的核心是找到两张表之间的关联字段，比如员工表中的 dept_id 和部门表中的 id。

inner join 只返回两张表都能匹配上的数据；left join 会保留左表全部数据；right join 会保留右表全部数据。

实际开发中，最常用的是 left join。因为很多列表页面都要以某一张主表为核心，即使关联表没有数据，也希望主表数据能正常展示。

这一篇内容在 Java 后端项目中非常重要。比如员工关联部门、学生关联班级、订单关联用户、商品关联分类，本质上都是多表查询。

下一篇我们继续学习数据库中非常关键的内容：索引。也就是为什么加了索引之后，查询速度会变快。