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 未对查询结果进行了排序。