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, 'ivany', 93 );
Insert into student values (10, '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 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

-- Union

Select *
From student
Where ID> 2 and ID <6

Union

Select *
From student
Where id <4

Check whether 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 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, name
From student
Where ID> 2 and ID <6

Union

Select score, ID, name
From student
Where 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, name
From student
Where ID> 2

Union

Select score, ID, name
From student
Where 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, 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 sorting rules will change the sorting result after the operation. Valid for Union, Union all, intersect, and minus.

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

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.