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

1、SET操作符介绍：

特点：

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