Question

在MS SQL中进行多表（三个以上）外连接时的困惑

现在假设有三张表，分别是：
table1:
a b
----------
1 x1
2 x2

table2:
a b
----------
1 y1
2 y2
3 y3

table3:
a b
----------
1 z1
2 z2
3 z3
4 z4

当运行select table1.b,table2.b,table3.b from table1 right join table2 on table1.a=table2.a
right join table3 on table3.a=table2.a时，显示结果为：

b b b
------------------
x1 y1 z1
x2 y2 z2
NULL y3 z3
NULL NULL z4

而运行select table1.b,table2.b,table3.b from table1 right join table2 on table1.a=table2.a
right join table3 on table3.a=table1.a时，显示结果为：

b b b
------------------
x1 y1 z1
x2 y2 z2
NULL NULL z3
NULL NULL z4

请教各位高手这是怎么回事？请说详细一点，谢谢！

Answer 1

declare @T1 table (a int,b varchar(10))
declare @T2 table (a int,b varchar(10))
declare @T3 table (a int,b varchar(10))
insert into @T1
select 1,'x1'
union all
select 2,'x2'
insert into @T2
select 1,'y1'
union all
select 2,'y2'
union all
select 3,'y3'
insert into @T3
select 1,'z1'
union all
select 2,'z2'
union all
select 3,'z3'
union all
select 4,'z4'

select A.b,B.b,C.b from @T1 A right join @T2 B on A.a = B.a
right join @T3 C on A.a = C.a

select A.b,B.b,C.b from @T1 A right join @T2 B on A.a = B.a
right join @T3 C on B.a = C.a

select A.fb,A.b,B.b from (
select A.a,A.b as fb,B.b from @T1 A right join @T2 B on A.a = B.a
) A
right join
(
select A.a,A.b as fb,B.b from @T1 A right join @T3 B on A.a = B.a
) B
on A.fb = B.fb

select A.b,A.fb,B.b from (
select A.a,A.b,B.b as fb from @T1 A right join @T2 B on A.a = B.a
) A
right join
(
select A.a,A.b as fb,B.b from @T2 A right join @T3 B on A.a = B.a
) B
on A.fb = B.fb