oracle set运算符

--union取两个查询结果的合集 order by 1相当于按第一列，employee_id排,第二列相当于安department_id排
select employee_id, department_id id from empb
union all
select employee_id, department_id from empc order by 1;


--intersect取两个查询结果的交集
select employee_id,department_id id from empb
intersect
select employee_id,department_id from empc order by id;


--minus取两个查询结果的叉集
select employee_id,department_id id from empb
minus
select employee_id,department_id from empc order by id;
--可以通过补同一类型的null值来使其对齐，如下:
select employee_id ,department_id,to_char(null) from employees 
union 
select to_number(null),department_id,department_name from departments order by 2;




select 'study at', 2 a_dumny from dual
union 
select 'i want' ,1 from dual
union 
select 'school', 3 from dual order by 2;
--不显示 为a_dumny的列
column a_dumny noprint;
查询部门的部门号，其中不包括job_id 是‘st_clerck’的部门号；
--方法一
select department_id from departments
where  department_id not in (select department_id from employees where lower(job_id) ='st_clerck');
--方法二
select department_id from departments 
minus 
select department_id from employees where job_id='st_clerck';



--查询所有员工的department_id,department_name,last_name;
select department_id,to_char(null),last_name from employees 
union 
select department_id,department_name,to_char(null) from departments;