Home > Database > Mysql Tutorial > body text

Analysis of MINUS, INTERSECT and UNION ALL of SQL statements

一个新手
Release: 2017-10-17 10:10:33
Original
1874 people have browsed it

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
Copy after login

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
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!