JOIN或WHERE中的条件

在JOIN子句和WHERE子句中放置条件之间有什么区别(性能、最佳实践等)吗?

例如……

-- Condition in JOIN
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = 'John'


-- Condition in WHERE
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE CUS.FirstName = 'John'

你更喜欢哪一个(也许为什么)?

356268 次浏览
小开

WHERE将在JOIN发生后进行筛选。

在JOIN上的过滤器，以防止在JOIN过程中添加行。

小开

我更喜欢使用JOIN来连接完整的表/视图，然后使用WHERE来引入结果集的谓词。

它在语法上感觉更简洁。

小开

对我来说，在join中放置条件似乎是“语义上错误的”，因为这不是join的“目的”。但这是非常定性的。

另外一个问题:如果您决定从内部连接切换到(比如说)右连接，将条件设置在join内部可能会导致意想不到的结果。

小开

大多数RDBMS产品将以相同的方式优化这两个查询。在Peter Gulutzan和Trudy Pelzer的“SQL性能调优”中，他们测试了多个RDBMS品牌，并没有发现性能差异。

我倾向于将连接条件与查询限制条件分开。

如果你在使用OUTER JOIN，有时有必要在join子句中放入条件。

小开

在筛选连接时，我通常看到性能有所提高。特别是如果可以联接两个表的索引列。大多数查询都这样做，您应该能够减少逻辑读取，在大容量环境中，这是一个比执行时间更好的性能指标。

当有人展示他们的SQL基准测试时，他们在午夜在开发服务器上执行了一个sproc的两个版本5万次，并比较了平均时间，我总是觉得有点好笑。

小开
最佳答案

关系代数允许WHERE子句和INNER JOIN子句中的谓词互换性，因此即使带有WHERE子句的INNER JOIN查询也可以由优化器重新排列谓词，以便在JOIN过程中可能已经被排除在外了。

我建议您以最易读的方式编写查询。

有时，这包括使INNER JOIN相对“不完整”，并将一些标准放在WHERE中，只是为了使过滤条件列表更容易维护。

例如，不要:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
AND c.State = 'NY'
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
AND a.Status = 1

写:

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
AND a.Status = 1

当然，这要视情况而定。

小开

在我看来，当你有一个更大的表时，连接更快。这真的没有太大的区别，特别是如果你处理的是一个相当小的桌子。当我第一次学习连接时，有人告诉我，连接中的条件就像where子句中的条件一样，如果where子句特定于在哪个表上执行条件，那么我可以互换地使用它们。

小开

对于内部连接，我并没有注意到有什么不同(但是与所有性能调优一样，您需要在您的条件下检查数据库)。

但是，如果使用左连接或右连接，将条件放在哪里会产生巨大的差异。例如，考虑以下两个查询:

SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate >'20090515'


SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >'20090515'

第一个将只给您那些订单日期晚于2009年5月15日的记录，从而将左连接转换为内连接。

第二个将提供这些记录加上任何没有订单的客户。根据放置条件的位置不同，结果集有很大不同。(选择*仅供示例使用，当然您不应该在产品代码中使用此选项。)

这种情况的例外情况是，您希望只查看一个表中的记录，而不查看另一个表中的记录。然后使用where子句作为条件，而不是连接。

SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null
小开

最好在Join中添加条件。性能比可读性更重要。对于大型数据集，这很重要。

小开

同意第二多数投票的答案，使用LEFT JOIN或RIGHT JOIN会有很大的不同。实际上，下面两种说法是等价的。所以你可以看到AND子句在JOIN之前进行筛选，而WHERE子句在JOIN之后进行筛选。

SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >'20090515'


SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN (SELECT * FROM dbo.Orders WHERE OrderDate >'20090515') AS ORD
ON CUS.CustomerID = ORD.CustomerID

提交答案