Shulou(Shulou.com)05/31 Report--

What this article shares to you is about what is the difference between in and exists in Oracle. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

I. the first case

Select * from A where mm in (select mm from B)

Efficient. The index on large table An is used here.

Select * from B exists (select mm from A where mm=B.mm)

Efficient. The index on small table B is used here.

II. The second case

Select * from B where mm in (select mm from A)

Inefficient. The index on small table B is used here.

Select * from An exists (select mm from B where mm=A.mm)

Efficient. The index on large table An is used here.

Third, the third situation

Not exists will still use the index on the table when using it, but not in will do a full scan

Therefore, not exists is always more efficient than not in.

IV. The fourth case

In and = = have the same effect.

These are the differences between in and exists in Oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.