Minus,intersect and UNION ALL of SQL statements

The three keyword in the SQL statement:minus(minus),INTERSECT(intersection), and UNIONAll (set);

About the concept of the collection, the middle school should have learned, not much to say. These three keyword mainly operate on the query results of the database, as in Chinese meaning: two queries, minus is the result of subtracting the second query from the results of the first query, assuming that there is an intersection minus the intersecting part; 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 such a problem, or use Minus,intersect and union ALL to solve this problem, or facing the business of millions of data, the number of queries, database server is not the dead of my play?

PS: Apply two sets of subtract, intersect and add, there are strict requirements: 1. The fields of two sets must be clear (with * not, error); 2. The field type and order are the same (the 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, suppose to sort the results, be able to set a query after the set operation, and then sort, as the previous sample 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