这一章,要用到两个表:emp和job_history.下面是job_history的创建信息:
CREATE TABLE JOB_HISTORY(
empno NUMBER(4) NOT NULL,
deptno NUMBER(4) NOT NULL ,
start_date DATE,
end_date DATE,
job VARCHAR2(9));
insert into JOB_HISTORY values(7369,10,'01-1月-2002','31-12月-2004','CLERK');
insert into JOB_HISTORY values(7369,20,'01-1月-2005','31-12月-2006','CLERK');
insert into JOB_HISTORY values(7499,10,'15-2月-2002','31-12月-2004','SALESMAN');
insert into JOB_HISTORY values(7499,20,'12-3月-2005','31-12月-2006','SALESMAN');
insert into JOB_HISTORY values(1000,10,'15-2月-2002','31-12月-2004','SALESMAN');
1.联合运算(UNION)
-从两个查询返回除去重复值后的结果
例:显示当前和以前所有雇员的工作岗位,重复的记录不显示
SELECT empno,job
FROM emp
UNION
SELECT empno,job
FROM job_history;
结果:
EMPNO JOB
1000
7369 CLERK
7499 SALESMAN
7521 SALESMAN
...
7934 CLERK
2.全联合运算(UNION ALL)
-从两个查询返回包括所有重复值的结果
例:显示当前和以前所有雇员所在的部门
SELECT empno,job,deptno
FROM emp
UNION ALL
SELECT empno,job,deptno
FROM job_history
ORDER BY empno;
结果:
EMPNO JOB DEPTNO
1000 10
7369 CLERK 20
7499 SALESMAN 30
7521 SALESMAN 30
7566 MANAGER 20
7654 SALESMAN 30
7698 MANAGER 30
7782 MANAGER 10
7788 ANALYST 20
7839 PRESIDENT 10
7844 SALESMAN 30
7876 CLERK 20
7900 CLERK 30
7902 ANALYST 20
7934 CLERK 10
3.相交运算(INTERSECT)
-返回多个查询中所有相同的行
例:显示雇员表的emp和job,这些雇员当前所做的工作是他们以前做过一段时间,后来有变化,现在又在做的工作
SELECT empno,job
FROM emp
INTERSECT
SELECT empno,job
FROM job_history;
结果:
EMPNO JOB
7369 CLERK
7499 SALESMAN
4.相减运算(MINUS)
-返回在第一个查询中而不再第二个查询中的行
例:显示那些从来没有改变过他们工作的雇员
SELECT empno,job
FROM emp
MINUS
SELECT empno,job
FROM job_history;
结果:
EMPNO JOB
1000
7521 SALESMAN
7566 MANAGER
7654 SALESMAN
7698 MANAGER
7782 MANAGER
7788 ANALYST
7839 PRESIDENT
7844 SALESMAN
7876 CLERK
7900 CLERK
7902 ANALYST
7934 CLERK
5.结合运算的原则
-在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配
-在结果中的列名是第一个查询中出现的列名
-ORDER BY子句:
-只能出现在语句的最后
-从第一个SELECT语句接收列名、别名,或者位置记号
6.匹配SELECT语句
例:使用UNION运算,显示所有雇员的empno、job和sal
SELECT empno,job,sal
FROM emp
UNION
SELECT empno,job,0
FROM job_history;
结果:
EMPNO JOB SAL
1000 SALESMAN 0
1000 3000
7369 CLERK 0
7369 CLERK 2000
7499 SALESMAN 0
7499 SALESMAN 1600
7521 SALESMAN 1250
7566 MANAGER 2975
7654 SALESMAN 1250
7698 MANAGER 2850
7782 MANAGER 2450
7788 ANALYST 3000
7839 PRESIDENT 5000
7844 SALESMAN 1500
7876 CLERK 1100
7900 CLERK 950
7902 ANALYST 3000
7934 CLERK 1300
练习:
1.用集合运算,列出不包含job为CLERK的部门的部门号
SELECT deptno
FROM emp
MINUS
SELECT deptno
FROM emp
WHERE job='CLERK';
2.列出雇员的雇员编号和工作标识,哪些雇员现在的工作和他们以前的工作相同
SELECT empno,job
FROM emp
INTERSECT
SELECT empno,job
FROM job_history;
3.写一个复合查询,列出下面的信息:
-emp表中所有雇员的名字和部门标识
-dept表中的所有部门标识和部门名称
SELECT ename,deptno,TO_CHAR(null)
FROM emp
UNION
SELECT TO_CHAR(null),deptno,dname
FROM dept;