十、多表查询

*多表查询的本质就是把多张表合并成一个临时表

1、多表数据合并方案

1. 连接查询合并方案【最常见】

   1. 首先确保两张表之间存在【隶属关系】
   2. 将两张表中的数据行【沿着水平方向进行拼接】
   3. 最终得到一个拥有【完整隶属关系】的新数据行

2. 联合查询合并方案

   1. 不需要两张表之间存在【隶属关系】
   2. 将两张表中的数据行【沿着垂直方向进行堆砌】

---

2、连接查询【水平拼接】

1、join

拼接的临时表行数是两个表的笛卡尔积（A×B）

内联和外联区别：
外键有空值或者不匹配时，外联依然显示左表所有数据，内联不会显示该数据

1、cross join（交叉连接）

交叉连接返回emp、dept的笛卡尔积，即emp行数 × dept行数条数据，一般在不需要任何过滤条件的情况下使用

SELECT * FROM emp,dept;
SELECT * FROM emp CROSS JOIN dept;
-- 两条语句是等效的，第一条语句是隐式的交叉连接，第二条是显示的交叉连接

2、inner join（内连接）

• 内连接也是返回emp和dept的笛卡尔积，但是在内连接后一般都会加on语句附加过滤条件，所以返回的是符合条件的笛卡尔积
• 拼接的新临时表为避免重名，列名都是“表名.列名”格式
• 为了剔除脏数据，使用【连接查询过滤方案】 on 进行过滤

SELECT empno,ename,sal,emp.deptno,dname,loc
FROM emp
INNER JOIN dept -- 运行到这里查出来的数据必然存在脏数据
ON emp.deptno=dept.deptno; -- 用 on 定位合法数据行，剔除脏数据，将合法数据行放到一个新的临时表里

3、natural join （自然连接）

相同的列数值相等连接

***自然连接和内连接区别：**自然连接是特殊的内连接，连接的列只能显示一个列（注：使用的列(重复列)不能有限定词）

-- 格式
select 列...
from 表1
natural outer join 表2-- 栗子
SELECT empno,ename,sal,deptno,dname,loc
FROM emp
NATURAL JOIN dept;

4、outer join（外连接）

1、left outer join （左外连接）

左表为基础，左表信息全部显示，右表信息对应显示

-- 格式
select 列...
from 左表
left outer join 右表
on 连接条件 [ + 查询条件 （先筛选再连接）]
[ where 查询条件 （先连接再筛选） ]-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e	-- 左边的表emp是主表
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;-- 92语法
SELECT
e.*,d.*
FROM emp e,dept d
WHERE e.deptno=d.deptno(+);

2、right outer join （右外连接）

以右表为基础，右表信息全部显示，左表信息对应显示

-- 格式
select 列...
from 左表
right outer join 右表
on 连接条件 [ + 查询条件 （先筛选再连接）]
[ where 查询条件 （先连接再筛选） ]-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e	-- 右边的表dept是主表
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;-- 92语法
SELECT
e.*,d.*
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;

3、full outer join （满外连接）※注意：MySql不支持满外连接！※

左右表的数据都显示

-- 格式
select 列...
from 表1
full outer join 表2 -- Oracle用法
on 连接条件 [ + 查询条件 （先筛选再连接）]
[ where 查询条件 （先连接再筛选） ]-- 栗子
SELECT
e.(all),
d.(all)
FROM emp e
FULL OUTER JOIN dept d -- Oracle用法
ON e.deptno=d.deptno;

2、自连接

自己连接自己

-- 查询员工名字和对应领导的名字(所有人的信息都在这一张表里，包括上下级关系)
-- 把emp当成两张表：worker、leader
-- 查询条件是：worker.mgr=leader.empno
SELECT worker.empno,worker.ename,worker.mgr,leader.ename
FROM emp worker, emp leader
WHERE worker.mgr=leader.empno;

3、非等值连接

between … and …

-- 查询员工工资等级范围（salgrade表）
-- on的条件是个范围用between…and
SELECT
e.empno,e.ename,e.sal,s.grade
FROM emp e
INNER JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

---

3、联合查询【垂直堆砌】

*联合查询的3个特征：

1. 要求参与合并的两个临时表的字段结构必须保持一致【字段个数,字段类型顺序】
2. 将两个临时表数据行沿着垂直方向堆砌到同一个临时表
3. 联合查询生成临时表的字段只能来自于【第一个临时表字段】

1、intersect （交）

对两个结果集进行交集操作，不包括重复行，不排序

SELECT empno,ename,sal,deptno FROM emp
INTERSECT
SELECT empno,ename,sal,deptno FROM emps;

---

2、union （并）

1、union

对两个结果集进行并集操作，不包括重复行，默认进行了union默认的排序

SELECT empno,ename,sal,deptno FROM emp
UNION
SELECT empno,ename,sal,deptno FROM emps;

2、union all

对两个结果集进行并集操作，包括重复行，没有排序

SELECT empno,ename,sal,deptno FROM emp
UNION ALL
SELECT empno,ename,sal,deptno FROM emps;

---

3、minus （差）

对两个结果集进行差集操作，不包括重复行，默认进行minus的默认排序

SELECT empno,ename,sal,deptno FROM emp
MINUS
SELECT empno,ename,sal,deptno FROM emps;

---

4、except （除去）※注意：MySql不支持except！※

EXCEPT运算自动去除重复，如果想保留所有的重复，必须用EXCEPT ALL代替EXCEPT，结果中出现的重复元组数等于两集合出现的重复元组数之差（前提是差是正值）

-- Oracle用法
SELECT empno,ename,sal,deptno FROM emp
EXCEPT
SELECT empno,ename,sal,deptno FROM emps;