oracle——SET运算符

create table employees01  --创建表1
as 
select * from employees
where department_id in (70,80)

select * from employees01

create table employees02  --创建表2
as 
select * from employees
where department_id in (80,90)

select * from employees02

select * from employees where department_id = 80  --查看公共部分

--union 并集,不包括重复
--注意：select 列表的列名的表达式在数量上和数据类型要一致，如果不对应，要进行匹配
select employee_id from employees01
union
select employee_id from employees02

--union all 并集，包括重复
select employee_id from employees01
union all
select employee_id from employees02

--可以起别名，但是以上面的表为基础显示
select employee_id emp_id from employees01 --注意别名
union all
select employee_id from employees02


--intersect交集
select employee_id emp_id from employees01
intersect
select employee_id from employees02

select employee_id, department_id from employees01
intersect
select employee_id, department_id from employees02
order by 1 desc     --以第一列排序

--minus差集，集合A-B
select employee_id emp_id from employees01
minus
select employee_id from employees02

--匹配select举例
select employee_id,department_id,to_char(null)
from employees01
union
select to_number(null),department_id,department_name
from departments


--举例
--I want
--study at
--www.atguigu.com
select 'sdudy at' as "my dream",2
from dual
union
select 'I want to',1
from dual
union
select 'atguigu.com' as "my dream",3
from dual
order by 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'
)

--方式二：minus
select department_id
  from departments
minus
select distinct department_id
  from employees
 where job_id = 'ST_CLERK'
 
--2.	查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列 
select job_id，department_id,1
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