IN,EXISTS or INNER JOIN - which one is the best (performance wise)

hi all,
when we compare IN,EXISTS or INNER JOIN with performance wise which one is the best?
Thanks
Tharindu Dhaneenja

hi Frank Kalis,
Thanks. nop i didn't face and issue but during my working Exp. i have noticed IN operator performance wise not that much good compare with EXISTS and JOIN.so that's way i have create post using this topic.do you have any comments on that.thanks for the advice.
Thanks
Tharindu Dhaneenja

A lot depends on the indexing. No proper indexes, then none of them will perform terribly well.
The consensus appears to be that JOIN operations are optimized, so in many situations you can just go with a JOIN. And of course if you need to return column values from the joined table, then EXISTS or IN is not an option.
If you need to add DISTINCT, which is a relatively costly operation, and you don't need to return column values from the joined table, then EXISTS might offer better performance (certainly not worse).