select a1,null from a union all select null,b1 from b
select a1,''as b1 from t1 union all select '' as a1,b1 from t2
declare @a table(a1 nvarchar(10)) declare @b table(b1 nvarchar(10)) insert into @a select 'ax1' insert into @a select 'ax2' insert into @b select 'bx1' insert into @b select 'bx2' select * from @a a full join @b b on a.a1 = b.b1
declare @a table (a1 varchar(4)) insert @a select 'ax1' union all select 'ax2'declare @b table(b1 varchar(4)) insert @b select 'bx1' union all select 'bx2' --查看结果--连接查询 select a1,null as b1 from @a union all select null as a1,b1 from @b /* a1 b1 ---- ---- ax1 NULL ax2 NULL NULL bx1 NULL bx2(所影响的行数为 4 行)*/
--创建测试数据 declare @a table(id int,a1 varchar(10)) insert @a select 1,'ax1' union all select 2,'ax2' declare @b table(id int,b1 varchar(10)) insert @b select 1,'bx1' union all select 2,'bx2' --查看测试数据 select * from @a select * from @b --查看结果1 select id,a1,null as b1 from @a union all select id,null,b1 from @b /* id a1 b1 ----------- ---------- ---------- 1 ax1 NULL 2 ax2 NULL 1 NULL bx1 2 NULL bx2(所影响的行数为 4 行) */ --查看结果2 select * from ( select id,a1,null as b1 from @a union all select id,null,b1 from @b ) t order by id /* id a1 b1 ----------- ---------- ---------- 1 ax1 NULL 1 NULL bx1 2 NULL bx2 2 ax2 NULL(所影响的行数为 4 行)*/
declare @a table(id int,a1 varchar(10)) insert @a select 1,'ax1' union all select 2,'ax2' declare @b table(id int,b1 varchar(10)) insert @b select 1,'bx1' union all select 2,'bx2' select a.a1,b.b1 from @a a full outer join @b b on a.a1=b.b1
select null,b1 from b
union all
select '' as a1,b1 from t2
declare @b table(b1 nvarchar(10))
insert into @a select 'ax1'
insert into @a select 'ax2'
insert into @b select 'bx1'
insert into @b select 'bx2' select * from @a a full join @b b on a.a1 = b.b1
insert @a
select 'ax1' union all
select 'ax2'declare @b table(b1 varchar(4))
insert @b
select 'bx1' union all
select 'bx2'
--查看结果--连接查询
select a1,null as b1 from @a
union all
select null as a1,b1 from @b
/*
a1 b1
---- ----
ax1 NULL
ax2 NULL
NULL bx1
NULL bx2(所影响的行数为 4 行)*/
table a:
[id] [a1]
1 ax1
2 ax2[id] [b1]
1 bx1
2 bx2怎样得出结果:
[id] [a1] [b1]
1 ax1 null
1 null bx1
2 ax2 null
2 null bx2
declare @a table(id int,a1 varchar(10))
insert @a select 1,'ax1'
union all select 2,'ax2'
declare @b table(id int,b1 varchar(10))
insert @b select 1,'bx1'
union all select 2,'bx2'
--查看测试数据
select * from @a
select * from @b
--查看结果1
select id,a1,null as b1 from @a
union all
select id,null,b1 from @b
/*
id a1 b1
----------- ---------- ----------
1 ax1 NULL
2 ax2 NULL
1 NULL bx1
2 NULL bx2(所影响的行数为 4 行)
*/
--查看结果2
select * from (
select id,a1,null as b1 from @a
union all
select id,null,b1 from @b ) t
order by id
/*
id a1 b1
----------- ---------- ----------
1 ax1 NULL
1 NULL bx1
2 NULL bx2
2 ax2 NULL(所影响的行数为 4 行)*/
insert @a select 1,'ax1'
union all select 2,'ax2'
declare @b table(id int,b1 varchar(10))
insert @b select 1,'bx1'
union all select 2,'bx2'
select a.a1,b.b1 from @a a full outer join @b b on a.a1=b.b1