Performance differences between Oracle IN and NOT IN

The business problem can be roughly described as follows: A parent table and a sub-table, the query result is to find the records in the sub-table that do not use the parent table id. In this case, it is estimated that many systems will be involved. Let's take an example:

Table 1: parent table parent

Table 2: child table childen

The parent table stores the parent table, the child table stores the child table, and then uses the pid to associate with the parent table. The result of the query is to find the father who does not have the child.

Let's take a look at the syntax of the query statement:

Select * from parent where id not in (select pid from childen)

When the sub-table contains 0.5 million records, the query time exceeds 10 seconds, which is far more than one second on the original SQL server. I came up with a solution:

Select * from parent where id in

(Select id from parent minus select pid from childen)

Under normal understanding, this statement should be more time-consuming, but the fact is totally unexpected. This statement is not only fast when the sub-table has a large number of records, in the case of a small amount of data in a sub-table, the speed is also very good, basically completed within 1 second.