Difference between Union and Union all

If we have a table student that contains the following fields and data:

 

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, 'damn', 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, 'jar', 90); Commit;

 

First, let's take a look at the example of union:

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 73sql>

 

Assume that two result sets are connected by Union all. The result is as follows:

 

SQL> select * 2 from student 3 where ID <4 4 union all 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 3 Cindy 89 4 Damon 90 5 Ella 736 rows selected.

 

We can see that one of the differences between Union and Union all lies in the processing of repeated results.

 

Next, let's exchange the order of two select statements to see how the results are.

 

SQL> select * 2 from student 3 where ID> 2 and ID <6 4 Union 5 select * 6 from Student 7 where ID <4 8; ID name score ---------- ---------------------------- ---------- 1 Aaron 78 2 Bill 76 3 Cindy 89 4 Damon 90 5 Ella 73sql> select * 2 from student 3 where ID> 2 and ID <6 4 union all 5 select * 6 from Student 7 where ID <4 8; ID name score ---------- ------------------------------ ---------- 3 Cindy 89 4 Damon 90 5 Ella 73 1 Aaron 78 2 Bill 76 3 Cindy 896 rows selected.

 

We can see that for union, the results are still the same after the two select statements are exchanged, because union will sort the statements by itself. The results of Union all are not the same after the SELECT statement order is exchanged, because union all does not sort the results by itself.

 

So what is this automatic sorting rule? Let's take a look at the order of the selected fields after the SELECT statement (select * is equivalent to select ID, name, score) to see how the results are:

 

SQL> select score, ID, name 2 from student 3 where ID <4 4 Union 5 select score, ID, name 6 from Student 7 where ID> 2 and ID <6 8; score ID name ---------- ------------------------------ 73 5 Ella 76 2 Bill 78 1 Aaron 89 3 Cindy 90 4 Damon

 

However, we can see that the result is sorted by the field score (the previous select * is sorted by ID ).

 

Some people may ask, if I want to control sorting by myself, can I use order? Of course. Note the following points in writing:

 

Select score, ID, namefrom studentwhere ID> 2 and ID <7 unionselect score, ID, namefrom studentwhere id <4 unionselect score, ID, namefrom studentwhere ID> 8 order by ID DESC

 

The order by clause must be written in the last result set, and its sorting rules will change the sorting result after the operation. Valid for Union, Union all, intersect, and minus.

 

Other set operators, such as intersect and minus, are basically the same as Union. Here we will summarize them together:

 

Union: Performs Union operations on the two result sets, excluding repeated rows, and sorts the default rules at the same time;

Union all: Union operation on the two result sets, including repeated rows without sorting;

Intersect performs intersection operations on the two result sets, excluding repeated rows, and sorts the default rules at the same time;

Minus performs the Difference Operation on the two result sets, excluding repeated rows, and sorts the default rules at the same time.

 

You can specify the order by clause in the last result set to change the sorting method.

 

 

 

Difference between Union and Union all