求一条三表连接的sql语句

[lilyfirstone]

要求 ,有三个条,每个表有两列,有一个共同列name.
A表
name sex
bb 男
cc 女
B表
name addr
bb 无
ee 有
C表
name mail
ee @kk
gg @343

想要的就是
bb 男 无
cc 女
ee 有 @kk
gg @342

不要告诉我用别的方法,我就是想用一条sql语句实现

[caiyunxia]

select d.name,sex,addr,mail
from (select distinct name from A union select distinct name from b union select distinct name from A ) d left join a on a.name=d.name
left join b on b.name = d.name left join c on c.name=d.name

[caiyunxia]

select d.name,sex,addr,mail
from (select distinct name from A union select distinct name from b union select distinct name from c ) d left join a on a.name=d.name
left join b on b.name = d.name left join c on c.name=d.name

[Yang_]

full join :

select isnull(x.name,c.name) as name,x.sex,x.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from #a a full join #b b on a.name=b.name) x
full join #c c on x.name=c.name

[psxfghost]

唉,来晚了!................
^_^

[firetoucher]

select D.name,sex,addr,mail
from
(select distinct name from A union select distinct name from B union select distinct name from C ) D,A,B,C
where D.name *= A.name and
D.name*= B.name and
D.name*= C.name

[愉快的登山者]

select isnull(A.name, isnull(B.name, C.name)) name, A.sex, B.addr, C.mail
from A full join B on A.name = B.name
full join C on A.name = C.name
order by isnull(A.name, isnull(B.name, C.name))

[yoki]

select isnull(t.name,c.name) as name,xt.sex,t.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from a full join b on a.name=b.name)t
full join c on t.name=c.name

[yoki]

select isnull(t.name,c.name) as name,t.sex,t.addr,c.mail
from
(select isnull(a.name,b.name) as name ,a.sex,b.addr
from a full join b on a.name=b.name)t
full join c on t.name=c.name

[lilyfirstone]

谢谢大家捧场,我自以为sql语句没那么糟,现在才发现这样的问题就是给大家送分的
多谢多谢,又学了一招,以后自己也要多多努力啦

写完这个存储过程,结帖.因为诸位的语句,我还没空试,不知道是不是能有理想的效果.

不管怎么说,感激涕零,无以为表!

[happydreamer]

declare @a table(a int,b int)
declare @b table(a int,b int)
insert @a values(1,1)
insert @a values(2,2)
insert @b values(1,1)
insert @b values(3,3)

--左:
select * from @a Aa left join @b Bb on Aa.a=Bb.a
--右：
select * from @a Aa right join @b Bb on Aa.a=Bb.a
--内
select * from @a Aa join @b Bb on Aa.a=Bb.a
--外：
select * from @a Aa full join @b Bb on Aa.a=Bb.a
--完全
select * from @a,@b


cross join 是笛卡儿乘积 就是一张表的行数乘以另一张表的行数
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 返回两张表中的行 left join+right join
inner join 只返回两张表连接列的匹配项

[caiyunxia]

create table #a(name varchar(10),sex varchar(10))
create table #b(name varchar(10),addr varchar(10))
create table #c(name varchar(10),mail varchar(10))
insert into #a(name,sex) values('bb','男')
insert into #a(name,sex) values('cc','女')
insert into #b(name,addr) values('bb','无')
insert into #b(name,addr) values('ee','有')
insert into #c(name,mail) values('ee','@kk')
insert into #c(name,mail) values('gg','@343')

select d.name,sex,addr,mail
from (select distinct name from #a union select distinct name from #b union select distinct name from #c ) d left join #a on #a.name=d.name
left join #b on #b.name = d.name left join #c on #c.name=d.name

select COALESCE (#a.name,#b.name,#c.name) as name ,sex,addr,mail
from #a full join #b on #b.name = #a.name full join #c on (#c.name=#b.name or #c.name=#a.name)

drop table #a
drop table #b
drop table #c


name sex addr mail
---------- ---------- ---------- ----------
bb 男 无 NULL
cc 女 NULL NULL
ee NULL 有 @kk
gg NULL NULL @343

（所影响的行数为 4 行）

name sex addr mail
---------- ---------- ---------- ----------
ee NULL 有 @kk
gg NULL NULL @343
bb 男 无 NULL
cc 女 NULL NULL