oracle中union和union all区别与性能分析

[ 概要 ]

经常写sql的同学可能会用到union和union all这两个关键词,可能你知道使用它们可以将两个查询的结果集进行合并,

那么二者有什么区别呢? 下面我们就简单的分析下.

?

?

[ 比较 ]

union: 对两个结果集进行并集操作,不包括重复行,相当于distinct,同时进行默认规则的排序;

union all: 对两个结果集进行并集操作,包括重复行,即所有的结果全部显示,不管是不是重复;

?

下面我们举一个简单的例子来证明上面的结论:

1. 准备数据:

?

1. drop?table?student;??
2. ??
3. create?table?student??
4. (??
5. ?????id?int?primary?key,??
6. ?????name?nvarchar2(50)?not?null,??
7. ?????score?number?not?null??
8. );??
9. ??
10. insert?into?student?values(1,‘Aaron‘,78);??
11. insert?into?student?values(2,‘Bill‘,76);??
12. insert?into?student?values(3,‘Cindy‘,89);??
13. insert?into?student?values(4,‘Damon‘,90);??
14. insert?into?student?values(5,‘Ella‘,73);??
15. insert?into?student?values(6,‘Frado‘,61);??
16. insert?into?student?values(7,‘Gill‘,99);??
17. insert?into?student?values(8,‘Hellen‘,56);??
18. insert?into?student?values(9,‘Ivan‘,93);??
19. insert?into?student?values(10,‘Jay‘,90);??
20. ??
21. commit;??

?

?

2. 比较不同点

查询比较①

?

1. --?union?all???
2. select?*?from?student?where?id?<?4??
3. union?all??
4. select?*?from?student?where?id?>?2?and?id?<?6??
5. ?????
6. --?union??
7. select?*?from?student?where?id?<?4??
8. union??
9. select?*?from?student?where?id?>?2?and?id?<?6??

union all 查询结果:

?

union 查询结果:

通过比较不难看出,union all不会去掉重复记录,而union去掉了重复的记录.

?

查询比较②

?

1. --?union?all??
2. select?*?from?student?where?id?>?2?and?id?<?6??
3. union?all??
4. select?*?from?student?where?id?<?4??
5. ??
6. --?union??
7. select?*?from?student?where?id?>?2?and?id?<?6??
8. union??
9. select?*?from?student?where?id?<?4??

union all 查询结果:

?

union 查询结果:

通过比较不难看出,union all会按照关联的次序组织数据,而union会依据一定的规则进行排序.?

那么这个规则是什么呢? 我们通过下面的查询得出规律:

?

1. --?union??
2. select?score,id,name?from?student?where?id?>?2?and?id?<?6??
3. union??
4. select?score,name?from?student?where?id?<?4??

?

结论: 按照字段出现的顺序进行排序,之前的查询相当于order by id,name,score,刚刚的查询相当于order by score,name.

?

?

[ 总结 ]

1. ?因为union all仅仅是简单的合并查询结果,并不会做去重操作,也不会排序,所以union all效率要比union高.

? ? 所以在能够确定没有重复记录的情况下,尽量使用union all.

?

2. 通常如果表有多个索引列时,用union替换where子句中的or会起到较好的效果,索引列使用or会造成全表扫描.?

? ? 注意: 以上规则只针对多个索引列有效,假如有column没有被索引,那还是用or吧.?

? ? 例如: 还是使用上面的例子,假定name和score上建有索引.?

1 -- 高效  
2 select id,score from student where name like ‘%y%‘   
3 union  
4 select id,score from student where score between 80 and 90  
5   
6 -- 低效  
7 select id,score from student where name like ‘%y%‘ or score between 80 and 90  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!