Oracle数据库从入门到精通 多表查询知识以及范例
视频课程：李兴华 Oracle从入门到精通视频课程
学习者：阳光罗诺
视频来源：51CTO学院
总体内容：
- 多表查询的意义以及基本问题。
- 表的连接查询
- SQL：1999语法标准对多表查询的支持。
- 数据的集合操作。
一、认识多表查询
所谓的多表查询就是同时从多张表中取出数据并且显示的一种操作。语法只是做了一些简单的修改。
语法格式：
1 【③选出所需要的数据列】SELECT [DISTINCT] * 列[别名]，列[别名]，列[别名]······ 2 3 【①确定数据来源（行和列的集合）】FROM 表名称 [别名]，表名称 [别名]，······ 4 5 【②筛选数据行】[WHERE 限定条件] 此时的条件可以是多个语法结构。 6 7 【④数据排序】[ORDER BY 排序字段 [ASC|DESC] 可以设置多个]
于是我们就可以按照这样子的语法结构来实现多表查询。本次将会使用emp和dept两张表格进行多表的查询。于是在查询之前，先做一些准备，在数据库中有一个COUNT()函数，这个函数的主要作用：可以统计出一张数据表中的数据量。
准备查询一：查询dept表中的数据量。（4行记录）
代码示例：
1 select count(*) from dept;
准备查询二：查询emp表中的数据量。（14行记录）
代码示例：
1 select count(*) from emp;
目前两张表的记录加起来，总记录是18行。根据语法格式可以实现多表的查询。
范例：实现emp与dept的多表查询。
发现在每一行emp表中的记录出现了4次，而4次是dept表中的数据量，所以最终产生了14行X4行=56行记录。
之所以出现这样的情况，主要用于数据库的产生原理有关———数学的集合。在这样子的集合操作里面，我们会将这两个集合（数据表）统一查询，作为乘法的形式出现。结果一定会产生积————笛卡儿积。在任何情况下要进行的多表查询中都一定会存在有笛卡儿积的问题。事实上，这些积的产生对用户而言是没有实质上的用处，需要想办法去消除积。
如果要消除积，就必须有关联字段。
很明显，现在emp和dept数据表之中存在有关联字段（大部分情况下，都习惯将关联字段设置为同名）。我们就可以使用关联字段来消除笛卡儿积。
代码示例：
1 SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
可以得出结论：只要是多表查询，这多张表之间就一定要存在有关联关系。没有关联关系的表是不可能多表查询的。
但是就目前而言，代码依然是存在有问题。
此时进行字段访问的时候采用的是“表名称.字段名称”，如果是表名称短的话，一般是没有什么问题的。而如果表名称长了，例如：“yuzhou_yinghexi_diqiu_yazhoudalu_shanghai_ren”，所以在进行多表查询的时候，强烈建议使用别名。
实际上笛卡儿积的存在对于整个程序的影响是相当巨大的，即使现在可以消除显示的笛卡儿积，但是从本质上来讲，永远无法避免笛卡儿积。
例如：在Oracle的样本数据中有sh的大数据用户。
分析一：取得costs表中的记录数。
SELECT COUNT(*) FROM costs;
多表查询操作案例（分析过程）
范例：要求查询出每一个雇员的编号、姓名、职位、基本工资、部门名称、部门位置。
·确定要使用的数据表
- emp表：雇员的编号、姓名、职位、基本工资
- dept表：部门名称、部门位置
·确定已知的关联字段
- 雇员与部门关联：emp.deptno = dept.deptno
第一步：查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。
代码示例：
1 select e.empno,e.ename,e.job,e.sal from emp e;
第二步：查询出每一个雇员对应的部门信息。需要引入dept表（引入表的时候一定要考虑有关联），这两张表直接可以利用dept表deptno字段关联，所以需要利用WHERE子句来消除笛卡儿积。
代码示例：
1 select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno;
以上的操作属于之前基本概念的加强，并且给出了明确的关联字段。可是很多的查询是不会明确给出关联字段的。
范例：要求查询出每一个雇员的编号、姓名、职位、基本工资、工资等级。
- 确定要使用的数据表。
emp表：雇员的编号、姓名、职位、基本工资
salgrade表：工资等级
- 确定已知的关联字段。
雇员与工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal；
第一步：查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。
代码示例：
1 select e.empno,e.ename,e.job,e.sal from emp e;
第二步：增加salgrade表，增加了数据表之后，就需要引入WHERE子句来消除掉笛卡儿积。
代码示例：
1 select e.empno,e.ename,e.job,e.sal,s.grade 2 3 from emp e,salgrade s 4 5 WHERE e.sal BETWEEN s.losal AND s.hisal;
范例：查询每一个雇员的编号、姓名、职位、基本工资、部门名称、工资等级。
确定所需要的数据表：
- emp表：编号、姓名、职位、基本工资
- dept表：部门名称
- salgrade表：工资等级
确定相互关联的字段：
- 雇员与部门：emp.deptno = dept.deptno;
- 雇员与工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
第一步：查询每一个雇员的编号、姓名、职位、基本工资。
代码示例：
1 select e.empno,e.ename,e.job,e.sal 2 3 from emp e;
第二步：加入部门名称，增加一张表就增加阳光条件就消除笛卡儿积。
代码示例;
1 select e.empno,e.ename,e.job,e.sal,d.dname 2 3 from emp e,dept d 4 5 WHERE e.deptno= d.deptno;
第三步：加入工资等级信息，与原始的消除笛卡儿积条件应该同时满足，所以使用AND 来连接。
示例代码：
1 select e.empno,e.ename,e.job,e.sal,d.dname 2 3 from emp e,dept d,salgrade s 4 5 WHERE e.deptno= d.deptno and e.sal BETWEEN s.losal AND s.hisal;
表的连接
实际上对于两张数据表进行多表查询，对于消除笛卡儿积来讲主要是依靠连接模式来处理的，而对于表的连接模式在数据库的定义上，有两种：
l 内连接：在之前都利用WHERE子句消除了笛卡儿积，这就属于内连接。
l 外连接：分为三种：左外连接、右外连接、全外连接。
为了方便更好的观察到连接的区别，现在已经在dept表中提供了一个没有雇员的部门（40部门），同时在emp表中增加一个没有部门的雇员。
代码示例：
观察1：内连接的实现效果。
观察2：使用左（外）连接。希望所有的雇员信息都显示出来。即使没有对应的部门。
代码示例：
1 SELECT e.empno,e.ename,d.deptno,d.dname 2 3 FROM emp e,dept d 4 5 WHERE e.deptno=d.deptno(+);
此时没有部门的雇员信息出现了，也就是说左表的数据全部显示了。
观察3：使用右（外）连接。将所有的部门信息显示出来。
代码示例：
1 SELECT e.empno, e.ename, d.deptno, d.dname 2 3 FROM emp e, dept d 4 5 WHERE e.deptno(+) = d.deptno;
内连接就是所有满足关联关系的数据出现，不满足的不出现，外连接就是指定一张数据表中的内容全部都显示，但是没有对应的其他的表的数据内容位null。
在Oracle中使用了“(+)”来控制连接方式。
左外连接：关联字段1=关联字段2(+)；
右外连接：关联字段1(+)=关联字段2；
大部分情况下一般都只会考虑内连接，但是当你发现所需要的数据不全的时候就可以考虑外连接。、
范例：查询每一个雇员的编号、姓名、职位、领导姓名、领导职位。
确定所需要的数据表。
- emp表（雇员信息）：编号、姓名、职位。
- emp表（领导信息）：领导姓名、领导职位。
确定已知的关联字段。
- 雇员与领导：emp.mgr = memp.empno
第一步：查询出每一个雇员的编号、姓名、职位。
代码示例：
1 SELECT e.empno,e.ename,e.job FROM emp e;
第二步：加入领导信息。需要引入自身关联，而后消除笛卡儿积。
代码示例：
1 SELECT e.empno,e.ename,e.job,m.ename,m.job 2 3 FROM emp e, emp m 4 5 WHERE e.mgr=m.empno;
第三步：发现emp表（雇员信息）数据不完整，因为不满足于等值关联判断，所以要让雇员信息显示完整，则必须使用外连接控制。
SQL：1999语法定义
对于数据表的连接操作，从实际使用来讲各个数据库都是有所支持的，所以对于所有的数据库，进行表连接最好的做法是利用以下的语法可以完成：
语法：
1 SELECT [DISTINCT] * | 列 [别名] ，列 [别名]，······ 2 3 FROM 表1 [别名] 4 5 [CROSS JOIN 表2 [别名]] 6 7 [NATURE JOIN 表2 [别名]] 8 9 [JOIN 表2 [别名] ON （条件）| USING（关联字段）] 10 11 [LEFT | RIGHT | FULL OUTER JOIN ON（条件）表2]；
个人在进行表连接的时候，如果是内连接一定使用等值判断，如果是外连接才会使用LEFT、RIGHT、OUTER等操作。
- 交叉连接：目的是产生笛卡儿积
语法：
1 SELECT [DISTINCT] * | 列 [别名] ，列 [别名]，······ 2 3 FROM 表1 [别名] [CROSS JOIN 表2 [别名]]
范例：实现交叉连接
代码示例：SELECT * FROM emp CROSS JOIN dept;
- 自然连接：利用关联字段自己进行消除笛卡儿积（只要字段名称相同即可）
语法：
1 SELECT [DISTINCT] * | 列 [别名] ，列 [别名]，······ 2 3 FROM 表1 [别名] [NATURE JOIN 表2 [别名]]
范例：实现自然连接。（内连接）
代码示例：
1 SELECT * FROM emp NATURAL JOIN dept;
- 使用自然连接是要求两张表的字段名称相同，但是如果说有一天不相同了呢？或者两张表中中有两组字段都是重名的。所以这种使用ON子句指定关联条件，而利用USING子句设置关联字段。
范例：利用USING子句设置关联字段实现自然连接。
1 SELECT * FROM emp JOIN dept USING(deptno);
范例：利用ON子句设置关联条件。
1 SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno);
外连接
1 SELECT [DISTINCT] * | 列 [别名] ，列 [别名]，······ 2 3 FROM 表1 [别名] [LEFT | RIGHT | FULL OUTER JOIN ON（条件）表2]；
范例：观察左外连接
1 SELECT * FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno);
范例：观察右外连接。
1 SELECT * FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);
范例：全外连接
代码示例：
1 SELECT * FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno);
部分截图：
数据集合操作
数学集合：交集、并集、补集。
每一次查询实际上都会返回数据集合，所以返回的结果可以使用UNION、UNION ALL、MINUS、INTERSECT来实现集合的操作。
1 SELECT [DISTINCT] * 列[别名]，列[别名]，列[别名]······ 2 3 FROM 表名称 [别名]，表名称 [别名]，······ 4 5 [WHERE 限定条件] 此时的条件可以是多个语法结构。 6 7 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个] 8 9 UNION | UNION ALL | INTERSECT | MINUS 10 11 12 13 SELECT [DISTINCT] * 列[别名]，列[别名]，列[别名]······ 14 15 FROM 表名称 [别名]，表名称 [别名]，······ 16 17 [WHERE 限定条件] 此时的条件可以是多个语法结构。 18 19 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个]