Union, Union all, intersect, and minus in Oracle

Several well-known result set operation commands have been tested in detail today, and some problems have been found.

Suppose we have a table student, which includes 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,'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(10,'Jay',90);commit; 

The difference between Union and Union All.

select *from studentwhere id < 4unionselect *from studentwhere id > 2 and id < 6 

The result will be

1 Aaron 78

2 Bill 76

3 Cindy 89

4 Damon 90

5 Ella 73

If Union all is used to connect two result sets, the returned result is:

1 Aaron 78

2 Bill 76

3 Cindy 89

3 Cindy 89

4 Damon 90

5 Ella 73

As you can see,One of the differences between Union and Union All lies in processing repeated results.

Next we will change the order of the two subqueries

--Unionselect *from studentwhere id > 2 and id < 6unionselect *from studentwhere id < 4 

Check whether the execution result is consistent with your expectation?

--Union Allselect *from studentwhere id > 2 and id < 6union allselect *from studentwhere id < 4 

What about this?

Based on this, we know that,The second difference lies in the processing of sorting.Union all organizes data according to the associated order, while union will sort data according to certain rules. So what is this rule? Let's change the query method to see:

select score,id,namefrom studentwhere id > 2 and id < 6unionselect score,id,namefrom studentwhere id < 4 

The result is as follows:

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

As we expected, it will be sorted by field order. Previously, our query is based on the field order of ID, name, and score. The result set is sorted by ID first. The new field order also changes the sorting of the query results. In addition, it is the order by according to the order of the given fields a, B, c. That is, the result is order by a, B, c. Let's look at the next query:

select score,id,namefrom studentwhere id > 2unionselect score,id,namefrom studentwhere id < 4 

The result is as follows:

56 8 hellen

61 6 Frado

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

90 10 Jay

93 9 Ivan

99 7 Gill

As you can see, records with the same score are sorted by the id of the next field. If we want to control the sorting by ourselves, can we use order by to specify it? The answer is yes, but note the following points in writing:

select score,id,namefrom studentwhere id > 2 and id < 7unionselect score,id,namefrom studentwhere id < 4unionselect score,id,namefrom studentwhere id > 8order 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.

Note:

1, Union can beThe field names are different, but the data types are the same.;

2. IfDifferent Field NamesThe result setThe order by clause will be invalid..

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

The operations of Intersect and Minus are basically the same as those of Union. Here we will summarize the following:

Union: Perform Union operations on two result sets, excluding duplicate rows, and sort the default rules at the same time;

Union All: Perform Union operations on two result sets,Including duplicate rows,Do not sort;

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

Minus performs the Difference Operation on two result sets, excluding duplicate rows and sorting by default rules.

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

Blog Source:Http://www.cnblogs.com/RobertLee/archive/2008/03/05/898115.html