union 和 union all的区别

union 和 union all的区别

相同点和不同点

相同点：
union和union all 都是对于多个查询结果的并集进行操作
不同点：
1.union 不会输出两个结果并集的重复行
2.union all 会输出两个结果并集的重复行

实验表

字段解释：
xh：学号
xh：姓名
nl：年龄

create table student(xh number,xm varchar2(4),nl int);insert into student values(1,'A',21);insert into student values(2,'B',21);insert into student values(3,'A',21);insert into student values(4,'A',21);insert into student values(5,'A',21);insert into student values(6,'C',21);insert into student values(7,'B',21);

查看表

SQL> select * from student;    XH XM           NL---------- ------------ ----------     1 A            21     2 B            21     3 A            21     4 A            21     5 A            21     6 C            21     7 B            217 rows selected.SQL> 

例子

union

SQL> select * from student  2  union  3  select * from student where xm='A';    XH XM           NL---------- ------------ ----------     1 A            21     2 B            21     3 A            21     4 A            21     5 A            21     6 C            21     7 B            217 rows selected.SQL> 

union all

SQL> select * from student  2  union all  3  select * from student where xm='A';    XH XM           NL---------- ------------ ----------     1 A            21     2 B            21     3 A            21     4 A            21     5 A            21     6 C            21     7 B            21     1 A            21     3 A            21     4 A            21     5 A            2111 rows selected.SQL>