Condition within JOIN or WHERE

Question

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

For example...

-- 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)?

Answer 1

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

In some cases that includes making the INNER JOIN relatively "incomplete" and putting some of the condition in the WHERE clause just to make the lists of filtering conditions more easily managable.

As for example, instead of using the below code:

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

Use this code:

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