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: 600

Filed under:
|
|
|

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

Related posts about sql

Related posts about tsql