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