Union, UNION All, Intersect, minus[in Oracle)

It is well known that several result set collection operations commands, tested today in detail, found some problems and recorded the preparation.

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 (, ' Jay ', 90);

Commit

• The difference between Union and union all.

SELECT * FROM student where ID < 4

Union

SELECT * FROM student where ID > 2 and ID < 6

The result will be

1 Aaron 2 Bill 3 Cindy 4 Damon 5 Ella 73

If you switch to union ALL to connect two result sets, the result is:

1 Aaron 2 Bill 3 Cindy 3 Cindy 4 Damon 5 Ella 73

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

Next we will adjust the order of the two sub-queries, instead

--union

SELECT * FROM student where ID > 2 and ID < 6

Union

SELECT * FROM student where ID < 4

See if the results are consistent with your expectations?

--union All

SELECT * FROM student where ID > 2 and ID < 6

UNION ALL

SELECT * FROM student where ID < 4

Well, what about this one?

Thus we know that the difference between the two is the processing of sorting. UNION ALL organizes the data in the order in which it is associated, and the Union is sorted according to certain rules. So what's the rule? Let's look at it in a different way:

Select Score,id,name from student where ID > 2 and ID < 6

Union

Select Score,id,name from student where ID < 4

The results are as follows:

5 Ella 2 Bill 1 Aaron 3 Cindy 4 Damon

Consistent with what we expected: the order of the fields will be sorted. Before our query was based on the Id,name,score field order, the result set would be sorted by ID precedence, and now the new field order would have changed the ordering of the query results. And, is a,b,c by the given field ... ORDER BY. The result is Order by a,b,c ..... Of Let's look at the next query:

Select Score,id,name from student where ID > 2

Union

Select Score,id,name from student where ID < 4

The results are as follows:

8 Hellen 6 Frado 5 Ella 2 Bill 1 Aaron 3 Cindy 4 Damon 90 Ten Jay 9 Ivan 7 Gill

As you can see, for score the same record, it will be sorted by the next field ID. If we want to control the sort by ourselves, is it possible to specify it with order by? The answer is yes, but there are areas to note:

Select Score,id,name from student where ID > 2 and ID < 7

Union

Select Score,id,name from student where ID < 4

Union

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 sorted result after the operation. Valid for union, union all, Intersect, minus.

=============================================================================================================== ==

The operation of intersect and minus is basically consistent with the Union, and here we summarize:

Union, which is combined with two result sets, excluding duplicate rows, and sorting the default rules;

Union all, which is set up for two result sets, including repeating rows , without sorting ;

Intersect, the intersection of two result sets, excluding duplicate rows, and the sorting of the default rules;

Minus, a differential operation on two result sets, excluding duplicate rows, and sorting the default rules.

You can specify the ORDER BY clause in the last result set to change the ordering method

Union, UNION All, Intersect, minus[in Oracle)