Filtering JOINs: WHERE vs. ON

Question

Is my understand of filtering joins using ON vs. WHERE correct?

---

WHERE

...will filter the result of the joined tables, meaning a LEFT JOIN or RIGHT JOIN won't display all records in the intended table because they will be filtered out even though the WHERE filter might be on a field in the other table.

ON

...can be used as a filter for the table being joined. I used to think ON was only used to compare fields between two tables when joining them, but it can also act like an isolated WHERE for the specific table being joined.

---

None of this really matters when you are only joining two tables, but I have come to realize that understanding the difference is critical when doing very large joins across 3+ tables.

Answer 1

The link @Gratzy provided is helpful

The distinction between conditions in the ON clause and the WHERE clause is certainly a grey one.

For INNER JOINS they are equivalent. For OUTER JOINS, your understanding is correct that the WHERE clause is conceptually applied after the ON condition has been evaluated.

But in many cases the difference is more down to intent than functionality. There is often a semantic difference between the ON conditions and the WHERE clause.

For example, older versions of SQL Server really did implement the ON syntax using conditions in the WHERE clause, employing a *= or =* syntax to imply LEFT or RIGHT joins (which led to oddly subtly different results form the LEFT and RIGHT JOIN equivalents in some cases)

In general, my advice is always to use the appropriate key fields in the ON clause to join records based on the logic of how the records associate with each other. Use the WHERE clause to apply filtering conditions that progressively restrict this result set.