Filtering using the JOIN instead of WHERE

Question

In SQL (MSSQL, Oracle, etc., whatever), when joining tables, what is the gain from adding a filter to the JOIN statement instead of having it in the WHERE clause?

i.e.

SELECT * FROM X INNER JOIN Y ON X.A = Y.A WHERE X.B = 'SOMETHING'

versus

SELECT * FROM X INNER JOIN Y ON X.A = Y.A AND X.B = 'SOMETHING'

I realize that this does not work in all cases, but I've noticed that in some cases there appears to be a performance gain by putting the filter criteria in the JOIN statement. However, since it's a part of the JOIN statement, it can also cause it to behave a little strangely.

Thoughts?

Answer 1

For INNER JOIN queries, the performance characteristics of these filters will depend on many factors - the size of the tables, indexing, the selectivity of the query, and other factors specific to the RDBMS on which the query is executed.

In LEFT and RIGHT OUTER JOIN, the position of the filter matters much more than INNER JOIN, since affects whether it will be applied before (JOIN clause) or after (WHERE clause) the join is carried out.

Answer 2

I sometimes do this in queries that have a lot of joins because it localises all the information about the join in one part of the query rather than having some in the join condition and some in the where clause.

Answer 3

For an INNER JOIN, I would not expect a performance difference, but rather that the same plan would be used whether the filter was in the JOIN...ON clause or the WHERE clause. I personally prefer to use write the join criteria in the JOIN clause and the filtering in the WHERE clause- a sort of way to stick all the "parameters" to the SQL statement in the same place- this isn't necessarily sensible or well-thought-out. Conversely, some people like to have everything in the JOIN clause to keep everything together.

The situation with outer joins is different- there is a significant difference between "a LEFT OUTER JOIN b ON a.a_id=b.a_id AND b.type = 1" and "a LEFT OUTER JOIN b ON a.a_id=b.a_id WHERE b.type=1"- in fact the latter is implicitly forcing an inner join. This would be another reason to put all such conditions in the JOIN clause, for consistency.