SQL statement minus, intersect, and Union all

 

Three keywords in an SQL statement:Minus(Minus ),Intersect(Intersection) andUnion all(Union );

 

I should have learned the concept of collection in middle school. these three keywords are mainly used to operate the database query results, just as in the Chinese meaning: For two queries, minus subtract the second query result from the first query result, if there is an intersection part, the intersection part is subtracted; otherwise there is no difference with the first query result. intersect is the intersection of two query results, and Union all is the union of the two queries;

 

Although the same functions can be implemented using simple SQL statements, the performance difference is very large. Some people have done experiments: made_order has a total of 0.23 million records, and charge_detail has a total of 0.17 million records:

Select order_id from made_order
Minus
Select order_id from charge_detail
Time consumption: 1.14 Sec

Select a. order_id from made_order
Where a. order_id not exists (
Select order_id
From charge_detail
Where order_id = A. order_id
)
Time consumption: 18.19 Sec
Performance is 15.956 times different! Therefore, minus, intersect, and Union all are used to solve this problem. Otherwise, you can query millions of data volumes that can be seen everywhere in your business, isn't the database server overwhelmed?

PS: apply two sets of subtraction, intersection and addition, there are strict requirements: 1. the fields of the Two sets must be clear (if * is used, an error is returned); 2. the field type and sequence are the same (the name can be different). For example, if field 1 of Set 1 is number and field 2 is varchar, Field 1 of Set 2 must be number, field 2 must be varchar; 3. sorting is not allowed. If you want to sort the results, you can set another query on the outside after the set operation, and then sort the results. For example, the preceding example can be changed:

Select * from
(Select order_id from made_order
Minus
Select order_id from charge_detail)
Order by order_id ASC