Problem
Is there a difference between putting a condition in the JOIN clause vs. the WHERE clause in terms of performance, best-practice, and so on?
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 one do you favor (and why)?
Asked by Steve Dignan
Solution #1
Because the predicates in the WHERE clause and the INNER JOIN can be interchanged thanks to relational algebra, even INNER JOIN queries containing WHERE clauses can have the predicates rearranged by the optimizer such that they are already rejected during the JOIN process.
I recommend that you write the queries in the clearest possible manner.
Making the INNER JOIN relatively “incomplete” and putting some of the criteria in the WHERE is sometimes done just to make the filtering criteria lists easier to maintain.
As an example, instead of saying:
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
But, of course, it depends.
Answered by Cade Roux
Solution #2
I haven’t observed much of a change in inner joins (but as with all performance tuning, you need to check against your database under your conditions).
If you’re using left or right joins, though, where you put the condition matters a lot. Consider the following two questions:
SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate >'20090515'
SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >'20090515'
The first will return only records with an order date after May 15, 2009, effectively turning the left join into an inner join.
The second will include such records as well as any clients who have placed no orders. Depending on where you put the condition, the results are significantly varied. (Select * is solely for demonstration purposes; you should not use it in production code.)
The only time this isn’t true is when you only want to see the records in one table and not the other. The condition is then handled by the where clause rather than the join.
SELECT *
FROM dbo.Customers AS CUS
LEFT JOIN dbo.Orders AS ORD
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null
Answered by HLGEM
Solution #3
The majority of RDBMS solutions will optimize both queries in the same way. Peter Gulutzan and Trudy Pelzer examined different RDBMS brands and found no performance differences in their book “SQL Performance Tuning.”
I prefer to keep query restriction criteria and join conditions separate.
When utilizing an OUTER JOIN, it’s sometimes important to provide conditions in the join clause.
Answered by Bill Karwin
Solution #4
After the JOIN, WHERE will filter the results.
To prevent rows from being inserted during the JOIN operation, use a filter on the JOIN.
Answered by TheTXI
Solution #5
I prefer to use the Link to join full tables/Views and then use the WHERE to filter the results. To introduce the resulting set’s predicate.
It has a cleaner syntactic feel to it.
Answered by Johnno Nolan
Post is based on https://stackoverflow.com/questions/1018952/condition-within-join-or-where