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 it returning same result? and what's the difference? does DBMS run them in same way? Thanks.
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.