JOIN 或 WHERE 内的条件 [英] Condition within JOIN or WHERE

问题描述

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

Is there any difference (performance, best-practice, etc...) between putting a condition in the JOIN clause vs. the WHERE clause?

例如...

-- 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'

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

Which do you prefer (and perhaps why)?

推荐答案

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

The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so even INNER JOIN queries with WHERE clauses can have the predicates rearrranged by the optimizer so that they may already be excluded during the JOIN process.

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

I recommend you write the queries in the most readable way possible.

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

Sometimes this includes making the INNER JOIN relatively "incomplete" and putting some of the criteria in the WHERE simply to make the lists of filtering criteria more easily maintainable.

例如，代替:

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

但这当然取决于.

这篇关于JOIN 或 WHERE 内的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！