left outer join的正确写法

Monday February 20, 2006 - 02:59pm (CST)
1)select customerid,b.aggregateno
from temptop10 a left outer join AGGREGATE_MEMBER b
on a.aggregateno=b.aggregateno and b.dwmonth='2005-12-31'

2)select customerid,b.aggregateno
from temptop10 a left outer join AGGREGATE_MEMBER b
on a.aggregateno=b.aggregateno where b.dwmonth='2005-12-31'

sql1和sql2看起来没什么分别，事实上差别大了。sql1是正确的写法，得到的是左连接的结果：
75736122-X | GL200405260015
F2685322-X | GL200405260015
| 75313212-0
| 15438327-8
| 74200965-3
| 48800033-7
11701-5009 | GL200506240001
15498586-9 | GL200506240001
.....

sql2的结果看起来确像一个等值连接的结果：
75736122-X | GL200405260015
F2685322-X | GL200405260015
11701-5009 | GL200506240001
15498586-9 | GL200506240001
15514263-1 | GL200506240001
15814304-4 | GL200506240001
15815336-5 | GL200506240001
61144602-8 | GL200511160014
75738868-4 | GL200511160014
26017968-4 | GL200312010002
61100604-9 | GL200312010002
61130906-6 | GL200312010002
....

分析原因，sql1是先解析on中的条件从b表过滤出数据然后left outer join a表，得出正确结果。sql2是先left outer join b 再按 where中的 dwmonth条件过滤，这时候左连接得到的笛卡儿乘积中的dwmonth为null的值就全部被过滤了，得到结果和等值连接一样。

结论：对被关联的表(如以上的b表)的限定条件，要写在on语句中Image