Oracle 集合运算

/*
       集合运算: 
         并集: 将两个查询结果进行合并
         交集
         差集
       
       所有的查询结果可能不是来自同一张表,  
         emp  2000年
              2017年 手机 详细信息 emp2017
         
*/
--工资大于1500,或者20号部门下的员工
select * from emp where sal > 1500 or deptno = 20;

--工资大于1500
select * from emp where sal > 1500;
--20号部门下的员工
select * from emp where deptno = 20;

--并集运算: union  union all
/*
        union : 去除重复的,并且排序
        union all : 不会去除重复的
*/
select * from emp where sal > 1500
union
select * from emp where deptno = 20;

select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

/*
    交集运算: intersect
       
*/
--工资大于1500,并且20号部门下的员工
select * from emp where sal > 1500;
select * from emp where deptno = 20;

select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;


/*
   差集运算: 两个结果相减
*/
--1981年入职员工(不包括总裁和经理)
--1981年入职员工
select * from emp where to_char(hiredate,'yyyy')='1981';

--总裁和经理
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';


select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';



/*
       集合运算中的注意事项:
         1.列的类型要一致
         2.按照顺序写
         3.列的数量要一致,如果不足,用空值填充
*/
select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--列的类型不匹配
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;

--列的数量不匹配
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;

select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,66 from emp where deptno = 20;

select * from emp;
select * from dept;