Left outer join

大家猜猜以下两个语句执行结果是否一样？

select * from aa a left outer join bb b on a.a=b.a and a.b=’wen’ ———(1)
select * from aa a left outer join bb b on a.a=b.a where a.b=’wen’——(2)

其中表bb跟表aa的结构一样，如下：

create table aa
(a int primary key,
b varchar(20))

结果如下：

语句(1):

a b a b
———– ——————– ———– ——————–
1 wen 1 today
2 su NULL NULL
3 test NULL NULL
4 ok NULL NULL
5 niu NULL NULL

(5 row(s) affected)

语句(2):

a b a b
———– ——————– ———– ——————–
1 wen 1 today

(1 row(s) affected)

以上结果在 oracle 9i,sql server 2000,sybase anywhere 9下一样

关于left outer join,在sql server 2000 books online执行计划一节中是这样解释的:

The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.