Comparative Analysis of Oracle in and exists not in and not exists

Let's sum up and answer these two common questions that most netizens are concerned about.

Differences between in and exist

From the perspective of SQL programming, in is intuitive, and exists is not intuitive. There is one more select,
In can be used for subqueries, while exists seems to be used only for subqueries.

In terms of performance
Exists uses loop. The number of cycles has a large impact. The number of external records must be small, so it doesn't matter if the internal table is used.

In uses hash join, so if the inner table is small, the entire query range will be very small. If the inner table is large, and the External table is very slow, in this case, exists is faster than in.

Difference between not in and not exists

Not in internal and external tables are all scanned, and no index is used;
Not extsts subqueries can use table indexes.

Therefore, we recommend that you use not exists instead of not in.

However, if it is exists and in, it depends on the situation.

Specific instances and execution plans are provided for time.