Oracle result set operation information

Several result set operation commands that are well known for the operation information of oracle result set are tested in detail today. Some problems are found and the test preparation is recorded. Www.2cto.com 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; • difference between Union and Union All. Select * from student where id <4 union select * from student where id> 2 and id <6 results will be 1 Aaron 78 2 Bill 76 3 Cindy 89 4 Damon 90 5 Ella 73 If use Union All 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. One of the differences between Union and Union All lies in processing repeated results. Next, we will adjust the order of the two subqueries, change to -- Union select * from student where id> 2 and id <6 union select * from student where id <4 to see if the execution result is consistent with your expectation? -- Union All select * from student where id> 2 and id <6 union all select * from student where id <4 What about this? Based on this, we can see 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, name from student where id> 2 and id <6 union select score, id, name from student where id <4 results: 73 5 Ella 76 2 Bill 78 1 Aaron 89 3 Cindy 90 4 Damon is expected to be in the order of fields. 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, name from student where id> 2 union select score, id, name from student where id <4 results: 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 can see that for the same score record, sort 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, 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 order by clause must be written in the last result set, the sorting rule changes the sorting result after the operation. Valid for Union, Union All, Intersect, and Minus. Note: 1. Union can merge result sets with different field names but with the same data type. 2. If the result sets with different field names are Union, the Order by clause of this field will be invalid. ========================================================== ======== The operations of Intersect and Minus are basically the same as those of Union, here we will summarize: Union, which performs Union operations on the two result sets, excluding duplicate rows, and sorts the default rules at the same time. Union All performs Union operations on the two result sets, contains duplicate rows without sorting. Intersect performs intersection operations on the two result sets, excluding duplicate rows, and sorts the default rules at the same time. Minus performs difference operations on the two result sets, duplicate rows are not included, and default rules are sorted at the same time. You can specify the Order by clause in the last result set to change the sorting method.