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'

您更喜欢哪一个（或许还有原因）？

关系代数允许谓词的互换性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

Write:

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

但这当然取决于情况。