Hello everyone, meet again. I'm Jun Quan. I wish every programmer can learn more languages.
If we have a table Student, it contains the following fields and data:
drop table student;</p> <p>create table student<br />(<br />id int primary key,<br />name nvarchar2(50) not null,<br />score number not null<br />);</p> <p>insert into student values(1,'Aaron',78);<br />insert into student values(2,'Bill',76);<br />insert into student values(3,'Cindy',89);<br />insert into student values(4,'Damon',90);<br />insert into student values(5,'Ella',73);<br />insert into student values(6,'Frado',61);<br />insert into student values(7,'Gill',99);<br />insert into student values(8,'Hellen',56);<br />insert into student values(9,'Ivan',93);<br />insert into student values(10,'Jay',90);</p> <p>commit;<br />
First, let's take a look at the example of UNION:
SQL> select *<br /> 2 from student<br /> 3 where id<4<br /> 4 union<br /> 5 select *<br /> 6 from student<br /> 7 where id>2 and id<6<br /> 8 ;</p> <p> ID NAME SCORE<br />-—- ----- -—-<br /> 1 Aaron 78<br /> 2 Bill 76<br /> 3 Cindy 89<br /> 4 Damon 90<br /> 5 Ella 73</p> <p>SQL>
Assuming that Union All connects two result sets, the results are as follows:
SQL> select *<br /> 2 from student<br /> 3 where id<4<br /> 4 union all<br /> 5 select *<br /> 6 from student<br /> 7 where id>2 and id<6<br /> 8 ;</p> <p> ID NAME SCORE<br />-—- ----- -—-<br /> 1 Aaron 78<br /> 2 Bill 76<br /> 3 Cindy 89<br /> 3 Cindy 89<br /> 4 Damon 90<br /> 5 Ella 73</p> <p>6 rows selected.
It can be seen that one of the differences between Union and Union All lies in the processing of repeated results.
Next, we exchange the order of one or two SELECT statements to see how the results are.
SQL> select *<br /> 2 from student<br /> 3 where id>2 and id<6<br /> 4 union<br /> 5 select *<br /> 6 from student<br /> 7 where id<4<br /> 8 ;</p> <p> ID NAME SCORE<br />-—- ----- -—-<br /> 1 Aaron 78<br /> 2 Bill 76<br /> 3 Cindy 89<br /> 4 Damon 90<br /> 5 Ella 73</p> <p>SQL> select *<br /> 2 from student<br /> 3 where id>2 and id<6<br /> 4 union all<br /> 5 select *<br /> 6 from student<br /> 7 where id<4<br /> 8 ;</p> <p> ID NAME SCORE<br />-—- ----- -—-<br /> 3 Cindy 89<br /> 4 Damon 90<br /> 5 Ella 73<br /> 1 Aaron 78<br /> 2 Bill 76<br /> 3 Cindy 89</p> <p>6 rows selected.
It can be seen that for UNION, the results are still the same after exchanging the order of two SELECT statements, because UNION will actively sort itself. The results of UNION ALL are different after exchanging the order of SELECT statements, because UNION ALL will not actively sort the results.
So what is the rule of self active sorting? Let's exchange the order of the selected fields after SELECT (the previous use of SELECT * is equivalent to SELECT ID,NAME,SCORE), and see how the results are:
SQL> select score,id,name<br /> 2 from student<br /> 3 where id<4<br /> 4 union<br /> 5 select score,id,name<br /> 6 from student<br /> 7 where id>2 and id<6<br /> 8 ;</p> <p> SCORE ID NAME<br />-—- -—- -----<br /> 73 5 Ella<br /> 76 2 Bill<br /> 78 1 Aaron<br /> 89 3 Cindy<br /> 90 4 Damon
However, you can see that the results are sorted according to the field SCORE (the previous SELECT * is sorted according to the ID).
Then someone will ask, if I want to control sorting by myself, can I use ORDER BY? Of course. There are only some points that should be paid attention to in writing:
select score,id,name<br />from student<br />where id > 2 and id < 7</p> <p>union</p> <p>select score,id,name<br />from student<br />where id < 4</p> <p>union</p> <p>select score,id,name<br />from student<br />where id > 8<br />order by id desc<br />
The order by clause must be written in the last result set, and its sorting rule will change the sorting result after the operation. It is valid for Union, Union All, Intersect and Minus.
The operations of other set operators, such as Intersect and Minus, are basically the same as those of Union. Here is a summary:
Union, which performs union operation on two result sets, does not contain repeated rows, and sorts by default rules at the same time;
Union All, union operation of two result sets, including repeated rows, without sorting;
Intersect: perform intersection operation on two result sets, excluding repeated rows, and sort by default rules at the same time;
Minus: perform the difference operation on the two result sets, excluding repeated rows, and sort by the default rule at the same time.
You can specify the Order by clause in the last result set to change the sorting method.
Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/118098.html Original link: https://javaforall.cn