SQL : where vs. on in join
- by Erwin
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 !
