Oracle 集合操作符 UNION/UNION ALL/INTERSECT/MINUS的用法

SET OPERATIONS
集合操作符
----合并
UNION / UNION ALL

----交集
INTERSECT

----
MINUS

集合操作的規範
-兩個查詢的 SELECT 列表必須在個數上匹配
-第二個查詢中的每個列的資料類型必須與第一個查詢中相應列的資料類型匹配
-可以使用括弧來改變執行的順序
-ORDER BY 可以也只可以出現在整個語句的結尾。

除了 UNION ALL 以外，其餘的三個操作符都會消除結果集中的重複行。
結果集使用第一個查詢中的列名顯示
預設情況下，除 UNION ALL 外，輸出以升序進行排序

樣本 table：
HR schema 下的 EMPLOYEES 和 JOB_HISTORY

樣本：
----union 返回排除重複記錄
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY;

----union all 返回包括重複記錄且不排序
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY;
ORDER BY EMPLOYEE_ID;
----最後的 order by 是對整個union all 操作結果集的 order by

----INTERSECT 返回交集
SELECT EMPLOYEE_ID, JOB_ID
FROM EMPLOYEES
INTERSECT
SELECT EMPLOYEE_ID, JOB_ID
FROM JOB_HISTORY;

EMPLOYEE_ID JOB_ID
----------- ----------
        176 SA_REP
        200 AD_ASST


----MINUS 操作符（取交集）
MINUS 返回第一個查詢查詢出的所有經過 distinct 操作後的且未出現在第二個
查詢結果集中的行。

SELECT EMPLOYEE_ID
FROM EMPLOYEES
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;

----有時，兩張表中的列的資料欄位類型不符，這時可以作如下處理
SELECT LOCATION_ID,DEPARTMENT_NAME "DEPARTMENT",
       TO_CHAR(NULL) "WAREHOUSE LOCATION"
FROM DEPARTMENTS
UNION
SELECT LOCATION_ID, TO_CHAR(NULL) "DEPARTMENT", STATE_PROVINCE
FROM LOCATIONS;

LOCATION_ID DEPARTMENT                     WAREHOUSE LOCATION
----------- ------------------------------ -------------------------
       1000
       1100
       1200                                Tokyo Prefecture
       1300
       1400 IT
       1400                                Texas
       1500 Shipping
       1500                                California
       1600                                New Jersey
       1700 Accounting
       1700 Administration
       1700 Benefits
       1700 Construction
       1700 Contracting
       1700 Control And Credit
       1700 Corporate Tax
       1700 Executive
       1700 Finance
       1700 Government Sales
       1700 IT Helpdesk
       1700 IT Support
       1700 Manufacturing
       1700 NOC
       1700 Operations
       1700 Payroll
       1700 Purchasing
       1700 Recruiting
       1700 Retail Sales
       1700 Shareholder Services
       1700 Treasury
       1700                                Washington
       1800 Marketing
       1800                                Ontario
       1900                                Yukon
       2000
       2100                                Maharashtra
       2200                                New South Wales
       2300
       2400 Human Resources
       2400
       2500 Sales
       2500                                Oxford
       2600                                Manchester
       2700 Public Relations
       2700                                Bavaria
       2800                                Sao Paulo
       2900                                Geneve
       3000                                BE
       3100                                Utrecht
       3200                                Distrito Federal,

  
-----------
使用 UNION /UNION ALL /INTERSECT/MINUS
的查詢稱為組合查詢compound query
組合查詢中各個組件查詢無法單獨使用 ORDER BY 子句
ORDER BY 子句只能出現在組合查詢的最後面
ORDER BY 子句只能識別第一個查詢中的列
預設情況下，第一個查詢的第一個列用來對輸出以升序排序。
SELECT LOCATION_ID,DEPARTMENT_NAME "DEPARTMENT",
       TO_CHAR(NULL) "WAREHOUSE LOCATION"
FROM DEPARTMENTS
UNION
SELECT LOCATION_ID, TO_CHAR(NULL) "DEPARTMENT", STATE_PROVINCE
FROM LOCATIONS

ORDER BY LOCATION_ID;(這裡的LOCATION_ID只能是第一個查詢中的列名)

轉載請註明出處及原文連結，否則拒絕轉載