FIltering on the join?

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.del开发者_Python百科eted = 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?

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.

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).

No there is no differences between these two, because in the logical processing of the query, WHERE will always go right after filter clause(ON), in your examples you will have:

1. Cartesian product (number of rows from TableA x number of rows from TableB)
2. Filter (ON)
3. Where.

Your examples are in ANSI SQL-92 standard, you could also write the query with ANSI SQL-89 standard like this:

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

THIS IS TRUE FOR INNER JOINS, WITH OUTER JOINS IS SIMILAR BUT NOT THE SAME