Oracle 做并集-union、差集minus、交集intersect

创建测试数据：

create table A(ID NUMBER,NAME VARCHAR2(10));
insert into A
	select 1 as ID,'北京' as NAME from dual union all
	select 2 as ID,'上海' as NAME from dual union all
	select 3 as ID,'广州' as NAME from dual;


create table B(ID NUMBER,NAME VARCHAR2(10));
insert into B
	select 1 as ID,'北京' as NAME from dual union all
	select 2 as ID,'上海' as NAME from dual union all
	select 4 as ID,'深圳' as NAME from dual;

commit;

A表与B表 

并集：

union（对合成的结果去重）

select * from A 
union 
select * from B;

 

union all（完全合成两个表）

select * from A 
union all
select * from B;

交集：intersect（取在A表与B表同时存在的数据） 

select * from A 
intersect
select * from B;

差集：minus（取在A表中存在但是B表中不存在的数据）

select * from A 
minus
select * from B;

注意，这里的函数都需要两个表有相同的表结构。

有个小技巧，可以查询Oracle数据库的指定行到指定行的数据：

select * from A where ROWNUM <=3
minus
select * from A where ROWNUM <=2;