6

I see a lot of places suggesting using INNER JOIN's instead of writing IN CLAUSES because of performance reasons. None of them explain why, though. Can someone provide a detailed explanation why that is?

bjnr
  • 3,353
  • 1
  • 18
  • 32
Glide
  • 20,235
  • 26
  • 86
  • 135
  • that is not actually true, in most cases that i have seen, they perform the same, these type of issues are very data/schema related and it is not like one size fits all thing, so if there is a problem you should check the execution plans and see what is causing a problem. – Aram Dec 11 '13 at 23:20
  • 2
    This is implementation dependant. Some products optimise correlated sub queries badly and always use nested loops. Others don't. Often they don't have the same semantics anyway unless `DISTINCT` is added to the `JOIN` version. – Martin Smith Dec 11 '13 at 23:22
  • Try googling, someone already asked this: http://stackoverflow.com/questions/5274618/inner-join-and-where-in-clause-performance – John Gibb Dec 11 '13 at 23:23
  • Related, if not exact duplicate: http://stackoverflow.com/q/2577174/102937 – Robert Harvey Dec 11 '13 at 23:24
  • 2
    Read this article very informative sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join and also http://explainextended.com/2009/06/16/in-vs-join-vs-exists/ – M.Ali Dec 11 '13 at 23:58

0 Answers0