SQL JOIN 在哪里放置 WHERE 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15483808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL JOIN where to place the WHERE condition?
提问by Aley
I have two following examples.
我有以下两个例子。
1. Example (WHERE)
1. 示例（WHERE）
SELECT 1
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
WHERE t2.field = true
2. Example (JOIN AND)
2. 示例（JOIN AND）
SELECT 1
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id AND t2.field = true
What is the faster way in terms of performance? What do you prefer?
就性能而言，什么是更快的方法？你喜欢哪个？
采纳答案by Sebas
If a filter enters in a JOINcondition functionally (i.e. it is an actual join condition, not just a filter), it must appear in the ONclause of that join.
如果过滤器在JOIN功能上进入一个条件（即它是一个实际的连接条件，而不仅仅是一个过滤器），它必须出现在该ON连接的子句中。
Worth noting:
值得注意：
If you place it in the
WHEREclause instead, the performances are the same if the join isINNER, otherwise it differs. As mentioned in the comments it does not really matter since anyway the outcome is different.Placing the filter in the
WHEREclause when it really is anOUTER JOINcondition implicitely cancels theOUTERnature of the condition ("join even when there are no records") as these filters imply there must be existing records in the first place. Example:
如果你把它放在
WHERE子句中，如果连接是INNER，则表现相同，否则不同。正如评论中提到的，这并不重要，因为无论如何结果是不同的。当过滤器
WHERE确实是一个OUTER JOIN条件时，将过滤器放在子句中会隐式取消条件的OUTER性质（“即使没有记录也加入”），因为这些过滤器首先意味着必须存在现有记录。例子：
... table1 t LEFT JOIN table2 u ON ... AND t2.column = 5is correct
... table1 t LEFT JOIN table2 u ON ... AND t2.column = 5是正确的
... table1 t LEFT JOIN table2 u ON ...
WHERE t2.column = 5
is incorrect, as t2.column = 5tells the engine that records from t2 are expected, which goes against the outer join. Exception to this would be an IS NULLfilter, such as WHERE t2.column IS (NOT) NULL(which is in fact a convenient way to build conditional outer joins)
是不正确的，因为它t2.column = 5告诉引擎来自 t2 的记录是预期的，这与外部联接背道而驰。对此的例外是IS NULL过滤器，例如WHERE t2.column IS (NOT) NULL（这实际上是构建条件外连接的便捷方法）
LEFTandRIGHTjoins are implicitelyOUTERjoins.
LEFT和RIGHT连接是隐式OUTER连接。
Hope it helped.
希望它有所帮助。
回答by MPelletier
JOINconditions should normally be independent from filter conditions. You define rules of your join (the how) with ON. You filter whatyou want with WHERE. Performance wise, there's no general rule across all engines and designs, so your mileage will vary greatly.
JOIN条件通常应独立于过滤条件。你定义你的加入规则（如何）ON。你过滤什么你想WHERE。性能方面，所有引擎和设计都没有通用规则，因此您的行驶里程会有很大差异。
回答by Artemination
I think the faster way is to put the filter in the where clause, because it will procees that filter in the where first , and then the join clause, so there will be no need of permutation of filters.
我认为更快的方法是将过滤器放在 where 子句中，因为它将首先在 where 子句中处理过滤器，然后在 join 子句中，因此不需要过滤器的排列。
