union的特性，去重与不去重

bug使我进步

最近一个功能异常了，sql是这个样子的

select * from 表1
union
select * from 表2
union
select * from 表3

 

发现统计的时候少数据，仔细把查询出来的结果和表里的数据对比后，发现每个字段值都相同的行竟然只留下了一行，比如数据库里，像下面这样的5行数据，完全一样，结果集里就只出现了一条

看到这样子的结果，顿时觉得有点糊涂，这个sql既没有用group by，也没有用distinct，怎么会去重的？脑子里一篇混乱，觉得这个sql好有个性，还带高兴了自己去重的

不过还是想着研究下这个sql的脾气，看下到底啥时候去重，啥时候不去重，仔细想了下，觉得有可能是这个union的问题，于是百度了一下，发现这个union不了解的话还真不是随随便便能乱用的，这个union还真会去重！

union的特性，去重与不去重

集合操作有　并，交，差　３种运算：

union: 得到两个查询结果的并集，并且自动去掉重复行。不会排序 

union all: 得到两个查询结果的并集，不会去掉重复行。也不会排序 

intersect: 得到两个查询结果的交集，并且按照结果集的第一个列进行排序 

minus: 得到两个查询结果的减集，以第一列进行排序 

 

例子：

下面是两个表：一个主修课程表，一个选修课程表。 

这个是主修课程表： 
create table minors( 
  minor_id number primary key, 
  minor_name varchar2(30) not null, 
  credit_hour number(2) 
) 

插入３条记录：　　　　　　　　　　　　　　　 
insert into minors values(10101,'计算机原理',4) 
insert into minors values(10201,'自动控制原理',3) 
insert into minors values(10301,'工程制图原理',4) 

 

下面创建选修课程表： 
create table minors2( 
  minor_id number primary key, 
  minor_name varchar2(30) not null, 
  credit_hour number(2) 
)

插入两条记录： 
insert into minors2 values(10201,'自动控制原理',3) 
insert into minors2 values(10301,'工程制图原理',4)

 

1、两个表使用union all:
select minor_id,minor_name,credit_hour from minors 
union all 
select minor_id,minor_name,credit_hour from minors2
order by credit_hour 

结果： 
MINOR_ID MINOR_NAME                     CREDIT_HOUR 
---------- ------------------------------ ----------- 
     10201 自动控制原理                             3 
     10201 自动控制原理                             3 
     10101 计算机原理                               4 
     10301 工程制图原理                             4 
     10301 工程制图原理                             4 

 

2、两个表使用union:
select minor_id,minor_name,credit_hour from minors 
union 
select minor_id,minor_name,credit_hour from minors2 
order by credit_hour 

结果： 
MINOR_ID MINOR_NAME                     CREDIT_HOUR 
---------- ------------------------------ ----------- 
     10201 自动控制原理　　　　　　　　　　　　　　　　　3 
     10101 计算机原理  　　　　　　　　　　　　　　　　　4 
     10301 工程制图原理　　　　　　　　　　　　　　　　　4 

 

3、两个表使用intersect:
select minor_id,minor_name,credit_hour from minors 
intersect 
select minor_id,minor_name,credit_hour from minors2 

结果： 
MINOR_ID MINOR_NAME                     CREDIT_HOUR 
---------- ------------------------------ ----------- 
     10201 自动控制原理                             3 
     10301 工程制图原理                             4 

 

4、两个表使用minus:
select minor_id,minor_name,credit_hour from minors 
minus 
select minor_id,minor_name,credit_hour from minors2

结果： 
MINOR_ID MINOR_NAME                     CREDIT_HOUR 
---------- ------------------------------ ----------- 
     10101 计算机原理                               4