Forum Discussion

saravanakumar28's avatar
saravanakumar28
Copper Contributor
Jun 13, 2024

Performance Tuning

What is the difference between ON clause and WHERE clause in joining condition 

Example 

select * from tableA JOIN  tableB on tableA.ID=tableB.ID where tableA.ID=1 

select * from tableA JOIN tableB on tableA.ID=1 and tableA.ID=tableB.ID 

My doubt is how this condition work in background and what are all the pros while  using the condition in ON clause and WHERE clause in sql server 

1 Reply

Sort By
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 14, 2024

    saravanakumar28 , check the execution plan of both queries if there is a difference; there won't be one.

    You used only the keyword "JOIN" without anything else, so it's an implicit "INNER JOIN"; here it makes no difference where you put the filter on "Id".

    But it makes a big difference if you use an "OUTER JOIN" = LEFT/RIGTH.

     

     

Resources