Oracle minus usage details and application examples

Oracle minus usage

"Minus" is directly translated into Chinese as "minus". It is also used for subtraction operations in Oracle, but it is not the subtraction of numbers in the traditional sense, but Is the subtraction of the query result set. A minus B means the result of removing all records contained in result set B from result set A, that is, records that exist in A but not in B. The algorithm is similar to RemoveAll() of Collection in Java, that is, A minus B will only remove the intersection of A and B. No operations will be performed on records that exist in B but not in A, and no exception will be thrown. .

Oracle’s minus is compared by column, so the prerequisite for A to minus B is that result set A and result set B need to have the same number of columns, and the columns with the same column index have the same data type. . In addition, Oracle will deduplicate the result set after minus. That is, if there are originally multiple identical records in A, only one corresponding record will remain after A minus B. For details, please see the example below.

Let’s look at an example of practical application of minus. Suppose we have a user table t_user with the following record data:

Then:

(1) The result of "select id from t_user where id<6 minus select id from t_user where id between 3 and 7" will be:

(2 ) The result of "select age,level_no from t_user where id<8 minus select age,level_no from t_user where level=3" is:

Seeing such a result, maybe You may feel a little strange, why is this? Let's analyze it. First, the result of "select age,level_no from t_user where id<8" will be like this:

Then, "select age,level_no from t_user where level=3" The result will be like this:

Then, directly after A minus B, the result should be:

At this time , we can see that there are duplicate records in the result set, and after deduplication, the above actual results are obtained. In fact, this is easy to understand, because the function of minus is to find the records that exist in A but not in B.

The above examples are all for a single table. Obviously, there is no advantage in using minus for single table operations. It is usually used to find out that some fields in table A do not exist in table B. Corresponding to the recorded situation. For example, we have another table t_user2, which has the same table structure as the t_user table. Then the following statement can find the records that exist in the t_user table but do not exist in the t_user2 table except for id.

select no,name,age,level_no from t_user minus select no,name,age,level_no from t_user2;

Thank you for reading, I hope it can help everyone, thank you for your support of this site!

For more detailed explanations of Oracle minus usage and application examples, please pay attention to the PHP Chinese website!