Minus,intersect and UNION ALL of SQL statements

Source: Internet
Author: User


Three keywords in the SQL statement:minus (minus),INTERSECT (intersection), and UNION all (unions);

About the concept of the set, the middle school should have learned, not much to say. These three keywords are mainly for the database query results to operate, as its Chinese meaning: two queries, Minus is from the first query results minus the second query results, if there is the intersection of the part minus the intersection; Otherwise, there is no difference from the results of the first query. Intersect is the intersection of two query results, and UNION All is a set of two queries;

Although the same function can be implemented with simple SQL statements, but the performance difference is very large, someone did experiment: Made_order a total of 230,000 records, charge_detail a total of 170,000 records:

SELECT order_id from Made_order
Minus
SELECT order_id from Charge_detail
Time: 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
)
Time: 18.19 sec
Performance Difference 15.956 times times! So in the face of this problem, or with the Minus,intersect and union all to solve the problem, otherwise facing the business of millions of data on the query, the database server is not the death of my play?

PS: Apply two sets of subtract, intersect and add, there are strict requirements: 1. The fields of the two collection must be clear (with * will not, error); 2. The field type and order (name can be different), such as: The field 1 of the Set 1 is number, the field 2 is varchar, Then the field 1 of collection 2 must also be number, and field 2 must be varchar;3. Cannot be sorted, if you want to sort the results, you can set a query after the set operation, and then sort, as 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

Minus,intersect and UNION ALL of SQL statements

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.