FIltering on the join?

Question

Is there any argument, performance wise, to do filtering in the join, as opposed to the WHERE clause?

For example,

SELECT blah FROM TableA a INNER JOIN TableB b ON b.id = a.id AND b.deleted = 0 WHERE a.field = 5 

As opposed to

SELECT blah FROM TableA a INNER JOIN TableB b ON b.id = a.id WHERE a.field = 5   AND b.deleted = 0 

I personally prefer the latter, because I feel filtering should be done in the filtering section (WHERE), but is there any performance or other reasons to do either method?

Answer 1

If the query optimizer does its job, there is no difference at all (except clarity for others) in the two forms for inner joins.

That said, with left joins a condition in the join means to filter rows out of the second table before joining. A condition in the where means to filter rows out of the final result after joining. Those mean very different things.

Answer 2

With inner joins you will have the same results and probably the same performance. However, with outer joins the two queries would return different results and are not equivalent at all as putting the condition in the where clause will in essence change the query from a left join to an inner join (unless you are looking for the records where some field is null).