SQL 基礎7—— 集合運算(UNION 與UNION ALL)

 集合運算操作符可以将兩個或多個查詢傳回的行組合起來，即集合屬于縱向連接配接運算

一、常用的集合運算符

    UNION ALL 傳回各個查詢檢索出的所有的行，不過濾掉重複記錄

    UNION     傳回各個查詢檢索出的過濾掉重複記錄的所有行，即并集

    INTERSECT 傳回兩個查詢檢索出的共有行，即交集

    MINUS     傳回将第二個查詢檢索出的行從第一個查詢檢索出的行中減去之後剩餘的行，即差集

二、集合運算的原則

    1.所有選擇清單的表達式數目必須相同

    2.對于結果集中各列，或個别子查詢中的任意列的子集必須具有相同的資料類型，或是可以隐式轉化為相同的資料類型，否則需顯示轉換

    3.各個查詢中對應的結果集列出現的順序必須相同

    4.生成的結果集中的列名來自UNION語句中第一個單獨的查詢

三、示範各個集合運算符  

    --為集合運算生成環境，生成有相同結構的emp表，且命名為emp2

    SQL> conn scott/tiger;

    Connected.

    SQL> select * from emp;

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

         7369 SMITH      CLERK           7902 17-DEC-80        800                    20

         7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

         7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

         7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

         7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

         7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

         7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

         7839 KING       PRESIDENT            17-NOV-81       5000                    10

         7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

         7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

         7900 JAMES      CLERK           7698 03-DEC-81        950                    30

         7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

         7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

    14 rows selected.

    SQL> create table emp2 tablespace tbs1 as select * from emp where empno in (7369,7654,7839,7876);

    Table created.

    SQL> insert into emp2 (empno,ename,sal) select 8001,'ROBINSON',3500 from dual;

    1 row created.

    SQL> insert into emp2 (empno,ename,sal) select 8002,'HENRY',3700 from dual;

    SQL> insert into emp2 (empno,ename,sal) select 8004,'JOHNSON',4000 from dual;

    SQL> select * from emp2;

         8001 ROBINSON                                        3500

         8002 HENRY                                           3700

         8004 JOHNSON                                         4000

    --1.UNION  過濾了重複記錄

       SQL> select empno,ename,job,hiredate,sal from emp

         2  union   

         3  select empno,ename,job,hiredate,sal from emp2;

            EMPNO ENAME      JOB       HIREDATE         SAL

       ---------- ---------- --------- --------- ----------

             7369 SMITH      CLERK     17-DEC-80        800

             7499 ALLEN      SALESMAN  20-FEB-81       1600

             7521 WARD       SALESMAN  22-FEB-81       1250

             7566 JONES      MANAGER   02-APR-81       2975

             7654 MARTIN     SALESMAN  28-SEP-81       1250

             7698 BLAKE      MANAGER   01-MAY-81       2850

             7782 CLARK      MANAGER   09-JUN-81       2450

             7788 SCOTT      ANALYST   19-APR-87       3000

             7839 KING       PRESIDENT 17-NOV-81       5000

             7844 TURNER     SALESMAN  08-SEP-81       1500

             7876 ADAMS      CLERK     23-MAY-87       1100

             7900 JAMES      CLERK     03-DEC-81        950

             7902 FORD       ANALYST   03-DEC-81       3000

             7934 MILLER     CLERK     23-JAN-82       1300

             8001 ROBINSON                             3500

             8002 HENRY                                3700

             8004 JOHNSON                              4000

       17 rows selected.

    --2.UNION ALL 并集，不去重複記錄

         2  union all

       21 rows selected.

    --3.INTERSECT 交集,傳回兩個結果集中共有了部分

         2  intersect

    --4.MINUS  補集,前一個結果集減後一個結果集後的結果

       SQL> select empno as "EmployeeNo" ,ename  "EmployeeName",job  "Job" ,hiredate as "HireDate",sal

"Sal" from emp

         2  minus

         3  select empno,ename,job,hiredate,sal from emp2

         4  order by "Sal";

       EmployeeNo EmployeeNa Job       HireDate         Sal

       10 rows selected.