多表查询

1. 笛卡尔积(交叉连接)

笛卡尔积是关系型数据库，进行多表查询的基础
A{a,b,c}
B{d,e,f,g}
A x B = {ad,ae,af,ag,bd,be,bf,bg,cd,ce,cf,cg}

查询emp表与dept表笛卡尔积

select *
from emp cross join dept

cross join 又被称为 交叉连接
使用emp表的每行数据与dept表的每行数据分别组合

image

2. 等值连接和不等值连接

2.1 等值连接

可以利用where条件对两张表的笛卡尔积进行筛选
只保留两列deptno相等的数据

示例1： 查询月薪高于3000的员工的姓名，月薪和所在部门名称

select ename, sal, dname
from emp cross join dept
where emp.deptno = dept.deptno and sal > 3000

利用专门的join...on...语法完成等值筛选

select ename, sal, dname
from emp join dept on(emp.deptno = dept.deptno)
where sal > 3000

为表起别名
示例2： 查询员工姓名，职位，月薪，所在部门编号，所在部门名称

select e.ename, e.job, e.sal, d.dname
from emp e join dept d on(e.deptno = d.deptno)

更多张表进行关联

image image image image

示例3：查询员工姓名，部门名称，工作地点，地址

select e.ename, d.dname, d.loc, l.adress
from emp e 
    join dept d on (e.deptno = d.deptno)
    join location l on (d.loc = l.locid)

---

就算查询结果中没有dept表的内容，也得需要dept进行关联才能将emp和location关联上
示例4：查询员工姓名，地址

select e.ename,  l.adress
from emp e 
    join dept d on (e.deptno = d.deptno)
    join location l on (d.loc = l.locid)

2.2 不等值连接

筛选的就是连接条件不相等的那部分数据

select ename, sal, dname
from emp join dept on(emp.deptno != dept.deptno)
where sal > 3000

3. 外连接（重点和难点）

emp表中有人没有部门，所以笛卡尔积没有等值关联的项

image image

3.1 左外连接（重要）

示例5：查询所有员工的员工姓名和所在部门名称(包括没有部门的员工)

select * 
from emp e left join dept d on(e.deptno = d.deptno)

select *
from dept d right join emp e on(d.deptno = e.deptno)

image

3.2 右外连接

示例6：查询所有员工的员工姓名和所在部门名称(包括没有员工的部门)

select * 
from emp e right join dept d on(e.deptno = d.deptno)

image

3.3 全外连接

MySQL不支持全外连接的标准写法，在Oracle中是支持的

select * 
from emp e full join dept d on(e.deptno = d.deptno)

会将左连接和右连接的数据全部查询出来

3.4 更多张表时外连接的传导

示例7： 查询员工的员工信息，部门信息和地点信息（含没有部门的员工）

select * 
from emp e 
   left join dept d on(e.deptno = d.deptno)
   cross join location l 

image

select * 
from emp e 
   left join dept d on(e.deptno = d.deptno)
   left join location l on(d.loc = l.locid)

image

4. 自连接

一张表自己的列A去关联自己列B

示例8：查询员工编号，员工姓名，上司的编号和上司的姓名

select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1 
left join emp e2 on(e1.mgr = e2.empno);

分析：

• e1可以理解为员工表 mgr - 上司编号
• e2可以理解为上司表 empno - 上司的员工编号
• 员工KING没有上司 通过left join 将他显示出来