union all和union的区别

来源：互联网发布：临床医学生必备软件编辑：程序博客网时间：2024/06/02 00:46

假设我们有一个表Student，包括以下字段与数据：
[c-sharp] view plaincopyprint?
01.drop table student;
02.
03.create table student
04.(
05.id int primary key,
06.name nvarchar2(50) not null,
07.score number not null
08.);
09.
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;
drop table student;
create table student
(
id int primary key,
name nvarchar2(50) not null,
score number not null
);
insert into student values(1,'Aaron',78);
insert into student values(2,'Bill',76);
insert into student values(3,'Cindy',89);
insert into student values(4,'Damon',90);
insert into student values(5,'Ella',73);
insert into student values(6,'Frado',61);
insert into student values(7,'Gill',99);
insert into student values(8,'Hellen',56);
insert into student values(9,'Ivan',93);
insert into student values(10,'Jay',90);
commit;
首先，我们来看一下UNION的例子：
[c-sharp] view plaincopyprint?
01.SQL> select *
02. 2 from student
03. 3 where id<4
04. 4 union
05. 5 select *
06. 6 from student
07. 7 where id>2 and id<6
08. 8 ;
09.
10.        ID NAME                                SCORE
11.---------- ------------------------------ ----------
12.         1 Aaron                                  78
13.         2 Bill                                   76
14.         3 Cindy                                  89
15.         4 Damon                                  90
16.         5 Ella                                   73
17.
18.SQL>
SQL> select *
2 from student
3 where id<4
4 union
5 select *
6 from student
7 where id>2 and id<6
8 ;

        ID NAME                                SCORE
---------- ------------------------------ ----------
         1 Aaron                                  78
         2 Bill                                   76
         3 Cindy                                  89
         4 Damon                                  90
         5 Ella                                   73
SQL>
如果换成Union All连接两个结果集，则结果如下：
[c-sharp] view plaincopyprint?
01.SQL> select *
02. 2 from student
03. 3 where id<4
04. 4 union all
05. 5 select *
06. 6 from student
07. 7 where id>2 and id<6
08. 8 ;
09.
10.        ID NAME                                SCORE
11.---------- ------------------------------ ----------
12.         1 Aaron                                  78
13.         2 Bill                                   76
14.         3 Cindy                                  89
15.         3 Cindy                                  89
16.         4 Damon                                  90
17.         5 Ella                                   73
18.
19.6 rows selected.

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

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