关于三张表full outer join 的问题，向高手求助！

[windforcecn]

运行code代码如下

DECLARE  @test1 TABLE( 
		[id] int,
   		[name] [varchar](18) 
	)
DECLARE  @test2 TABLE( 
		[id] int,
   		[name] [varchar](18) 
	)

DECLARE  @test3 TABLE( 
		[id] int,
   		[name] [varchar](18) 
	)


insert into @test1 values(1,'a')
insert into @test1 values(2,'b')
insert into @test1 values(3,'c')


insert into @test2 values(1,'d')
insert into @test2 values(2,'e')
insert into @test2 values(3,'f')
insert into @test2 values(4,'g')
insert into @test2 values(5,'h')


insert into @test3 values(1,'i')
insert into @test3 values(2,'j')
insert into @test3 values(3,'k')
insert into @test3 values(4,'l')

select * from @test1 a full outer join @test2 b on a.id=b.id full outer join @test3 c on a.id=c.id  

得到结果是

id                name                id             name                id                name 
1                a	               1                d                1                    i
2                b                     2                e                2	                j
3                c	                3                f                3                    k
NULL             NULL                4                 g                NULL                NULL
NULL             NULL                5                 h                NULL                NULL
NULL             NULL                NULL              NULL             4                    l

这个结果是不对的，因为右边test3表的4 ,1 值应当和第2张test2表的 4平行。

应该是
select * from @test1 a full outer join @test2 b on a.id=b.id full outer join @test3 c on b.id=c.id

结果为

id                name                id             name                id                name 
1                a	               1                d                1                    i
2                b                     2                e                2	                j
3                c	                3                f                3                    k
NULL             NULL                4                 g                4                   1
NULL             NULL                5                 h                NULL                NULL

但我并不知道test1,test2,test3 哪张表的数据较多，所以不能知道应该用b的id还是a.id来匹配c的id。

请问有办法吗？请不要告诉我先count出来哪张表数据多。

[htl258_Tony]

DECLARE  @test1 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )
DECLARE  @test2 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )

DECLARE  @test3 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )


insert into @test1 values(1,'a')
insert into @test1 values(2,'b')
insert into @test1 values(3,'c')


insert into @test2 values(1,'d')
insert into @test2 values(2,'e')
insert into @test2 values(3,'f')
insert into @test2 values(4,'g')
insert into @test2 values(5,'h')


insert into @test3 values(1,'i')
insert into @test3 values(2,'j')
insert into @test3 values(3,'k')
insert into @test3 values(4,'l')

select isnull(isnull(a.id,b.id),c.id) id,isnull(isnull(a.name,b.name),c.name) name 
from @test1 a full outer join @test2 b on a.id=b.id full outer join @test3 c on a.id=c.id
/*
id	name
1	a
2	b
3	c
4	g
5	h
4	l
*/

[htl258_Tony]

DECLARE  @test1 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )
DECLARE  @test2 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )

DECLARE  @test3 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )


insert into @test1 values(1,'a')
insert into @test1 values(2,'b')
insert into @test1 values(3,'c')


insert into @test2 values(1,'d')
insert into @test2 values(2,'e')
insert into @test2 values(3,'f')
insert into @test2 values(4,'g')
insert into @test2 values(5,'h')


insert into @test3 values(1,'i')
insert into @test3 values(2,'j')
insert into @test3 values(3,'k')
insert into @test3 values(4,'l')

select * from @test1 a full outer join @test2 b on a.id=b.id full outer join @test3 c on isnull(a.id,b.id)=c.id  
/*
id	name	id	name	id	name
1	a	1	d	1	i
2	b	2	e	2	j
3	c	3	f	3	k
NULL	NULL	4	g	4	l
NULL	NULL	5	h	NULL	NULL
*/

看错.加个ISNULL在条件就行了.

[windforcecn]

楼上，我是要join，不是union数据啊

[windforcecn]

多谢多谢！

[dawugui]

DECLARE  @test1 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )
DECLARE  @test2 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )

DECLARE  @test3 TABLE( 
        [id] int,
           [name] [varchar](18) 
    )


insert into @test1 values(1,'a')
insert into @test1 values(2,'b')
insert into @test1 values(3,'c')


insert into @test2 values(1,'d')
insert into @test2 values(2,'e')
insert into @test2 values(3,'f')
insert into @test2 values(4,'g')
insert into @test2 values(5,'h')


insert into @test3 values(1,'i')
insert into @test3 values(2,'j')
insert into @test3 values(3,'k')
insert into @test3 values(4,'l')

select t1.* , t2.* , t3.* from @test1 t1
full join @test2 t2 on t1.id = t2.id
full join @test3 t3 on t2.id = t3.id

/*
id          name               id          name               id          name               
----------- ------------------ ----------- ------------------ ----------- ------------------ 
1           a                  1           d                  1           i
2           b                  2           e                  2           j
3           c                  3           f                  3           k
NULL        NULL               4           g                  4           l
NULL        NULL               5           h                  NULL        NULL

（所影响的行数为 5 行）

*/

[soft_wsx]

select t1.* , t2.* , t3.* from @test1 t1
full join @test2 t2 on t1.id = t2.id
full join @test3 t3 on t2.id = t3.id