Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.7k points)

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

Related questions

0 votes
1 answer
asked Jul 13, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Oct 7, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer

31k questions

32.9k answers

500 comments

693 users

Browse Categories

...