【oracle】union、union all、intersect、minus 的用法及区别

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、union与union all

首先建两个view

create or replace view test_view_1 as
select 1 as a, 1 as b, 1 as c from dual
union
select 2 as a, 2 as b, 2 as c from dual
union
select 3 as a, 3 as b, 3 as c from dual ;
-----
create or replace view test_view_2 as
select 4 as a, 4 as b, 4 as c from dual
union
select 3 as a, 3 as b, 3 as c from dual
union
select 2 as a, 2 as b, 2 as c from dual
order by a desc, b desc, c desc;
-- 注意：这个地方特意将test_view_2中的数据做了倒序排列

首先，看一下union的结果：

select * from test_view_1
union
select * from test_view_2;

结果如下：

然后，再看一下union all的结果：

select * from test_view_1
union all
select * from test_view_2;

结果如下：

从以上两个查询结果中，我们可以得到这样两个结论：
（1）union后的结果会自动去除数据重复的记录；而union all的结果会保留所有的数据。
（2）union后的结果会默认按照各字段的数据进行升序排序，各select本身的sort将会被忽略掉；而union all的结果不会进行排序，是单纯根据union all前后的结果顺序直接显示。

注：需要注意的一点，如果要对union或者union all的最终结果进行排序，那么需要在最后一个select语句上增加order by 才能对整个union或union all的结构起到排序作用

二、intersect和minus
1、子查询结果的列字段相同
这里我们依然使用上面的test_view_1和test_view_2这两个视图，同时还增加第三个视图

----- 新建视图
create or replace view test_view_3 as
select 3 as a, 3 as b, 3 as c from dual
union
select 4 as a, 4 as b, 4 as c from dual ;
select * from test_view_3;

看一下intersect的结果：

select * from test_view_1
intersect
select * from test_view_2;

结果如下：

从结果上，我们可以看到，intersect取了两个结果集的并集。

然后，我们再来看一下minus的结果：

select * from test_view_1
minus
select * from test_view_2 ;

结果如下：

从结果上，可以看到，只有一条记录返回，而且这条记录是第一个视图test_view_1中的数据。

然后我们再做一个minus的实验：

select * from test_view_1
minus
select * from test_view_3;

结果如下：

从结果上来看，依然只有第一个结果集（test_view_1）中的差显示出来，第二个结果集（test_view_3）中的记录4没有显示出来。

这时，我们就有一个疑问了：minus是做上下两个结果集的差，为什么结果中只有第一个视图的数据，而没有第二个视图的数据呢？

因为SQL中，minus做了如下处理：
如果两个查询结果集中有数据不相同的记录时（即存在差），将只显示第一个结果集中的差值记录，第二个结果集中的不一样的记录将被剔除。

那么，和minus相反的intersect取交集，是否也有同样的处理机制呢？可以做一个实验看一下。
首先创建新的视图，将数据做一下修改：

create or replace view test_view_4 as
select 2 as b, 2 as c, 2 as d from dual
union
select 3 as b, 3 as c, 3 as d from dual
union
select 4 as b, 4 as c, 4 as d from dual ;
--------create or replace view test_view_5 as
select 2 as a, 2 as b, 2 as c, 2 as d from dual
union
select 3 as a, 3 as b, 3 as c, 3 as d from dual
union
select 4 as a, 4 as b, 4 as c, 4 as d from dual ;-------
create or replace view test_view_6 as
select 'char1' as a, 'char1' as b, 'char1' as c from dual
union
select 'char2' as a, 'char2' as b, 'char2' as c from dual
union
select 'char3' as a, 'char3' as b, 'char3' as c from dual ;