MySQL：多表查询

一、前言

通常情况下，一个数据库中会存在多个表，这些表之间通过某些共同的字段（主键和外键）建立了关联关系，而多表查询是指在数据库中从两个或更多个相关联的表中获取所需数据的操作。

---

二、准备数据

-- 部门表
create table tb_dept (
    id int auto_increment primary key comment '部门ID',
    name varchar(20) comment '部门名'
) comment '部门表';
insert into tb_dept (name) values ('人事部'), ('财政部'), ('就业部');

-- 员工表
create table tb_emp (
    id int auto_increment primary key comment '员工ID',
    name varchar(50) comment '姓名',
    dept_id int comment '部门ID',
    constraint fk_dept_id foreign key (dept_id) references tb_dept(id),
    create_date date comment '入职时间'
) comment '员工表';
insert into tb_emp (name, dept_id, create_date ) values ('张三', 1, now()), ('李四', 2, now()), ('王五', 1, now()), ('赵六', null, '2003-08-03');

-- 多表查询
select * from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;

运行结果：

---

三、内连接

查询左表和右表的交集数据。

3.1 语法

-- 隐式内连接
select 字段列表 from 表1, 表2 where 条件 ...;

-- 显示内连接，中括号的内容可以不写
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

---

3.2 测试

-- A. 查询员工的姓名以及所属的部门 (隐式内连接)
select tb_emp.name, tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;

-- B. 查询员工的姓名以及所属的部门 (显示内连接)
select tb_emp.name, tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id

运行结果：

---

四、外连接

外连接分为左外连接和有外连接，语法如下：

-- 左外连接，中括号的内容可以不写
select 字段列表 from 表1 left [outer] join 表2 连接条件 ...;

-- 右外连接，中括号的内容可以不写
select 字段列表 from 表1 right [outer] join 表2 连接条件 ...;

4.1 左外连接

查询左表，以及左表和右表交集部分

-- A. 查询所有员工的姓名以及所属部门
select tb_emp.name, tb_dept.name 
from tb_emp left outer join tb_dept 
on tb_emp.dept_id = tb_dept.id;

运行结果：

---

4.2 右外连接

查询右表，以及右表和左表交集部分。需要注意的是右外连接可以被左外连接代替，只要调换一下表的位置就可以，所以一般我们会使用左外连接。

-- A. 查询部门表所有的部门名称以及归属的员工
select tb_dept.name, tb_emp.name 
from tb_emp right outer join tb_dept 
on tb_dept.id = tb_emp.dept_id;

运行结果：

---

五、子查询

子查询是一个嵌套在另一个查询（主查询）中的查询。子查询可以用于实现复杂的查询逻辑和数据筛选。子查询的外部可以是 insert / update / delete / select 的任何一个，最常见的是 select 。

5.1 标量子查询

子查询的结果会返回单个值。

-- A. 查询人事部的所有员工信息
    
-- 第一步. 查询人事部的部门ID
-- select id from tb_dept where name = '人事部';

-- 第二步. 根据查询出来的ID，在员工表的部门ID中查找，如果相等，代表该员工归属该部门
select * from tb_emp where tb_emp.dept_id = (select id from tb_dept where name = '人事部');

运行结果：

---

5.2 列子查询

子查询会返回一列值。

-- A. 查询人事部和财政部的所有员工信息

-- 1. 查询人事部和财政部的部门ID
-- select id from tb_dept where name = '人事部' or name = '财政部';

-- 2. 根据查询出来的ID列数据，在员工表的部门ID中查找，如果相等，代表该员工归属该部门
select * from tb_emp 
where tb_emp.dept_id in (select id from tb_dept where name = '人事部' or name = '财政部');

运行结果：

---

5.3 行子查询

子查询的结果会返回一行数据。

-- A. 查询与张三入职日期以及部门都相同的员工信息;

-- 第一步. 查询张三的入职日期和部门
-- select create_date, dept_id from tb_emp where name = '张三';

-- 第二步. 根据查询出来的行数据，在员工表中查询对应的数据，如果相等，则匹配到入职日期以及部门都相同的员工
select * from tb_emp 
where (create_date, dept_id) = (select create_date, dept_id from tb_emp where name = '张三');

运行结果：

---

5.4 表子查询

子查询的结果会返回一个多行多列的数据，可以作为一个临时表在主查询中使用。

-- A. 查询入职时间在 2003-08-03 之后的员工信息以及部门名称

-- 第一步. 查询入职时间在 2003-08-03 之后的员工
-- select * from tb_emp where create_date > '2003-08-03';

-- 第二步. 根据这些信息，查询到部门信息
select e.*, d.name 
from (select * from tb_emp where create_date > '2003-08-03') as `e`, tb_dept as `d` 
where e.dept_id = d.id;

运行结果：

---