Filtering JOINs: WHERE vs. ON
join, sql, sql-server-2008, where-clause
Solution
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.
Problem
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.