SQL JOIN在哪里放置WHERE条件? [英] SQL JOIN where to place the WHERE condition?

问题描述

我有两个以下示例.

1.示例(WHERE)

SELECT 1
  FROM table1 t1
  JOIN table2 t2 ON t1.id = t2.id
 WHERE t2.field = true

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?

推荐答案

如果过滤器在功能上进入JOIN条件(即它是实际的联接条件，而不仅仅是过滤器)，则它必须出现在该联接的子句.

If a filter enters in a JOIN condition functionally (i.e. it is an actual join condition, not just a filter), it must appear in the ON clause of that join.

值得注意的是:

• 如果将其放在WHERE子句中，则联接为INNER时的性能相同，否则会有所不同.正如评论中所提到的，这并不重要，因为无论如何结果都是不同的.

• If you place it in the WHERE clause instead, the performances are the same if the join is INNER, otherwise it differs. As mentioned in the comments it does not really matter since anyway the outcome is different.

在确实是OUTER JOIN条件的情况下将过滤器放在WHERE子句中会隐式取消条件的OUTER性质(即使没有记录也要联接")，因为这些过滤器暗示存在该条件必须首先是现有记录.示例:

Placing the filter in the WHERE clause when it really is an OUTER JOIN condition implicitely cancels the OUTER nature of the condition ("join even when there are no records") as these filters imply there must be existing records in the first place. Example:

... table1 t LEFT JOIN table2 u ON ... AND t2.column = 5是正确的

... table1 t LEFT JOIN table2 u ON ... 
WHERE t2.column = 5 

是不正确的，因为t2.column = 5告诉引擎期望来自t2的记录，这与外部联接背道而驰.例外是IS NULL过滤器，例如WHERE t2.column IS (NOT) NULL(实际上这是构建条件外部联接的便捷方法)

is incorrect, as t2.column = 5 tells the engine that records from t2 are expected, which goes against the outer join. Exception to this would be an IS NULL filter, such as WHERE t2.column IS (NOT) NULL (which is in fact a convenient way to build conditional outer joins)

• LEFT和RIGHT联接隐式地是OUTER联接.

• LEFT and RIGHT joins are implicitely OUTER joins.

希望有帮助.

这篇关于SQL JOIN在哪里放置WHERE条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！