过滤联接:WHERE与ON [英] Filtering JOINs: WHERE vs. ON
问题描述
我对使用ON与WHERE进行过滤联接的理解是否正确?
Is my understand of filtering joins using ON vs. WHERE correct?
...将过滤联接表的结果，这意味着LEFT JOIN或RIGHT JOIN不会在预期表中显示所有记录，因为即使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仅在连接两个表时用于比较两个表之间的字段，但对于要连接的特定表，它也可以充当孤立的WHERE.
...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.
当您仅连接两个表时，这些都不是真正重要的事情，但是我已经意识到，在3个以上的表之间进行非常大的联接时，理解差异至关重要.
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.
推荐答案
The distinction between conditions in the ON clause and the WHERE clause is certainly a grey one.
对于内部联接，它们是等效的.对于OUTER JOINS，您的理解是正确的，即WHERE子句在概念上是在评估ON条件之后应用的.
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.
但是在许多情况下，差异更多是出于意图，而不是功能. ON条件和WHERE子句之间通常存在语义差异.
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.
例如，较旧版本的SQL Server确实确实使用WHERE子句中的条件实现了ON语法，采用*=或=*语法表示LEFT或RIGHT连接(这导致与在某些情况下，LEFT和RIGHT JOIN等效)
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)
通常，我的建议是始终根据记录相互关联的逻辑，使用ON子句中的适当键字段来联接记录.使用WHERE子句可以应用逐渐限制此结果集的过滤条件.
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.
这篇关于过滤联接:WHERE与ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
