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. Oracle will figure out the most efficient way to run the query, either way.
Read this topic from AskTom: IN vs EXISTS for more.
For further reading
- What are INNER JOIN and OUTER JOIN?
- What are CBO and RBO?
- The Difference between Views and Materialized Views
- NOCOPY Parameter Hint in PL/SQL
- The Magic of ROWNUM
{ 1 comment… read it below or add one }
I didn’t know this behavior of IN vs EXISTS had changed. Looked up asktom which confirms it. Good article. Lots of myths flying around still about this topic, I’ll point this out to my team. Thanks.