Oracle之Union与Union all的区别

浏览数：41 / 时间：2015年06月12日

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是，union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union all：对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

下面以实例说明Union与Union all的区别：

1、首先创建一张jack表：

SQL> create table jack
  2  (
  3    id int primary key,
  4    name varchar2(30) not null,
  5    score number not null
  6  );

表已创建。
QL> insert into jack values(1,‘Aaron‘,78);
SQL> insert into jack values(2,‘Bill‘,76);
SQL> insert into jack values(3,‘Cindy‘,89);
SQL> insert into jack values(4,‘Damon‘,90);
SQL> insert into jack values(5,‘Ella‘,73);
SQL> insert into jack values(6,‘Frado‘,61);
SQL> insert into jack values(7,‘Gill‘,99);
SQL> insert into jack values(8,‘Hellen‘,56);
SQL> insert into jack values(9,‘Ivan‘,93);
SQL> insert into jack values(10,‘Jay‘,90);
SQL> commit;

SQL> select * from jack;

    ID NAME              SCORE
---------- -------------------- ----------
     1 Aaron            78
     2 Bill             76
     3 Cindy            89
     4 Damon            90
     5 Ella             73
     6 Frado            61
     7 Gill             99
     8 Hellen            56
     9 Ivan             93
    10 Jay                90

已选择10行。

2、使用union与union all进行查询：

SQL> select * from jack where id<4 
  2  union
  3  select * from jack where id >2 and id<6;

    ID NAME              SCORE
---------- -------------------- ----------
     1 Aaron            78
     2 Bill             76
     3 Cindy            89
     4 Damon            90
     5 Ella             73
     
SQL> select * from jack where id<4 
  2  union all
  3  select * from jack where id >2 and id<6;

    ID NAME              SCORE
---------- -------------------- ----------
     1 Aaron            78
     2 Bill             76
     3 Cindy            89
     3 Cindy            89
     4 Damon            90
     5 Ella             73

已选择6行。

从上面的两个查询中可以看出它们的区别之一在于对重复结果的处理。
3、调整两个子查的顺序：

SQL> select * from jack where id >2 and id<6
  2  union
  3  select * from jack where id<4 ;

    ID NAME              SCORE
---------- -------------------- ----------
     1 Aaron            78
     2 Bill             76
     3 Cindy            89
     4 Damon            90
     5 Ella             73
     
SQL> select * from jack where id >2 and id<6
  2  union all
  3  select * from jack where id<4 ;

    ID NAME              SCORE
---------- -------------------- ----------
     3 Cindy            89
     4 Damon            90
     5 Ella             73
     1 Aaron            78
     2 Bill             76
     3 Cindy            89

已选择6行。

它们两者的区别之二在于对排序的处理。Union all将按照关联的次序组织数据，而Union将进行依据一定规则进行排序。
4、验证Union排序规则(调整一下查询字段的顺序)：

SQL> select score,id,name from jack where id >2 and id<6
  2  union
  3  select score,id,name from jack where id<4 ;

     SCORE       ID NAME
---------- ---------- --------------------
    73        5 Ella
    76        2 Bill
    78        1 Aaron
    89        3 Cindy
    90        4 Damon

可以看到之前的查询是基于id,name,score的字段顺序，那么结果集将按照id优先进行排序；而现在新的字段顺序也改变了产讯结果的排序，由此可以按照union的排序是按照第一个字段进行排序的，但是我们也可以进行干预，指定按某个字段进行排序。
5、指定某个字段进行排序

SQL> select * from 
  2  (
  3   select score,id,name from jack where id>2 and id<7
  4  union
  5   select score,id,name from jack where id<4
  6  union
  7   select score,id,name from jack where id>8
  8  )
  9  order by id desc;

     SCORE       ID NAME
---------- ---------- --------------------
    90       10 Jay
    93        9 Ivan
    61        6 Frado
    73        5 Ella
    90        4 Damon
    89        3 Cindy
    76        2 Bill
    78        1 Aaron

已选择8行。