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.