What's the difference between where clause and on clause when table left join?

Question

SQL1:

select t1.f1,t2.f2 
from t1 
   left join t2 on t1.f1 = t2.f2 and t1.f2=1 and t1.f3=0 

SQL2:

select t1.f1,t2.f2 
from t1 
  left join t2 on t1.f1 = t2.f2 
where t1.f2=1 and t1.f3=0

The difference is where and on clause, is there same return result? and what's the difference ? does DBMS run them in same way? thanks.

Answer 1

The where clause applies to the whole resultset; the on clause only applies to the join in question.

In the example supplied, all of the additional conditions related to fields on the inner side of the join - so in this example, the two queries are effectively identical.

However, if you had included a condition on a value in the table in the outer side of the join, it would have made a significant difference.

You can get more from this link: http://ask.sqlservercentral.com/questions/80067/sql-data-filter-condition-in-join-vs-where-clause

For example:

select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 and t2.f4=1

select t1.f1,t2.f2 from t1 left join t2 on t1.f1 = t2.f2 where t2.f4=1

- do different things - the former will left join to t2 records where f4 is 1, while the latter has effectively been turned back into an inner join to t2.