union 与union all 的区别

 1、区别之一：union会剔除重复数据，而union all则不会。
create table student (
  sid     number,
  sname   varchar2(20),
  grade  number
);

insert into student(sid,sname,grade) values(1,'zhangsan',98);
insert into student(sid,sname,grade) values(2,'lisi',97);
insert into student(sid,sname,grade) values(3,'wangwu',87);
insert into student(sid,sname,grade) values(4,'tianyang',77);
insert into student(sid,sname,grade) values(1,'zhangsan',98);
commit;
 
select * from student
union
select * from student;
 
屏幕显示结果如下：
SID                    SNAME                GRADE                 
---------------------- -------------------- ----------------------
1                      zhangsan             98                    
2                      lisi                 97                    
3                      wangwu               87                    
4                      tianyang             77                    
4 rows selected
 
从屏幕显示结果来看,重复的数据已经被剔除了。

select * from student
union all
select * from student;

屏幕显示的结果如下：
SID                    SNAME                GRADE                 
---------------------- -------------------- ----------------------
1                      zhangsan             98                    
2                      lisi                 97                    
3                      wangwu               87                    
4                      tianyang             77                    
1                      zhangsan             98                    
1                      zhangsan             98                    
2                      lisi                 97                    
3                      wangwu               87                    
4                      tianyang             77                    
1                      zhangsan             98                     10 rows selected

显而易见，重复的数据并没有被剔除掉。
 
2、区别之二：union会将查询出来的结果排序，而union all则不会。
select * from student where sid > 3
union
select * from student where sid < 3;

屏幕显示结果如下：SID                    SNAME                GRADE                 
---------------------- -------------------- ----------------------
1                      zhangsan             98                    
2                      lisi                 97                    
4                      tianyang             77                     3 rows selected

从显示结果来看，union对查询结果进行了排序。

 

select * from student where sid > 3
union all
select * from student where sid < 3;

屏幕显示结果如下：SID                    SNAME                GRADE                 
---------------------- -------------------- ----------------------
4                      tianyang             77                    
1                      zhangsan             98                    
2                      lisi                 97                    
1                      zhangsan             98                     4 rows selected

从显示结果来看，union all 未对查询结果进行了排序。