Thursday, August 20, 2015

Difference between filtering joins in the ON and Where clauses

Recollecting what I learned about the topic:

For Inner Joins with filters in the ON or Where clauses, the result is the same - but filtering within the ON clause restricts the row set that will be input to the next Sql engine step (where filter, here), making it more performance-wise.

The results differ in outer joins - where you filter the table you are outer joining in the ON clause.
I found a good and concise explanation here.

The great thing about this article is that it explores an example of filtering the table that is to be outer joined (i.e., all its records should be returned).

This, together with the piece of information that outer joins are logically executed after inners, means that whenever you filter within the ON clause, in such an outer join, your filter is ultimately ignored/discarded, because the outer joining will occur afterwards anyway, "bringing back" all the outer joining table records to the result set. And with no Where clause left to filter, this outer join with no where clause shows more records than the one with a where clause.

No comments:

Post a Comment