外连接中用where子句

sqlserver2000环境下:

 

create table user (id int,school_id in);
insert into user values(1,1);
insert into user values(2,2);

 

create table school(id,name);
insert into school values(1,'邯村小学');
insert into school values(2,'正定四中');
insert into school values(3,'正定一中');
insert into school values(4,'西南农大');

 

注意3条语句的结果:

 

select a.id,school_name from user a right join school b on b.id=school_id
1          邯村小学
2          正定四中
NULL       正定一中
NULL       西南大学

 

select a.id,school_name from user a right join school b on b.id=school_id where a.id<6
1          邯村小学
2          正定四中

 

select a.id,b.school_name from (select * from user where id<3) a right join
    school b on a.school_id=b.id
1          邯村小学
2          正定四中
NULL       正定一中
NULL       西南大学