Search code examples
sqlperformancesql-server-2008subqueryjoin

Which of the join and subquery queries would be faster and why? When I should prefer one over the other?

I have a join query

Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

and a subquery query

Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

Which would be faster and why?

When I should prefer one over the other?

Solution

  • I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).

    As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.

    The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!