Oracle-Set运算符

Set运算符-将多个查询用SET操作符连接组成一个新的查询

UNION/UNION ALL

INTERSECT

MINUS

UNION  查询结果排除重复元素

select 'A' as col , 'B' as num from dual
union
select 'A' as col , 'B' as num from dual
union
select 'C' as col , 'B' as num from dual;

COL NUM
--- ---
A   B
C   B

UNION ALL 查询结果不排除重复元素

select 'A' as col , 'B' as num from dual
union all
select 'A' as col , 'B' as num from dual
union all
select 'C' as col , 'B' as num from dual;

COL NUM
--- ---
A   B
A   B
C   B

INTERSECT 查询结果中的交集

select 'A' as col , 'B' as num from dual
INTERSECT 
select 'A' as col , 'B' as num from dual
INTERSECT 
select 'C' as col , 'B' as num from dual;

COL NUM
--- ---

select 'A' as col , 'B' as num from dual
INTERSECT
select 'A' as col , 'B' as num from dual;

COL NUM
--- ---
A   B

MINUS 差集  A minus B  查询A的结果同时排除AB共有的结果

select 'A' as col , 'B' as num from dual
MINUS
select 'C' as col , 'B' as num from dual;


COL NUM
--- ---
A   B

select 'A' as col , 'B' as num from dual
MINUS
select 'A' as col , 'B' as num from dual;

COL NUM
--- ---

使用相对位置排序

select 'test1' as col , 'a' as num from dual
union
select 'test2' as col , 'b' as num from dual
union
select 'test3' as col , 'c' as num from dual
order by 2 desc;

COL   NUM
----- ---
test3 c
test2 b
test1 a