SET运算符

-- SET操作符：

   

-- union   联合去重 两个表的字段个数和类型都得一样，起别名 应该在第一个表上操作，，排序按照第一列从小到大来排的

select employee_id, department_id 
from employee01
union --union all
select employee_id, department_id 
from employee02;

minus  ：取差集

-- 使两表字段个数和类型一致

select employee_id emp_id,dapartment_id,to_char(null)
from employees01
union
select to_number(null),department_id,department_name
from departments;

--查询10,50,20部门的job_id,department_id，且department_id 按10,50,20的顺序来排列

column a_dummy noprint;
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;

JOB_ID     DEPARTMENT_ID
---------- -------------
AD_ASST               10 
SH_CLERK              50 
ST_CLERK              50 
ST_MAN                50 
MK_MAN                20 
MK_REP                20 

--查询所有员工的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;