SQL Filter criteria in join criteria or where clause which is more efficient

Viewed 24589

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.

9 Answers
Related