>>
>>What I learned is that IN with a subquery is always very poor performance and should never be done. Always use Exists instead.
>>
>>
http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance>
>Christian, that line about "always very poor performance" is simply not true.
>
>Yes, there are benefits to using EXISTS - but for almost the last 10 years, the optimizer often will generate the same plan. If you'd read that full stackoverflow article (and many others out there), you'd see there can be several factors.
I had a really big problem with some queries that were ran on MySQL (perhaps two years ago) and that IN clause just killed the process. It was a huge problem for the client and lots of frustration until the cause was found. Since then I prefer Exists to be on the good side. Is there a situation where IN() with subquery would absolutely give you a better result compared with Exists()? Then I would rethink my strategy.
Christian Isberner
Software Consultant