oracle union [all], intersect, minus

官方文档地址：

https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#SQLRF52341

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

union all

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows.

不做任何处理的将两个结果集合并

select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual 

A
1
2
3

union

The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows.

在union all的基础上，进行一个去重操作

select 1 a from dual union 
select 2 a from dual union 
select 2 a from dual 

A
1
2

intersect

The following statement combines the results with the INTERSECT operator, which returns only those unique rows returned by both queries:

取并集

select*from (
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual 
)
intersect
select*from (
select 1 a from dual union all
select 3 a from dual 
)

A
1
3

minus

The following statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second:

取差集，也就是说，最终的结果集为在前一个结果集中有，但是在后一个结果集中没有的。

select*from (
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual 
)
minus
select*from (
select 1 a from dual union all
select 3 a from dual 
)

A
2