Comparative analysis of the Oracle in and exists not in and not exists _oracle

The two very universal netizens more concerned about the question summary answer.

The difference between in and exist

From the perspective of SQL programming, in intuitive, exists not intuitive more than one select,
In can be used for various subqueries, and exists seems to be used only for associative subqueries

From the performance point of view
Exists is the way to loop, the number of times the impact of the cycle, the appearance to record a few, the table does not matter

In with a hash join, so if the inner table is small, the entire query range will be very small, if the table is very large, the appearance if also very big is very slow, at this time exists will really faster in the way.

Difference between not in and not exists

All table scans are performed on the outside of the inside, and no index is used;
A subquery of not extsts can use an index on a table.

Therefore, it is recommended to use not exists instead of

But if it's exists and in, it's going to be specific.

Have time to use specific examples and implementation plans to explain.