SQL: where vs. on in conjunction

Question

Perhaps a dumb question, but consider these two tables:

T1                       
Store    Year
01  2009
02  2009
03  2009
01  2010
02  2010
03  2010

T2
Store
02

Why does this INNER JOIN give me the results I want (filtering [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 does LEFT OUTER JOIN include 2010 records?

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 filter [year] in the "WHERE" section:

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

As I said, maybe a dumb question, but that overhears me!

Answer 1

If you are by definition LEFT JOIN, what

• for each row from the left table, it will return the corresponding row from the right table, if exists
• If the right side of the row does not exist, it will still return a row from the table on the left with all columns from the table on the right set to NULL

, ON, true, .

, t1.year - 2010, on false (x AND 2010 = 2009 - false , t1.year = 2010), , ( ).

, .

( , select)

EDIT: , where JOIN

select t1.*
from t1
left outer join t2
on t1.store = t2.store
or t1.year = '2009'
where t2.store is null

:

t1.store t1.year t2.store t1.store=t2.store t1.year=2009  join(OR)
01       2009    02       false             true          true
02       2009    02       true              true          true
03       2009    02       false             true          true
01       2010    02       false             false         false
02       2010    02       true              false         true
03       2010    02       false             false         false

, false, null t2. *.

, , , , (- , ), ( ).

- - .

ORs , AND (OR , AND). , .

, t1.year = '2009' where, ( ), , , , , , . t1, 2009 ( , , WHERE).

.

, - , t1.year = '2009' ( ). , , LEFT JOIN where t1.year = 2009 AND t2.store is null , .

Answer 2

, , ( .) , " t2.store null" "t2.Store" . , , :

01 2009 null
02 2009 02
03 2009 null
01 2010 null
02 2010 null
03 2010 null

, , t2.store null, , , (, ).