Which is faster – IN or EXISTS?

A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS?

The short answer, post-Oracle 9i is:
Both are pretty much the same!

Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

That used to be true in the pre-9i versions of Oracle.

The recommendation at that time were:

• If the majority of the filtering criteria is in the subquery, use IN.
• If the majority of the filtering criteria is in the main query, use EXISTS.

In other words,

• IN for big outer query and small inner query.
• EXISTS for small outer query and big inner query.

If both the outer query and inner query were large, either could work well – the choice would depend on indexes and other factors.

In current versions of Oracle, the optimizer (CBO) costs both to produce the best plan.

So which should I use now – IN or EXISTS?

Use whichever makes logical sense in the context. Or whichever is your personal favorite. It’s pretty much the same thing.

Read this topic from AskTom: IN vs EXISTS for more.