有关ORACLE 的 SET 操作符---UNION/UNION ALL、INTERSECT、MINUS学习

妖狐艹你老母 2023-05-31 02:33 16阅读 0赞

# 1、SET操作符介绍： #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 1]

### 特点： ###

![在这里插入图片描述][20191021221710315.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 2]

# 2、案例： #

##### 1. 查询部门的部门号，其中不包括job\_id是”ST\_CLERK”的部门号 #####

/* select department_id from departments where department_id not in ( select distinct department_id from employees where job_id = 'ST_CLERK' ) */
    select department_id
    from departments
    minus
    select department_id
    from employees
    where job_id = 'ST_CLERK'

##### 2. 查询10，50，20号部门的job\_id，department\_id并且department\_id按10，50，20的顺序排列 #####

1)column a_dummy noprint;
    2)
    SELECT job_id,department_id,1 a_dummy
    from employees
    where department_id = 10
    union
    SELECT job_id,department_id,2
    from employees
    where department_id = 50
    union
    SELECT job_id,department_id,3
    from employees
    where department_id = 20
    order by 3 asc

column a\_dummy noprint，不打印伪列 可以使用该语句

##### 3. 查询所有员工的last\_name ,department\_id 和department\_name #####

select last_name,department_id,to_char(null)
    from employees
    union
    select to_char(null),department_id,department_name
    from departments

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70]: /images/20230531/7b78fc63c8b3473b8b42fd3654a898da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 1]: /images/20230531/bfac58bab0be4e898c642f1ff29056ec.png
[20191021221710315.png]: /images/20230531/b449973d869f4a4f89bc978624abb986.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjE5MzQ4OQ_size_16_color_FFFFFF_t_70 2]: /images/20230531/9b294e377af34910b9460b771b62c420.png