WHERE子句与使用JOIN时的ON有什么区别？

Question

假设我有以下的T-SQL代码：

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId;
WHERE b.IsApproved = 1;

以下代码也会返回相同的一组行：

SELECT * FROM Foo f
INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);

这可能不是最好的示例，但这两者之间有性能差异吗？

- tugberk

---

这里有一个类似的问题：https://dev59.com/NHE85IYBdhLWcg3w_I78 - Mario S

机器将会自动计算并适当优化它。但是，为了那些在未来几年需要调试/修改/支持你的代码的人类，请将过滤条件放在WHERE子句中，将连接条件放在ON子句中。 - KM.

@KM。我并不总是知道如何区分连接条件和过滤器之间的区别。例如，在这个答案中，我认为最好在连接中使用，那么这是“连接条件”吗？在这里有另一个例子，我甚至不知道如何重写等价的where子句。 - Conrad Frix

连接条件是：tableA.column = tableB.column，过滤条件是：tableA.Column=5。在执行外连接（LEFT/RIGHT JOIN）时，你必须将过滤条件放在ON语句中，或者使用以下方式编写WHERE语句：(tableA.Column=5 OR tableA.Column IS NULL)。 - KM.

Answer 1

只需小心与外连接的差异。当在JOIN的ON条件中添加一个过滤器b.IsApproved（在右表Bar上）的查询时：

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); 

NOT的含义并不等同于将筛选器放在WHERE子句中：

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved = 1); 

对于'failed'的 outer join 到 Bar（即没有b.BarId与f.BarId匹配的情况），所有这些失败连接的行中的 b.IsApproved 都将被置为NULL，然后过滤掉这些行。

另一种看待这个问题的方式是，在第一个查询中，LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) 总是会返回左表的每一行，因为LEFT OUTER JOIN保证即使连接失败，也会返回左表的行。然而，在连接条件中添加 (b.IsApproved = 1) 的效果是，当 (b.IsApproved = 1) 为 false 时，将右表的任何列设置为 NULL，即按照同样应用于 LEFT JOIN 条件的规则 (b.BarId = f.BarId)。

更新：为了回答Conrad提出的问题，可选过滤器的等效LOJ如下所示：

SELECT * 
FROM Foo f 
LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId)
WHERE (b.IsApproved IS NULL OR b.IsApproved = 1);

WHERE 子句中需要同时考虑联接失败的情况（NULL），以及应该忽略筛选条件的情况，以及联接成功但必须应用筛选条件的情况。(b.IsApproved 或者 b.BarId 可能会被测试为空)

我已经准备好了一个SqlFiddle演示页面，其中演示了在JOIN相对于b.IsApproved筛选器的不同放置位置之间的差异。

- StuartLC

---

非常好的观点。如果您将外连接的筛选条件测试数据放入外连接本身中，您将获得比预期更多的行，因为所有的Foos都将被返回，而不考虑Bar的状态或存在性。当过滤器与连接分别指定时，两个表的行首先被连接，然后过滤器从未满足条件的表中删除整个行。 - KeithS

如果您将第二个查询的WHERE子句更正为WHERE b.IsApproved = 1 or b.BarId is Null，那么它们就是相同的了。现在您打算使用哪一个？ - Conrad Frix

@nonnn 嗯，在左连接版本中，您不需要 OR (b.BarId IS NULL)，只有在 WHERE 版本中才需要，并且您希望使它相同。 - Conrad Frix

Answer 2

不，查询优化器足够智能，会为这两个示例选择相同的执行计划。

您可以使用 SHOWPLAN 来检查执行计划。

---

尽管如此，您应该将所有连接放在ON子句中，将所有限制放在WHERE子句中。

- aF.

---

抢先一步了。虽然出于个人偏好，我会选择JOIN，因为它更具描述性。 - Ste

谢谢！想象一下有7或8个INNER JOIN的情况。你的答案也适用于这些情况吗？ - tugberk

在我的看法中，把所有的东西都放在“JOIN”里面实际上更加令人困惑。使用“JOIN”来关联查询中的表格，使用“WHERE”来过滤结果。当你混合使用两者并且只使用其中之一时，查询就会变得难以阅读。 - Yuck

@Yuck。很公正的观点，我同意混合变得难以管理。 - Ste

@Ste：实际上，我通常更喜欢使用JOIN和WHERE的混合方式，前提是在查询编写中强制执行每个关键字的目的。JOIN子句确定如何将表链接到“宽”结果集中，然后WHERE子句确定所述结果的过滤。鉴于此，我觉得使用JOIN和WHERE的查询比仅使用JOIN的查询更容易解读，就像使用JOIN比仅使用WHERE子句定义连接和筛选条件的查询更容易解读一样。 - KeithS

@KeithS：我想我从来没有真正监控过我的使用情况。稍后我会回报并开始一个关于它的聊天。 :) - Ste

Answer 3

SELECT * FROM Foo f
INNER JOIN Bar b ON b.BarId = f.BarId
WHERE b.IsApproved = 1;

这是更好的格式选择。它易于阅读和修改。在商业领域，这是您需要选择的格式。就性能而言，它们是相同的。

- Landin Martens

---

在我的当前情况下，我更喜欢使用WHERE子句，但不禁想知道是否存在性能差异。谢谢！ - tugberk

Answer 4

我刚刚对四个表进行了一个查询测试 - 一个主表与三个INNER JOIN，并且总共有四个参数，并比较了两种方法的执行计划（使用JOIN中的筛选条件，然后也在WHERE子句中使用）。

执行计划完全相同。我是在SQL Server 2008 R2上运行的。

- Jason L

Answer 5

我曾经看到过一些情况，即使在最新版本的MSSQL上，优化器也不够聪明 - 性能差异非常大。

但这是个例外，大多数情况下SQL Server优化器都会解决问题并得到正确的计划。

因此，在需要时使用WHERE子句上的过滤器并进行优化。

- Fabricio Araujo