The difference between Union and union all

Suppose we have a table student that includes the following fields and data:

drop table student; CREATE TABLE student (ID int primary key, name NVARCHAR2 NOT NULL, score number is 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 (A, ' Jay ', 90); Commit

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

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 2 Bill 3 Cindy 4 Damon 5 Ella SQ. L>

If you switch to union ALL to connect two result sets, 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 2 Bill 3 Cindy 3 Cindy 4 Damon 90 5 Ella 6 rows selected.

As you can see, one of the differences between Union and union all is the processing of duplicate results.

Next, we swap the order of the two SELECT statements to see what 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 2 Bill 3 Cindy 4 Damon 5 Ella SQ. L> 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 4 Damon 5 Ella 1 Aaron 2 Bill 76 3 Cindy 6 rows selected.

As you can see, for union, the result of swapping two SELECT statements is still the same, because union is sorted automatically. The union all, when the order of the SELECT statement is exchanged, results differently, because union all does not automatically sort the results.

So what are the rules for automatic sorting? Let's switch to select the order of the fields (previously using SELECT * 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--------------------------------------------------5 Ella 2 Bill 1 Aaron 3 Cindy 4 Damon

However, you can see that the results are sorted by field score (the previous select * is sorted by id).

Then someone would ask, if I want to control the sort by myself, can I use the order by? Of course. However, there are some areas to be noted in the wording:

Select Score,id,name from student where ID > 2 and IDS < 7 Union select Score,id,name from student where ID < 4 UN Ion Select Score,id,name from student where ID > 8 ORDER BY id DESC

The ORDER BY clause must be written in the last result set, and its collation will change the result of the order after the operation. For union, union all, Intersect, minus are all valid.

Other set operators, such as Intersect and minus, are basically consistent with the Union, and here's a summary of the following:

Union, the two result sets are combined to set operations, excluding duplicate rows, while the default rules are sorted;

Union all, which is a set of two result sets, including duplicate rows, not sorted;

Intersect, the two result sets are intersected, excluding duplicate rows, while the default rules are sorted;

Minus, the two result sets are poorly manipulated, excluding duplicate rows, while the default rules are sorted.

You can specify an ORDER BY clause in the last result set to change the sort method.