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

User · Answer

It is better to add the condition in the Join  Performance is more important than readability  For large datasets  it matters

User · Answer

I prefer the JOIN to join full tables Views and then use the WHERE To introduce the predicate of the resulting set   It feels syntactically cleaner

User · Answer

For inner joins I have not really noticed a difference  but as with all performance tuning  you need to check against your database under your conditions   However where you put the condition makes a huge difference if you are using left or right joins  For instance consider these two queries  SELECT   FROM dbo Customers AS CUS  LEFT JOIN dbo Orders AS ORD  ON CUS CustomerID   ORD CustomerID WHERE ORD OrderDate  gt  20090515   SELECT   FROM dbo Customers AS CUS  LEFT JOIN dbo Orders AS ORD  ON CUS CustomerID   ORD CustomerID AND ORD OrderDate  gt  20090515   The first will give you only those records that have an order dated later than May 15  2009 thus converting the left join to an inner join  The second will give those records plus any customers with no orders  The results set is very different depending on where you put the condition   Select   is for example purposes only  of course you should not use this in production code   The exception to this is when you want to see only the records in one table but not the other  Then you use the where clause for the condition not the join  SELECT   FROM dbo Customers AS CUS  LEFT JOIN dbo Orders AS ORD  ON CUS CustomerID   ORD CustomerID WHERE ORD OrderID is null

User · Answer

Putting the condition in the join seems  semantically wrong  to me  as that s not what JOINs are  for   But that s very qualitative   Additional problem  if you decide to switch from an inner join to  say  a right join  having the condition be inside the JOIN could lead to unexpected results

User · Answer

WHERE will filter after the JOIN has occurred   Filter on the JOIN to prevent rows from being added during the JOIN process

User · Answer

Most RDBMS products will optimize both queries identically   In  SQL Performance Tuning  by Peter Gulutzan and Trudy Pelzer  they tested multiple brands of RDBMS and found no performance difference   I prefer to keep join conditions separate from query restriction conditions   If you re using OUTER JOIN sometimes it s necessary to put conditions in the join clause

User · Answer

Agree with 2nd most vote answer that it will make big difference when using LEFT JOIN or RIGHT JOIN  Actually  the two statements below are equivalent  So you can see that AND clause is doing a filter before JOIN while the WHERE clause is doing a filter after JOIN  SELECT   FROM dbo Customers AS CUS  LEFT JOIN dbo Orders AS ORD  ON CUS CustomerID   ORD CustomerID AND ORD OrderDate  gt  20090515   SELECT   FROM dbo Customers AS CUS  LEFT JOIN  SELECT   FROM dbo Orders WHERE OrderDate  gt  20090515   AS ORD  ON CUS CustomerID   ORD CustomerID

User · Answer

I typically see performance increases when filtering on the join   Especially if you can join on indexed columns for both tables   You should be able to cut down on logical reads with most queries doing this too  which is  in a high volume environment  a much better performance indicator than execution time   I m always mildly amused when someone shows their SQL benchmarking and they ve executed both versions of a sproc 50 000 times at midnight on the dev server and compare the average times

User · Answer

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   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   For example  instead of   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 it depends  of course

User · Answer

Joins are quicker in my opinion when you have a larger table  It really isn t that much of a difference though especially if you are dealing with a rather smaller table  When I first learned about joins  i was told that conditions in joins are just like where clause conditions and that i could use them interchangeably if the where clause was specific about which table to do the condition on

[sql] Condition within JOIN or WHERE

Examples related to sql

Examples related to performance