34,590
社区成员
发帖
与我相关
我的任务
分享
create table A(s1 varchar(10),s2 varchar(10),s3 varchar(10))
insert into A select 'a1','a',''
insert into A select 'a2','b',''
insert into A select 'a3','c',''
insert into A select 'a4','d',''
insert into A select 'a6','f',''
create table B(s1 varchar(10),s2 varchar(10),s3 varchar(10))
insert into B select 'a1','','a'
insert into B select 'a2','','b'
insert into B select 'a3','','c'
insert into B select 'a4','','d'
insert into B select 'a5','','e'
select isnull(A.s1,B.s1) s1 , isnull(A.s2,'') s2 , isnull(B.s3,'') s3
from A full join B on A.s1 = B.s1
order by A.s1
drop table A,B
/*
s1 s2 s3
---------- ---------- ----------
a1 a a
a2 b b
a3 c c
a4 d d
a5 e
a6 f
(所影响的行数为 6 行)
*/
select isnull(A.s1,B.s1) s1 , isnull(A.s2,'') s2 , isnull(B.s3,'') s3
from A full join B on A.s1 = B.s1
select isnull(a.s1,b.s1)s1,isnull(a.s2,b.s2)s2,isnull(a.s3,b.s3)s3 from @a a
full join @b b on a.s1=b.s1
declare @a table(s1 varchar(10),s2 varchar(10),s3 varchar(10))
insert into @a select 'a1','a',''
insert into @a select 'a2','b',''
insert into @a select 'a3','c',''
insert into @a select 'a4','d',''
insert into @a select 'a6','f',''
declare @b table(s1 varchar(10),s2 varchar(10),s3 varchar(10))
insert into @b select 'a1','','a'
insert into @b select 'a2','','b'
insert into @b select 'a3','','c'
insert into @b select 'a4','','d'
insert into @b select 'a5','','e'
select isnull(a.s1,b.s1)s1,isnull(a.s2,b.s2)s2,isnull(a.s3,b.s3)s3 from @a a
full join @b b on a.s1=b.s1