Oracle SQL 学习笔记5 - 集合操作符

什么是集合操作符

集合操作符将两个查询的结果组合成一个结果，常见集合操作符：MINUS(减去)，INTERSECT(交集)和UNION ALL(并集)【1】。

UNION

Union.png

实例1，利用列配对

SQL> SELECT e.employee_id, e.job_id, e.salary
FROM hr.employees e
UNION
SELECT j.employee_id, j.job_id, 0
FROM hr.job_history j;   

EMPLOYEE_ID JOB_ID     SALARY
----------- ---------- ----------
    100 AD_PRES     24000
    101 AC_ACCOUNT      0
    101 AC_MGR      0
    101 AD_VP       17000
    102 AD_VP       17000
    102 IT_PROG     0
    103 IT_PROG      9000
    104 IT_PROG      6000
    105 IT_PROG      4800
    106 IT_PROG      4800
    107 IT_PROG      4200

实例2，为集合操作匹配列：

SQL> SELECT e.department_id, TO_NUMBER(null) location, e.hire_date
FROM HR.employees e
UNION 
SELECT d.department_id, d.location_id, TO_DATE(null)
FROM HR.departments d;  

DEPARTMENT_ID   LOCATION HIRE_DATE
------------- ---------- ---------
       10       1700
       10        17-SEP-03
       20       1800
       20        17-FEB-04
       20        17-AUG-05
       30       1700
       30        07-DEC-02
       30        18-MAY-03
       30        24-JUL-05
       30        24-DEC-05
       30        15-NOV-06

UNION ALL

UNION ALL 操作符返回两个查询结果集的合并，但不去重复值，也不排序。

Oracle UNION操作符特点

1. 除了UNION ALL其他集合操作都自动剔除重复值
2. 结果列名自动继承第一个SELECT语句的列名
3. 除了UNION ALL语句其他集合操作都自动对结果排序
4. 多个SELECT表达式列表必须在类型和个数上匹配
5. 可以用括号来改变执行顺序
6. ORDER BY子句只能出现在整个语句的最后。可以使用第一个SELECT语句的列名、列别名和位置号

INTERSECT

Intersect.png

实例，找到工作title和初始受雇时一样的员工，并显示他们的员工号和工作号。

SQL> SELECT e.employee_id, e.job_id
FROM HR.employees e
INTERSECT
SELECT j.employee_id, j.job_id
FROM HR.job_history j;    

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

MINUS

Minus.png

实例，显示从来没有改变过工作的员工的员工号：

SQL> SELECT e.employee_id
FROM HR.employees e
MINUS
SELECT h.employee_id
FROM HR.job_history h;    

EMPLOYEE_ID
-----------
    100
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112

控制返回行的顺序

如果需要在集合操作的结果中体现不同集合的顺序，可以加入一个虚拟的列，并用ORDER BY 子句对行按照虚拟列排序。

SQL> 
COLUMN a_dummy NOPRINT
SELECT 'sing' AS "My dream", 3 a_dummy 
FROM dual
UNION SELECT 'I''d like to teach', 1 a_dummy
FROM dual
UNION
SELECT 'the world to', 2 a_dummy
FROM dual
ORDER BY a_dummy;

My dream
-----------------
I'd like to teach
the world to
sing

本文涉及到的表

本文涉及到的表来自于Oracle数据库自带的HR shema 下的：

1. EMPLOYEES：员工信息
2. JOB_HISTORY：员工工作变动历史信息

【1】Oracle——集合操作符（MINUS、INTERSECT、UNION ALL）