第8章：集合运算

集合运算：是用来把两个或多个查询的结果集做并、交、差的集合运算，包含集合运算的查询称为复合查询。

注意：
1.多个集合运算符，没有括号，那么从左到右的顺序计算

2.注：INTERSECT (相交) 和 MINUS (相减) 运算不是 ANSI SQL-99 兼容的，他们是 Oracle 特定的。

===============
联合查询的特点：

1.返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列升序排序。
2.默认安照第一列升序排序
3.两个集合列数和数据类型对应，列的名字可以不一样

例子：
select empno,ename from emp
union
select deptno,dname from dept;

为演示案例，复制如下语句，并执行：

1.创建员工历史岗位表 emp_jobhistory：
CREATE TABLE emp_jobhistory(
id NUMBER,–流水号
empno NUMBER,–员工编号
job VARCHAR2(9),–岗位
begindate DATE,–开始日期
sal Number(7,2)–在该岗位时工资
)

2.插入如下数据：
INSERT INTO emp_jobhistory VALUES(1,7839,‘TRAINEE’,‘17-11月-81’,500);
INSERT INTO emp_jobhistory VALUES(2,7839,‘SALESMAN’,‘17-2月-82’,1800);
INSERT INTO emp_jobhistory VALUES(3,7839,‘CLERK’,‘17-2月-83’,2000);
INSERT INTO emp_jobhistory VALUES(4,7839,‘SALESMAN’,‘17-2月-85’,1800);
INSERT INTO emp_jobhistory VALUES(5,7839, ‘MANAGER’,‘17-2月-87’,3000);
select *from emp_jobhistory

注：此页内容不要求学生掌握，只是方便后面案例及练习。
思考：查询编号为7839的员工当前工资、岗位及历史工资、岗位？

select empno,sal,job from emp where empno=7839
union
select empno,sal,job from emp_jobhistory where empno=7839;

======================================================
完全联合运算(union all性能好)
返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排序

返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排序 。

使用原则
多个被联合的查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
如果多个查询结果都有NULL值，不被去掉。
每个查询不能包含自己的Order by子句，只能在联合之后使用Order by子句。

注意：使用UNION ALL会比UNION的速度快，因为省去了去掉重复记录和排序的时间。
select empno,sal,job from emp where empno=7839
union all
select empno,sal,job from emp_jobhistory where empno=7839;–没有去重，没有排序

–课后第五题
select deptno,dname,null ,’’ from dept
union
select deptno,’’,empno,ename from emp

===========================================
相交运算

返回多个查询结果集的公有行。
使用原则
多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
相交运算不忽略空值。

例：查询哪些员工做过岗位调动？
SELECT empno FROM emp INTERSECT SELECT empno FROM emp_jobhistory;—求交集，不是减，都有7839

==============================================
相减运算

返回第一个查询中存在 而第二个查询中不存在的行记录。

使用原则
多个查询语句所选择的列数和列的数据类型必须一致，列的名字不必相同。
相减运算不忽略空值。

例：查询哪些员工没做过岗位调动？

SELECT empno FROM emp
MINUS
SELECT empno FROM emp_jobhistory;—求差

--------------------------练习1------------------------------------------------------------------
1.分别使用联合运算及完全联合运算完成，按照时间升序顺序，查询员工7839的工作岗位列表。
SELECT job ,NULL begindate FROM emp WHERE empno=7839
UNION
SELECT job,begindate FROM emp_jobhistory WHERE empno =7839
ORDER BY begindate

SELECT job ,NULL begindate FROM emp WHERE empno=7839
UNION ALL
SELECT job,begindate FROM emp_jobhistory WHERE empno =7839
ORDER BY begindate

2.使用多表连接，查询每个部门的部门编号，部门人数，没有人数的部门显示0。
select d.deptno,count(e.deptno)
from emp e,dept d
where e.deptno(+)=d.deptno ----------注意：这整条oracle语句的意思：1.在部门表里面拿取deptno去emp表里面找deptno里面是否有员工
group by d.deptno — 有员工的话就计数，没有的话就不计数为0

----------------错误示范1-----------------------------------------
select d.deptno,count(*)
from emp e,dept d
where e.deptno(+)=d.deptno ----------注意：这条语句，1.在dept表里面拿取deptno去找数据，有数据就计数，所以即使没有员工的部门也计数2.然后也去emp表里面计数
group by d.deptno

select *from dept

----------------错误示范2-----------------------------------------
select e.deptno,count(*)
from emp e,dept d
where e.deptno(+)=d.deptno ----------注意：这条语句根本用不到dept表，查出来的就只是emp表的数据，因为emp表里面的部门编号不包括dept里面的编号（除了两表共有的部门编号）
group by e.deptno — 所以要用到第一种做法，要查询大的表，就是dept表里面编号包括了emp部门的标号，就是数学里面的包含关系

select *from dept

3.使用联合运算，查询每个部门的部门编号，部门人数，没有人数的部门显示0。

select deptno,count(*)
from emp
where deptno is not null
group by deptno
union
select deptno,0
from dept
where deptno not in(select deptno from emp where deptno is not null)

--------------------------课后作业------------------------------------------------------------------
1.用集合运算，列出不包含job为SALESMAN 的部门的部门号。

select deptno from dept
minus
select deptno from emp where job <> ‘SALESMAN’;
select *from emp
2.写一个联合查询，列出下面的信息：EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。DEPT表中的所有部门编号和部门名称，不管他们是否有员工。
select deptno,dname ,’‘雇员的名字 from dept
union
select deptno,’’,ename from emp
3.用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名、职位，不排除重复结果。

select deptno,ename,job from emp where job=‘SALESMAN’
union all
select deptno,ename,job from emp where deptno=10

4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。
select deptno,ename,(select dname from dept where deptno=10)部门名称 from emp where deptno=10
union all
select deptno,ename,(select dname from dept where deptno=20)部门名称 from emp e where deptno=20