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只能是第一个查询中的列名)

转载请注明出处及原文链接，否则拒绝转载