The difference between union and union all

The similarities and differences between union and union all

Similarities:

Both union and union all operate on the union of multiple query results

Differences:

1.union will not output duplicate rows with two results union.

2.union all will output duplicate rows with two results joined.

Experimental table

Field explanation:

Xh: student number

Xh: first name

Nl: age

Create table student (xh number,xm varchar2 (4), nl int); insert into student values (1); insert into student values (2); insert into student values (3); insert into student values (4); insert into student values (5); insert into student values (6); insert into student values (7)

View tabl

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

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 217 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 >