2

Is there and advantage to one of the following options over the other or are they the same ?

select * 
from T1 
Join T2 
   on T2.id=T1.t2_id 
   and T2.field=value;

vs.

select * 
from T1 
Join T2 
  on T2.id=T1.t2_id 
where T2.field=value;

And in general - are there performance (dis)advantages to placing the conditions within the JOIN ON statement as opposed to the WHERE statement ?

  • 1
    Try them out and check the query plan. AFAIK SQL Server treats both as the same – Akash Mar 19 '15 at 19:25
  • 1
    The execution plan will be exactly the same. I don't recommend placing conditions in joins though. If you or any other developer decides to change the INNER JOIN to LEFT JOIN they might be surpirised that the query works differently. – BuahahaXD Mar 19 '15 at 20:46
  • @Łukasz Kastelik can you please explain what would be the difference if we change to a left join please ? – epeleg Mar 21 '15 at 19:19
  • 2
    @epeleg I can try to do it but I suggest you read the following post (at least the 2 top answers): stackoverflow.com/questions/1018952/… – BuahahaXD Mar 21 '15 at 20:47
  • Thanks, that was a realy good pointer.I read all of them and found the third one (stackoverflow.com/a/1019432/25412) to be the most informative IMHO. I wrote an answer to my own Q based on that Answer. – epeleg Mar 23 '15 at 12:55
2

When using inner joins there is no semantic difference between the two, and it would usually be make sense to put join related conditions in the JOIN and filtering ones in the WHERE clause.

However, when using outer joins the semantics are not the same. for example: in a left join - rows from the left table will be returned even if there the JOIN ON includes some limitation on the fields from right table that are not met. but if the same limitation would be moved to the WHERE clause - the (already Left Joined) row from the left table that has no matching row on the right table will be discarded.

So for INNER JOIN - it does not matter - for OUTER JOIN - you should think what exactly do you want to get.

Note that in some cases this is actually desirable. e.g. - In Queries such as:

SELECT *   
FROM T1 LEFT JOIN T2 ON T2.id = T1.t2_id   
WHERE T2.id IS NULL ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.