第八章: 集合运算
• 集合运算:是用来把两个或多个查询的结果集做并、交、差的集合运算,包含集合运算的查询称为复合查询。
联合UNION :相同的只保留一份
完全联合UNION ALL: 相同的保留两份
相交INTERSECT: 取两个的交集
相减:minus 共同有的被剪掉了
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)
• 联合(UNION)运算 A B
–返回由任一查询结果集包含的行,并且去除重复行,并且按照查询结果集的第一列升序排序。
–使用原则
• 多个被联合的查询语句所选择的列数和列的数据类型必须一致,列的名字不必相同。
•如果多个查询结果都有NULL值,整个结果中只包含一个NULL值。
•每个查询不能包含自己的Order by子句,只能在联合之后使用Order by子句。
思考:查询编号为7839的员工当前工资、岗位
及历史工资、岗位?
SELECT sal,job FROM emp WHERE empno=7839
UNION
SELECT sal,job FROM emp_jobhistory WHERE empno=7839
不管有没有关系,都可以连,只要保证列的个数和数据类型相同就行,可以连许多个
完全联合运算
• 完全联合 (UNION ALL) 运算 A B
–返回由任一查询结果集包含的行,并且包含重复行,默认情况下不对结果集进行排序 。
–使用原则
• 多个被联合的查询语句所选择的列数和列的数据类型必须一致,列的名字不必相同。 (查询结果以第一个列名为准)
•如果多个查询结果都有NULL值,不被去掉。
•每个查询不能包含自己的Order by子句,只能在联合之后使用Order by子句。
–注意:使用UNION ALL会比UNION的速度快,因为省去了去掉重复记录和排序的时间。
完全联合运算
例:查询编号为7839的员工当前工资、岗位及历
史工资、岗位,重复行记录不要求被去掉。
SELECT sal,job FROM emp WHERE empno= 7839
UNION ALL
SELECT sal,job FROM emp_jobhistory WHERE empno =7839;
练习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.dname,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.dname (现在的表里EMP里边也有dname了,所以也要标注)
• 3.使用联合运算,查询每个部门的部门编号,部门人数,没有人数的部门显示0。
SELECT d.dname,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname
UNION
SELECT dname,0
FROM dept
WHERE deptno NOT IN(SELECT deptno FROM emp
WHERE deptno IS NOT NULL)
• 4.使用联合运算,查询10号部门及20号部门的员工姓名,部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno=10
UNION
SELECT ename,deptno
FROM emp
WHERE deptno=20
5. 使用集合运算,输出如下效果?
部门 工作地点 员工姓名 入职日期 、
10 NEW YORK
10 CLARK 1981/6/9
10 KING 1981/11/17
10 MILLER 1982/1/23
20 DALLAS
20 ADAMS 1987/5/23
20 FORD 1981/12/3
20 JONES 1981/4/2
20 SCOTT 1987/4/19
20 SMITH 1980/12/17
30 CHICAGO
30 ALLEN 1981/2/20
30 BLAKE 1981/5/1
30 JAMES 1981/12/3
30 MARTIN 1981/9/28
30 TURNER 1981/9/8
30 WARD 1981/2/22
40 BOSTON
SELECT deptno ,loc,NULL,NULL
FROM dept
UNION
SELECT deptno,NULL ,ename,hiredate FROM emp
相交运算
• 相交运算 A B
–返回多个查询结果集的公有行。
–使用原则
• 多个查询语句所选择的列数和列的数据类型必须一致,列的 名字不必相同。
•相交运算不忽略空值。
• 例:查询哪些员工做过岗位调动?
SELECT empno FROM emp
INTERSECT
SELECT empno FROM emp_jobhistory;
• 相减运算 A B
– 返回第一个查询中存在而第二个查询中不存在的行记录。
–使用原则
•多个查询语句所选择的列数和列的数据类型必须一 致,列的名字不必相同。
•相减运算不忽略空值。
• 例:查询哪些员工没做过岗位调动?
SELECT empno FROM emp
MINUS
SELECT empno FROM emp_jobhistory;
课后作业
1.用集合运算,列出不包含job为SALESMAN 的部门的部门号。
2.SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp WHERE job='SALESMAN'
2.写一个联合查询,列出下面的信息:EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。
DEPT表中的所有部门编号和部门名称,不管他们是否有员工。
SELECT ename ,deptno,NULL FROM emp
UNION
SELECT NULL,deptno,dname FROM dept
ORDER BY deptno,ename DESC
3.用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名、职位,不排除重复结果。
SELECT empno,ename,job FROM emp WHERE job='SALESMAN'
UNION ALL
SELECT empno,ename,job FROM emp WHERE deptno =10
4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。
SELECT empno,ename,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND e.deptno=10
UNION ALL
SELECT empno,ename,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND e.deptno= 20
• 集合运算:是用来把两个或多个查询的结果集做并、交、差的集合运算,包含集合运算的查询称为复合查询。
联合UNION :相同的只保留一份
完全联合UNION ALL: 相同的保留两份
相交INTERSECT: 取两个的交集
相减:minus 共同有的被剪掉了
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)
• 联合(UNION)运算 A B
–返回由任一查询结果集包含的行,并且去除重复行,并且按照查询结果集的第一列升序排序。
–使用原则
• 多个被联合的查询语句所选择的列数和列的数据类型必须一致,列的名字不必相同。
•如果多个查询结果都有NULL值,整个结果中只包含一个NULL值。
•每个查询不能包含自己的Order by子句,只能在联合之后使用Order by子句。
思考:查询编号为7839的员工当前工资、岗位
及历史工资、岗位?
SELECT sal,job FROM emp WHERE empno=7839
UNION
SELECT sal,job FROM emp_jobhistory WHERE empno=7839
不管有没有关系,都可以连,只要保证列的个数和数据类型相同就行,可以连许多个
完全联合运算
• 完全联合 (UNION ALL) 运算 A B
–返回由任一查询结果集包含的行,并且包含重复行,默认情况下不对结果集进行排序 。
–使用原则
• 多个被联合的查询语句所选择的列数和列的数据类型必须一致,列的名字不必相同。 (查询结果以第一个列名为准)
•如果多个查询结果都有NULL值,不被去掉。
•每个查询不能包含自己的Order by子句,只能在联合之后使用Order by子句。
–注意:使用UNION ALL会比UNION的速度快,因为省去了去掉重复记录和排序的时间。
完全联合运算
例:查询编号为7839的员工当前工资、岗位及历
史工资、岗位,重复行记录不要求被去掉。
SELECT sal,job FROM emp WHERE empno= 7839
UNION ALL
SELECT sal,job FROM emp_jobhistory WHERE empno =7839;
练习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.dname,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.dname (现在的表里EMP里边也有dname了,所以也要标注)
• 3.使用联合运算,查询每个部门的部门编号,部门人数,没有人数的部门显示0。
SELECT d.dname,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname
UNION
SELECT dname,0
FROM dept
WHERE deptno NOT IN(SELECT deptno FROM emp
WHERE deptno IS NOT NULL)
• 4.使用联合运算,查询10号部门及20号部门的员工姓名,部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno=10
UNION
SELECT ename,deptno
FROM emp
WHERE deptno=20
5. 使用集合运算,输出如下效果?
部门 工作地点 员工姓名 入职日期 、
10 NEW YORK
10 CLARK 1981/6/9
10 KING 1981/11/17
10 MILLER 1982/1/23
20 DALLAS
20 ADAMS 1987/5/23
20 FORD 1981/12/3
20 JONES 1981/4/2
20 SCOTT 1987/4/19
20 SMITH 1980/12/17
30 CHICAGO
30 ALLEN 1981/2/20
30 BLAKE 1981/5/1
30 JAMES 1981/12/3
30 MARTIN 1981/9/28
30 TURNER 1981/9/8
30 WARD 1981/2/22
40 BOSTON
SELECT deptno ,loc,NULL,NULL
FROM dept
UNION
SELECT deptno,NULL ,ename,hiredate FROM emp
相交运算
• 相交运算 A B
–返回多个查询结果集的公有行。
–使用原则
• 多个查询语句所选择的列数和列的数据类型必须一致,列的 名字不必相同。
•相交运算不忽略空值。
• 例:查询哪些员工做过岗位调动?
SELECT empno FROM emp
INTERSECT
SELECT empno FROM emp_jobhistory;
• 相减运算 A B
– 返回第一个查询中存在而第二个查询中不存在的行记录。
–使用原则
•多个查询语句所选择的列数和列的数据类型必须一 致,列的名字不必相同。
•相减运算不忽略空值。
• 例:查询哪些员工没做过岗位调动?
SELECT empno FROM emp
MINUS
SELECT empno FROM emp_jobhistory;
课后作业
1.用集合运算,列出不包含job为SALESMAN 的部门的部门号。
2.SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp WHERE job='SALESMAN'
2.写一个联合查询,列出下面的信息:EMP表中所有雇员的名字和部门编号,不管他们是否属于任何部门。
DEPT表中的所有部门编号和部门名称,不管他们是否有员工。
SELECT ename ,deptno,NULL FROM emp
UNION
SELECT NULL,deptno,dname FROM dept
ORDER BY deptno,ename DESC
3.用集合运算查询出职位为SALESMAN和部门编号为10的人员编号、姓名、职位,不排除重复结果。
SELECT empno,ename,job FROM emp WHERE job='SALESMAN'
UNION ALL
SELECT empno,ename,job FROM emp WHERE deptno =10
4.用集合查询出部门为10和20的所有人员编号、姓名、所在部门名称。
SELECT empno,ename,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND e.deptno=10
UNION ALL
SELECT empno,ename,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND e.deptno= 20