oracle sql 集合差,ORACLE SQL 集合操作

第十五章:集合操作

create table emp_tmp as select * from emp where deptno=10;

insert into emp_tmp values (7935,'alvin','clerk',7566,sysdate,777,null,40);

select empno,ename,deptno from emp;

select empno,ename,deptno from emp_tmp;

并集：

--union 有去重操作

select empno,ename,deptno from emp

union

select empno,ename,deptno from emp_tmp;

--union all 不去重

select empno,ename,deptno from emp

union all

select empno,ename,deptno from emp_tmp;

交集:intersect

select empno,ename,deptno from emp

intersect

select empno,ename,deptno from emp_tmp;

差集:minus

select empno,ename,deptno from emp

minus

select empno,ename,deptno from emp_tmp;

select empno,ename,deptno from emp_tmp

minus

select empno,ename,deptno from emp;

两个查询所枚举的列的数量不同:

select empno,ename,sal from emp

union

select deptno,dname,null from dept;