Oracle数据库之集合操作

----数据库之集合操作
—查询一下雇员表中，工资最高的前三名人员，和工资最低的后三名人员

select *from (select * from  employees s order by s.salary desc )where rownum<=3
select *from (select * from  employees s order by s.salary asc)where rownum<=3

1、合并 --unio去重复 union不去重复

select *from (select * from  employees s order by s.salary desc )where rownum<=3
union all
select *from (select * from  employees s order by s.salary asc)where rownum<=3

2、相减–minus相减即可

—统计雇员的信息，删除工资最高的三个人

select *
  from employees s
minus
select *
  from (select * from employees s order by s.salary desc)
 where rownum <= 3

–从雇员表中将换过工作的人员去掉

select *
  from employees s
minus
select *
  from employees s
 where s.employee_id in (select distinct employee_id from job_history);

3、相交 intersect

select 1,2,3,4,7 from dual
intersect
select 1,2,3,4 ,6 from dual