Analysis of MINUS, INTERSECT and UNION ALL of SQL statements

Three keywords in the SQL statement: MINUS (subtraction), INTERSECT (intersection) and UNION ALL (union);

Everyone should have learned the concept of sets in middle school, so I won’t go into details. These three keywords are mainly used to operate on the query results of the database, just like their Chinese meanings: two queries, MINUS is to subtract the second query result from the first query result. If there is an intersection, subtract the intersection part; otherwise, there is no difference from the first query result. INTERSECT is two The intersection of query results, UNION ALL is the union of two queries;

Although the same function It can be implemented using simple SQL statements, but the performance difference is very big. Someone has done experiments: made_order has a total of 230,000 records, charge_detail has a total of 170,000 records:

    SELECT order_id FROM made_order
　　MINUS
　　SELECT order_id FROM charge_detail
耗时:1.14 sec
　　
　　SELECT a.order_id FROM made_order a
　　 WHERE a.order_id NOT exists (
　　   SELECT order_id
　　   FROM charge_detail
　　   WHERE order_id = a.order_id
　　 )
耗时:18.19 sec

The performance difference is 15.956 times! So when encountering this When this kind of problem arises, it is better to use MINUS, INTERSECT and UNION ALL to solve the problem. Otherwise, in the face of millions of data queries that can be seen everywhere in the business, the database server will not be beaten to death by us?

PS: When applying the subtraction, intersection and addition of two sets, there are strict requirements: 1. The fields of the two sets must be clear (* will not work, an error will be reported); 2. Field type and order The same (the names can be different), for example: field 1 of set 1 is NUMBER and field 2 is VARCHAR, then field 1 of set 2 must also be NUMBER and field 2 must be VARCHAR; 3. It cannot be sorted. If you want to sort the results, you can After the set operation, set a query outside and then sort. For example, the previous example can be changed to:

SELECT * FROM 
   (SELECT order_id FROM made_order
　　 MINUS
　　SELECT order_id FROM charge_detail)
ORDER BY ORDER_ID ASC

The above is the detailed content of Analysis of MINUS, INTERSECT and UNION ALL of SQL statements. For more information, please follow other related articles on the PHP Chinese website!