JOIN或WHERE内的条件

Question

将条件放入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'

您更喜欢哪一个（也许是为什么）？

Answer 1

关系代数允许WHERE子句和中的谓词可互换INNER JOIN，因此即使INNER JOIN带有WHERE子句的查询也可以使谓词由优化器重新排列，以便在处理过程中将它们 排除在外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

但这当然取决于。