oracle union [all], intersect, minus

谁借莪１个温暖的怀抱￠ 2021-09-28 07:30 296阅读 0赞

### 文章目录 ###

*   *   *  官方文档地址：
         *  union all
         *  union
         *  intersect
         *  minus

### 官方文档地址： ###

[https://docs.oracle.com/cd/E11882\_01/server.112/e41084/queries004.htm\#SQLRF52341][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

### 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

### 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 
    )

### 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 
    )

[https_docs.oracle.com_cd_E11882_01_server.112_e41084_queries004.htm_SQLRF52341]: https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#SQLRF52341