is it better to put more logic in your ON clause or should it only have the minimum necessary?

Question

Given these two queries:

Select t1.id, t2.companyName 
from table1 t1 
  INNER JOIN table2 t2 on t2.id = t1.fkId 
WHERE t2.aField <> 'C' 

OR:

Select t1.id, t2.companyName 
from table1 t1 
  INNER JOIN table2 t2 on t2.id = t1.fkId  and t2.aField <> 'C'

Is there a demonstrable difference between the two? Seems to me that the clause "t2.aField <> 'C'" will run on every row in t2 that meets the join criteria regardless. Am I incorrect?

Update: I did an "Include Actual Execution Plan" in SQL Server. The two queries were identical.

Answer 1

I prefer to use the Join criteria for explaining how the tables are joined together. So I would place the additional clause in the where section.

I hope (although I have no stats), that SQL Server would be clever enough to find the optimal query plan regardless of the syntax you use.

HOWEVER, if you have indexes which also have id, and aField in them, I would suggest placing them together in the inner join criteria.

It would be interesting to see the query plan's in these 2 (or 3) scenarios, and see what happens. Nice question.

Answer 2

There is a difference. You should do an EXPLAIN PLAN for both of the selects and see it in detail.

As for a simplier explanation: The WHERE clause gets executed only after the joining of the two tables, so it executes for each row returned from the join and not nececerally every one from table2.

Performance wise its best to eliminate unwanted results early on so there should be less rows for joins, where clauses or other operations to deal with later on.

In the second example, there are 2 columns that have to be same for the rows to be joined together so it usually will give different results than the first one.