oracle12c intersect,Oracle数据库union, union all, intersect, minus使用

create table test3( id int primary key, name varchar2(50) not null, age number not null ); insert into test3 values(1,'张三',23); insert into test3 values(2,'李四',21); insert into test3 values(3,'王二',20); insert into test3 values(4,'郭经',20); insert into test3 values(5,'王伟',23); insert into test3 values(6,'Frado',24); insert into test3 values(7,'伍梅',23); insert into test3 values(8,'张伦',23); insert into test3 values(9,'郭飞',21); insert into test3 values(10,'刘鹏飞',22);

去重显示 select * from test3 where id < 4 union select * from test3 where id > 2 and id < 6;如果有交集, 会去重显示

不去重显示 select * from test3 where id < 4 union all select * from test3 where id > 2 and id < 6; 如果有交集, 不会去重显示

不排序 select * from test3 where id > 2 and id < 6 union all select * from test3 where id < 4;

union all不会排序

排序 select * from test3 where id > 2 and id < 6 union select * from test3 where id < 4;

union会排序显示

INTERSECT交集 只查询交集部分 select * from test3 where id > 2 and id < 6 intersect select * from test3 where id < 4;

只显示交集的数据

MINUS 查询存在数据 select * from test3 where id > 2 and id < 6 --3,4,5 MINUS select * from test3 where id<4; --1,2,3

如果A表中的数据存在B表中, 那么A表中的这条数据会被移除, 只显示A表中的结果, 不会显示B表中的结果;