 |
|
IN vs. EXISTS SQL
Oracle Tips by Burleson Consulting
March 15, 2010
|
Question: I don’t understand
when I should use an IN subquery versus an EXISTS subquery. I have
tried it both ways and I get the same results, and both run fast.
Answer: As we know, SQL is
declarative and there are many equivalent ways to write a query that gives
the same results.
This is especially true for using the IN vs. the EXISTS
clause. The Oracle documentation notes that:
“If the selective predicate is in the subquery, then use IN. If the
selective predicate is in the parent query, then use EXISTS.”
Another rule of thumb was that if the subquery produced
a relatively small result set, you should use IN subquery rather and an
EXISTS subquery.
Prior to Oracle 10g this was a big issue and you could
get a really bad execution plan if you used an IN subquery instead of an
EXISTS subquery.
However, the Oracle optimizer has evolved, and as of
Oracle 10g the execution plans will be identical for both IN and EXISTS
subqueries.
