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

叁歲伎倆 2022-04-01 13:30 350阅读 0赞

**将多个查询用 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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzIyMDM5_size_16_color_FFFFFF_t_70][]

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

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** ###

*  取黄色相交部分

![2018123102451639.png][]

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中的元素

![2018123102473159.png][]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NzIyMDM5_size_16_color_FFFFFF_t_70]: /images/20220401/269f2260b08147e3aecdcee9c06ec0cc.png
[2018123102451639.png]: /images/20220401/e60462b951c94f75b814f20e5dfe72e2.png
[2018123102473159.png]: /images/20220401/b724a834034f4c76ab8d6fd7ecfc9e20.png