I have a relatively simple query joining two tables. The "Where" criteria can be expressed either in the join criteria or as a where clause. I'm wondering which is more efficient.
Query is to find max sales for a salesman from the beginning of time until they were promoted.
Case 1
select salesman.salesmanid, max(sales.quantity)
from salesman
inner join sales on salesman.salesmanid =sales.salesmanid
and sales.salesdate < salesman.promotiondate
group by salesman.salesmanid
Case 2
select salesman.salesmanid, max(sales.quantity)
from salesman
inner join sales on salesman.salesmanid =sales.salesmanid
where sales.salesdate < salesman.promotiondate
group by salesman.salesmanid
Note Case 1 lacks a where clause altogether
RDBMS is Sql Server 2005
EDIT
If the second piece of the join criteria or the where clause was sales.salesdate < some fixed date so its not actually any criteria of joining the two tables does that change the answer.
Best Answer
I wouldn't use performance as the deciding factor here - and quite honestly, I don't think there's any measurable performance difference between those two cases, really.
I would always use case #2 - why? Because in my opinion, you should only put the actual criteria that establish the JOIN between the two tables into the JOIN clause - everything else belongs in the WHERE clause.
Just a matter of keeping things clean and put things where they belong, IMO.
Obviously, there are cases with LEFT OUTER JOINs where the placement of the criteria does make a difference in terms of what results get returned - those cases would be excluded from my recommendation, of course.
Marc