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