Oracle Union Usage

1. [SQL] View plaincopyprint?
   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 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 78
   2 Bill 76
   3 Cindy 89
   4 Damon 90
   5 Ella 73
   
   If you switch to union ALL to connect two result sets, the 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 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
   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.
   
   Attention:
   
   1,union can merge result sets with different field names but with the same data type;
   
   2, if the field name has a different result set to union, then the ORDER BY clause for this field will be invalidated.
   
   =============================================================================================================== ==
   
   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.
   
   The ORDER BY clause can be specified in the last result set to change the ordering method.

Oracle Union Usage