SQL : where vs. on in join
        Posted  
        
            by Erwin
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by Erwin
        
        
        
        Published on 2010-04-13T09:38:50Z
        Indexed on 
            2010/04/13
            9:43 UTC
        
        
        Read the original article
        Hit count: 673
        
Perhaps a dumb question, but consider these 2 tables :
T1
Store    Year
01  2009
02  2009
03  2009
01  2010
02  2010
03  2010
T2
Store
02
Why is this INNER JOIN giving me the results I want (filtering the [year] in the ON clause) :
select t1.*
from t1
inner join t2
on t1.store = t2.store
and t1.[year] = '2009' 
Store    Year
02  2009
And why the LEFT OUTER JOIN include records of year 2010 ?
select t1.*
from t1
left outer join t2
on t1.store = t2.store
and t1.year = '2009' 
where t2.store is null
01  2009
03  2009
01  2010
02  2010
03  2010
And I have to write the [year] filter in the 'WHERE' clause :
select t1.*
from t1
left outer join t2
on t1.store = t2.store
where t2.store is null
and t1.year = '2009' 
01  2009
03  2009
Like I said, perhaps a dumb question, but it's bugging me !
© Stack Overflow or respective owner