declare @a table(a varchar(10),b varchar(10)) insert into @a select '1','10' insert into @a select '2','20' insert into @a select '3','30' declare @b table(a varchar(10),c varchar(10)) insert into @b select '1','15' insert into @b select '2','30' insert into @b select '','45' select a.a,a.b,c.c from @a a left join @b c on a.a=c.a union all select null,c,null from @b where isnull(a,'')=''
declare @a table (a int,b int) insert into @a select 1,10 insert into @a select 2,20 insert into @a select 3,30 declare @b table (a int,c int) insert into @b select 1,15 insert into @b select 2,30 insert into @b select null,45select a.*,b.c from (select * from @a union select * from @b where a is null)a left join @b b on a.a=b.a a b c NULL 45 NULL 1 10 15 2 20 30 3 30 NULL
create table a(a int, b int) insert into a values(1, 10) insert into a values(2, 20) insert into a values(3, 30) create table b(a int, c int) insert into b values(1 , 15) insert into b values(2 , 30) insert into b values(null, 45) goselect isnull(a.a,b.a) a , isnull(a.b,b.c) b , c from a full join b on a.a = b.adrop table a,b/* a b c ----------- ----------- ----------- 1 10 15 2 20 30 3 30 NULL NULL 45 45(4 行受影响) */
create table a(a int, b int) insert into a values(1, 10) insert into a values(2, 20) insert into a values(3, 30) create table b(a int, c int) insert into b values(1 , 15) insert into b values(2 , 30) insert into b values(null, 45) goselect isnull(a.a,b.a) a , isnull(a.b,b.c) b , c = case when a.a = b.a then c else null end from a full join b on a.a = b.adrop table a,b/* a b c ----------- ----------- ----------- 1 10 15 2 20 30 3 30 NULL NULL 45 NULL(4 行受影响) */
from a full join b on a.a=b.a
insert into @a select '1','10'
insert into @a select '2','20'
insert into @a select '3','30'
declare @b table(a varchar(10),c varchar(10))
insert into @b select '1','15'
insert into @b select '2','30'
insert into @b select '','45'
select a.a,a.b,c.c from @a a left join @b c on a.a=c.a
union all
select null,c,null from @b where isnull(a,'')=''
insert into @a select 1,10
insert into @a select 2,20
insert into @a select 3,30
declare @b table (a int,c int)
insert into @b select 1,15
insert into @b select 2,30
insert into @b select null,45select a.*,b.c from (select * from @a union select * from @b where a is null)a left join @b b
on a.a=b.a a b c
NULL 45 NULL
1 10 15
2 20 30
3 30 NULL
insert into a values(1, 10)
insert into a values(2, 20)
insert into a values(3, 30)
create table b(a int, c int)
insert into b values(1 , 15)
insert into b values(2 , 30)
insert into b values(null, 45)
goselect isnull(a.a,b.a) a , isnull(a.b,b.c) b , c
from a full join b
on a.a = b.adrop table a,b/*
a b c
----------- ----------- -----------
1 10 15
2 20 30
3 30 NULL
NULL 45 45(4 行受影响)
*/
insert into a values(1, 10)
insert into a values(2, 20)
insert into a values(3, 30)
create table b(a int, c int)
insert into b values(1 , 15)
insert into b values(2 , 30)
insert into b values(null, 45)
goselect isnull(a.a,b.a) a , isnull(a.b,b.c) b , c = case when a.a = b.a then c else null end
from a full join b
on a.a = b.adrop table a,b/*
a b c
----------- ----------- -----------
1 10 15
2 20 30
3 30 NULL
NULL 45 NULL(4 行受影响)
*/