Vishal Vishal - 1 year ago 87
SQL Question

SQL Joins Vs SQL Subqueries (Performance)?

I wish to know if I have a join query something like this -

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

and a subquery something like this -

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

When I consider performance which of the two queries would be faster and why ?

Also is there a time when I should prefer one over the other?

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me tools i should use to measure performance of two queries. Thanks a lot!

Answer Source

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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download