oracle9i学习笔记之十四 使用集合运算

 这一章，要用到两个表：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;