Oracle中UNION与UNION ALL的区别

在oracle数据库中，UNION与UNION ALL都是用来合并两个或者多个SELECT结果集,它们都要求这多个SELECT 有相同的数目的数据列，并且对应数据列的类型要相同。但UNION与UNION  ALL在查询结果以及查询结果的默认排序方式都有着不同，下面分别介绍它们的用法及区别。

  a) UNION

    SQL  UNION语法如下：

   

       SELECT column1,column2,...  FROM table1 

1. UNION 
2. SELECT column1,column2,...  FROM table2 

   SELECT column1,column2,...  FROM table1
   UNION
   SELECT column1,column2,...  FROM table2

   UNION操作的结果会去除相同的数据记录，并且按默认升序的方式把结果集排序

例如：我们创建TEST1以及TEST2两张表如下:

创建表test1:

 

1. create table TEST1 
2. ( 
3.   ID   VARCHAR2 (10 ), 
4.   NAME VARCHAR2 (20 ) 
5. ); 
6.  
7. insert into  test1( id, name ) values ( '1', 'andy'); 
8. insert into test1(id,name) values('2','ashely'); 
9. insert into test1(id,name) values('3','dona'); 

create table TEST1
(
  ID   VARCHAR2 (10 ),
  NAME VARCHAR2 (20 )
);

insert into  test1( id, name ) values ( '1', 'andy');
insert into test1(id,name) values('2','ashely');
insert into test1(id,name) values('3','dona');

创建表test2并插入三条数据

 

1. create table TEST2 
2. ( 
3.   ID   VARCHAR2 (10 ), 
4.   NAME VARCHAR2 (20 ) 
5. ); 
6.  
7. insert into  test1( id, name ) values ( '1', 'andy'); 
8. insert into test1(id,name) values('2','ashely'); 
9. insert into test1(id,name) values('3','beta'); 

create table TEST2
(
  ID   VARCHAR2 (10 ),
  NAME VARCHAR2 (20 )
);

insert into  test1( id, name ) values ( '1', 'andy');
insert into test1(id,name) values('2','ashely');
insert into test1(id,name) values('3','beta');

UNION测试：

SELECT　id,name FROM test1 结果如下：

        ID     NAME
       1     andy
       2     ahsely
       3     dona

SELECT　id,name FROM test2 结果如下：

      ID     NAME
       1     andy
       2     ahsely

       3     beta

而这两个SELECT结果集UNION的结果如下：

 

1. SELECT　id,name FROM test1 
2. UNION 
3. SELECT　id,name FROM test2 

 SELECT　id,name FROM test1
 UNION
  SELECT　id,name FROM test2

结果如下：

             ID     NAME

     1     andy
     2     ahsely
     3     beta
     3     dona

从结果我们可以看出：

1、UNION 在合并两个结果集会选择不同的值，把重复的数据记录只保留一份

2、UNION后的数据集会默认排序，如果我们需要按照需要进行排序的话，只用在最后一个SELECT语句中  加上排序语句如

1. SELECT　id,name FROM test1 
2. UNION 
3. SELECT　id,name FROM test2 ORDER BY name ASC;就会按照name进行升序排序 

 SELECT　id,name FROM test1
 UNION
  SELECT　id,name FROM test2 ORDER BY name ASC;就会按照name进行升序排序

3、UNION的结果列名会自动取第一个SELECT的列名作为返回数据集的列名与其他SELECT列名无关，因此其他数据列列名可省略是需要数据列数目相同，并且对应列的类型相同或者可以自动转换，但是实际应用中最好每个SELECT语句中加上对应的列名

  b)UNION  ALL

我们直接看下TEST1与TEST2使用UNION ALL的结果集：

 

1. SELECT　id,name FROM test1 
2. UNION ALL 
3. SELECT　id,name FROM test2 

 SELECT　id,name FROM test1
  UNION ALL
 SELECT　id,name FROM test2

其结果集如下：

             ID     NAME

     2     ahsely

     2     ahsely

     1     andy

     1     andy

     3     beta

     3     dona

  从查询的结果集我们可以得出以下结论：

  1、UINON ALL合并的两个SELECT数据集会把所有的记录都列出来，包括重复的数据

  2、UNION  ALL的结果集没有顺序，如果需要对结果集进行排序，需要在最后一个SELECT语句中使用ORDER BY 如：

1. SELECT　id,name FROM test1 
2.    UNION ALL 
3. SELECT　id,name FROM test2 ORDER BY　name; 

 SELECT　id,name FROM test1
    UNION ALL
 SELECT　id,name FROM test2 ORDER BY　name;

3、UNION ALL与UNION类似返回的结果集的列名与第一个SELECT列名相同，与其他SELECT的列名无关

因此在实际的开发和使用过程中，需要根据需求来确定使用UNION还是UNION ALL