Oracle集合运算

集合运算：组合两个或多个部分查询的结果到一个结果中。包含集合运算的查询称为复合查询。

•  并集：UNION(联合)
  

  SQL> select * from emp where deptno=10
    2  union
    3  select * from emp where deptno=20;
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7369 SMITH                CLERK                    7902 17-12月-80            800                 20
        7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
        7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
        7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
        7839 KING                 PRESIDENT                     17-11月-81           5000                 10
        7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
        7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
        7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10
  
  已选择8行。

• 并集（相交部分重复显示）

  SQL> select * from emp where deptno=10 or deptno=20
    2  union
    3  select * from emp where deptno=10 or deptno=20;
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7369 SMITH                CLERK                    7902 17-12月-80            800                 20
        7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
        7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
        7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
        7839 KING                 PRESIDENT                     17-11月-81           5000                 10
        7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
        7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
        7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10
  
  已选择8行。
  
  SQL> select * from emp where deptno=10 or deptno=20
    2  union all
    3  select * from emp where deptno=10 or deptno=20;
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7369 SMITH                CLERK                    7902 17-12月-80            800                 20
        7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
        7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
        7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
        7839 KING                 PRESIDENT                     17-11月-81           5000                 10
        7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
        7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
        7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10
        7369 SMITH                CLERK                    7902 17-12月-80            800                 20
        7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
        7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
        7839 KING                 PRESIDENT                     17-11月-81           5000                 10
        7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
        7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
        7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10
  
  已选择16行。

• 交集：INTERSECT(交叉)
  

  SQL> select * from emp where sal>1000
    2  intersect
    3  select * from emp where sal>800;
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30
        7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30
        7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
        7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30
        7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30
        7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
        7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
        7839 KING                 PRESIDENT                     17-11月-81           5000                 10
        7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30
        7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
        7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10
  
  已选择12行。

• 差集：MINUS (相减)

  SQL> select * from emp where sal<1300
    2  minus
    3  select * from emp where sal>1000;
  
       EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
  ---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
        7369 SMITH                CLERK                    7902 17-12月-80            800                 20
        7900 JAMES                CLERK                    7698 03-12月-81            950                 30