【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 ;

在test_view_4中，有三个column列，但是列名不再是a b c，而是改成了b c d ；
在test_view_5中，在已有的a b c三列基础上，又新增了第四列d ；
在test_view_6中，a b c三列的数据类型不再是number型，而是char字符型。

看一下test_view_1和test_view_4的intersect交集后的结果：

select a,b,c from test_view_1
intersect 
select b,c,d from test_view_4 ;

执行结果如下：

从以上结果可以看出，intersect在两个结果集的列名不同的情况下，也是取了第一个结果集中的数据进行显示（a b c），第二个结果集（test_view_4）中虽然d列也有数据和第一个结果集（test_view_1）的数据相同，但依然被intersect给剔除掉了。

 

接下来，测试一下，intersect是否会自动排序

select a,b,c from test_view_1
intersect 
select a,b,c from test_view_2 ;

执行结果如下：

根据上面的结果，得出结论：intersect两各结果集最后也是会自动升序排序。

 

然后再执行以下实验：

select a,b,c from test_view_1
intersect 
select a,b,c,d from test_view_5 ;

执行结果如下：
提示出错： ORA-01789: 查询块具有不正确的结果列数

 

select a,b,c from test_view_1
intersect 
select a,b,c from test_view_6 ;

执行结果如下：
提示出错：ORA-01790: 表达式必须具有与对应表达式相同的数据类型

从上述两个实验可以得出一个结论：intersect前后的结果集的列数 the number of columns 和 列字段的数据类型 the type of columns必须上下一致，否则执行失败。
minus和intersect一样，也是有这样的限制，这里不再做实验记录。