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 21
7 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 21
7 rows selected.
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 21
11 rows selected.
SQL>