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
Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)
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!