Oracle-set操作符（集合操作）

将多个查询用 SET 操作符连接组成一个新的查询

• UNION/UNION ALL
• INTERSECT
• MINUS

这里我基于同一张表创建出两张表

CREATE TABLE employees01
AS
SELECT *
FROM employees e
WHERE e.department_id IN (70,80);

CREATE TABLE employees02
AS
SELECT *
FROM employees e
WHERE e.department_id IN (90,80);

一：UNION/UNION ALL

• UNION：A+B-2

交集部分只取一次，相当于集合A和B的所有不重复的元素的集合

• UNION ALL：A+B

交集部分取两次，相当于集合A和B所有不重复的元素集合再加2

 

两个集合查询出来的字段必须一一对应

SELECT e1.employee_id,e1.department_id,e1.last_name
FROM employees01 e1
UNION
SELECT e2.employee_id,e2.department_id,e2.last_name
FROM employees01 e2

-- 这里支持起别名，但是只支持上面的表起别名，下面的表起别名不起作用
SELECT e1.employee_idemp_id,e1.department_id,e1.last_name NAME
FROM employees01 e1
UNION
SELECT e2.employee_id,e2.department_id,e2.last_name
FROM employees01 e2

-- 支持排序
SELECT e1.employee_id emp_id,e1.department_id,e1.last_name NAME
FROM employees01 e1
UNION ALL
SELECT e2.employee_id,e2.department_id,e2.last_name
FROM employees01 e2
ORDER BY emp_id DESC;

SELECT e1.department_id,e1.department_id,e1.last_name
FROM employees01 e1
UNION ALL
SELECT e2.department_id,e2.department_id,e2.last_name
FROM employees01 e2

二、INTERSECT

• 取黄色相交部分

 

SELECT e1.employee_id emp_id,e1.department_id,e1.last_name NAME
FROM employees01 e1
INTERSECT
SELECT e2.employee_id,e2.department_id,e2.last_name
FROM employees02 e2;

 

三、MINUS

Ⅰ：ORDER BY 子句

–只能在语句的最后出现

–可以使用第一个查询中的列名, 别名或相对位置

–系统将第一个查询的列名显示在输出中

–除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

• A-B：取集合A中减去集合B中的元素

SELECT e1.employee_id emp_id,e1.department_id,e1.last_name NAME
FROM employees01 e1
MINUS
SELECT e2.employee_id,e2.department_id,e2.last_name
FROM employees02 e2;

四、集合查询排序

• 除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

SELECT 'I want to' 
FROM dual
UNION 
SELECT 'study'
FROM dual
UNION
SELECT 'Oracle'
FROM dual;
-- 结果为I want to
--      Oracle
--      study
-- 这里第一列是字符串，按照字符串的首字母排序了

• 利用集合查询按照相对位置的规则，使用order by 让查询按照第二列排序，输出顺寻为‘I want to’ ‘study’ ‘Oracle’

SELECT 'I want to' ,1
FROM dual
UNION 
SELECT 'study',2
FROM dual
UNION
SELECT 'Oracle',3
FROM dual
ORDER BY 2;
-- 输出结果为：
-- I want to          1
-- study              2
-- Oracle             3