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 条件(即它是一个实际的连接条件，而不仅仅是一个过滤器)，它必须出现在该连接的 ON 子句中.

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子句中，如果join是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屋！