SQL Server Performance

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

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Dhaneenja, Nov 25, 2010.

  1. Dhaneenja New Member

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

    Welcome to the forum!
    You are asking a very broad question to which there is no general answer. Are you facing any concrete issues?
  3. Dhaneenja New Member

    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
  4. Adriaan New Member

    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).
  5. satya Moderator

Share This Page