IN operator in Oracle SQL

[ily_saravanan]

Hi,

I have one doubts in using the IN operator in the subquery from the performance point of view. If I use the IN operator, somebody is saying that it should not be used, instead exists operator can be used.

Please let me know if any body knows that why we should not use IN operator

Thanks in advance

Regards,
Saravanan.P

[krishnaindia2007]

Hi,

I have one doubts in using the IN operator in the subquery from the performance point of view. If I use the IN operator, somebody is saying that it should not be used, instead exists operator can be used.

Please let me know if any body knows that why we should not use IN operator

Thanks in advance

Regards,
Saravanan.P

IN or EXISTS which gives better performance depends upon the situation.

While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.

Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes
sense.

But in general IN is slower as the sub query need to be resolved before it can be tested where as Exists fire off the equivalent query as soon as single row is returned.

[CompSuyGuy]

Great points Krishna Indian. Just wanted to expand just a little bit on your points.

As far as situations dictating which is faster, is absolutely right. And it definitely is whether the subquery runs first or with the outer query. And even then there are exceptions as krishna pointed out a few.

If the subquery is a correlated subquery, such as it depends on a value from that of the outer query.

i.e.
(Select column1, column2, column3 from outer_table where column1 IN (select somecolumn from inner_table where join_column = outer_table.column3).

Here you see the value that gets returned by the inner column is actually dependent on the outer column. EXISTS do the subquery regardless, unless if the optimizer is smart enough to understand that the exists clause you put in does is not a correlated subquery, but i don't know if any databases are that smart yet, Oracle might be.

Normally it is thought to be that the correlated subqueries are slower, however, if you use indexed fields then that speeds it up dramatically. So really there is no direct answer, but just scenarios that you have to judge for yourself. And please point out anything if you deem is incorrect. Thank you.