Analysis of MINUS, INTERSECT and UNION ALL of SQL statements

Three keywords in the SQL statement:MINUS(subtraction),INTERSECT(intersection) andUNIONALL (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,MINUSis 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.INTERSECTis two The intersection of query results,UNION ALLis 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!