多表查询（图解join）

1 笛卡尔积

1.1 概念解释

• 两张表的行数据 排列组合 的结果
  
• 准备sql语句

# 查询员工信息
select * from tb_emp;

# 查询部门信息
select * from tb_dept;

# 查询排列组合结果
select e.*, d.* from tb_dept d, tb_emp e;

1.2 问题

• 里面有很多错误的数据需要过滤.

2 内连接查询

2.1 问题

• 查询 员工信息和对应的部门信息.
  

2.2 解决

2.2.1 方案1:

select e.*, d.* from tb_dept d, tb_emp e
where e.dept_id=d.did;

2.2.2 方案2:

select e.*,d.* from tb_emp e
inner join tb_dept d
on e.dept_id=d.did;

2.3 小结

• 内连接查询两张表的公共部分, 就是能够关联上数据

3 外连接查询

• 准备数据

insert into tb_emp values(5, '长孙无忌', 30000, null, 3);
insert into tb_emp values(6, '太平公主', 30000, null, 4);

3.1 问题

• 因为 长孙无忌退休了, 太平公主年龄太小, 没有在部门任职, 所以使用内连接查询无法显示两个人信息
• 能否查询时 将 长孙无忌 和 太平公主 查询出来且显示.
  

3.2 解决

3.2.1 左外连接

select e.*, d.* from tb_emp e left join tb_dept d
on e.dept_id=d.did;

3.3.2 右外连接

select e.*, d.* 
from tb_dept d right join tb_emp e
on e.dept_id=d.did;

3.3 小结

• 1 外连接以某表为主,会将关联上的数据 和 没有关联上的数据 都查询出来
• 2 左外连接和右外连接 可以相互转化

4 自连接查询

4.1 问题

• 在员工表, 查询员工的姓名和上级的姓名
  

4.2 解决

select yg.ename 员工姓名,sj.ename 上级姓名
from tb_emp yg left join tb_emp sj
on yg.manager=sj.eid;

5 子查询

• 子查询：一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

5.1 问题

需求1 查询归属于研发部的员工信息

# 方案一 分解式
# 1.1 在部门表 根据部门名称查询研发部对应的编号
select did from tb_dept
where dname='研发部'

# 1.2 在员工表 根据研发部的编号查询所属的员工信息
select * from tb_emp
where dept_id = 1

# 方案二 合并式
select * from tb_emp
where dept_id = (select did from tb_dept
                 where dname='研发部')

需求2 查询归属于研发部的员工信息 且 显示部门信息

# 方案一
select e.*, d.* from tb_emp e inner join tb_dept d
on e.dept_id = d.did
where d.dname='研发部';

# 方案二
# 2.1 查询研发部信息
select * from tb_dept where dname='研发部'

# 2.2 将2.1的结果当做临时表处理
select e.*, d.*
from tb_emp e inner join (select * from tb_dept where dname='研发部') d
on e.dept_id = d.did;

需求3 查询归属于研发部和行政部的员工信息

# 3.1 查询研发部和行政部的部门编号
select did from tb_dept
where dname in ('研发部', '行政部');

# 3.2 将3.1的结果作为参数值使用
select * from tb_emp
where dept_id in (select did from tb_dept
                  where dname in ('研发部', '行政部'));

需求4 查询归属于研发部和行政部的员工信息 且 显示部门信息

# 方案一
select e.*, d.* from tb_emp e inner join tb_dept d
on e.dept_id = d.did
where d.dname in ('研发部', '行政部');

# 方案二
# 4.1 查询研发部和行政部信息
select * from tb_dept where dname in ('研发部', '行政部')

# 4.2 将2.1的结果当做临时表处理
select e.*, d.*
from tb_emp e inner join (select * from tb_dept where dname in ('研发部', '行政部')) d
on e.dept_id = d.did;

5.2 小结

• 子查询 就是将查询的结果 当做 临时表或一个值或一列值来处理.
• 注意 in 要求只能是 一列值, 切忌切忌