参考:declare @tableA table (itemname char(1),message char(10)) declare @tableB table (itemname char(1),message char(10))insert @tableA values('A','AAAAAA') insert @tableA values('B','AAAAAA') insert @tableA values('C',null) insert @tableA values('D','') insert @tableA values('E','EEEEEE')insert @tableB values('A','AAAAAA') insert @tableB values('B',null) insert @tableB values('C','CCCCCC') insert @tableB values('D','DDDDDD') insert @tableB values('F','FFFFFF')select * into #t from @tableA select * into #t1 from @tableBselect ItemName=case isnull(a.ItemName,'') when '' then b.ItemName else a.ItemName end ,Message=case isnull(a.Message,'') when '' then b.Message else a.Message end from @tableA a full join @tableB b on a.itemname=b.itemnamedrop table #t drop table #t1
select b.id, IsNull(b.aa,a.aa) as aa, IsNull(b.bb,a.bb) as bb from table1 a full join table2 b on a.id = b.id
select t1.id,isnull(t2.aa,t1.aa)aa,isnull(ta.bb,t1.bb)bb from table1 t1 left join table2 t2 on t1.id=t2.id and t1.bb=t2.bb
select id=isnull(b.id,a.id),aa=isnull(b.aa,a.aa),bb=isnull(b.bb,a.bb) from table1 a left join table2 b on a.bb=b.bb and a.id=b.id
--下面是测试--测试数据 declare @TABLE1 table(id varchar(2),aa varchar(2),bb varchar(2)) insert into @TABLE1 select '01','a1','01' union all select '02','a2','01' union all select '03','a3','01' union all select '01','e1','02' union all select '02','e2','02'declare @TABLE2 table(id varchar(2),aa varchar(2),bb varchar(2)) insert into @TABLE2 select '01','b1','01' union all select '03','b2','01' union all select '01','a1','02'--查询处理: select id=isnull(b.id,a.id),aa=isnull(b.aa,a.aa),bb=isnull(b.bb,a.bb) from @table1 a left join @table2 b on a.bb=b.bb and a.id=b.id/*--测试结果id aa bb ---- ---- ---- 01 b1 01 02 a2 01 03 b2 01 01 a1 02 02 e2 02(所影响的行数为 5 行) */
select isnull(b.id,a.id) as id ,IsNull(b.aa,a.aa) as aa, IsNull(b.bb,a.bb) as bb from table1 a left join table2 b on a.id = b.id and a.bb=b.bb
select * from table2 union select * from table1 where (id,aa,bb) not in( select id,aa,bb from table2)
select isnull(a.aa,b.aa) as aa,isnull(a.bb,b.bb) as bb where from table1 b left join table2 a on a.id =b.id 采用左连接的效率会高点
select isnull(a.aa,b.aa) as aa,isnull(a.bb,b.bb) as bb from table1 b left join table2 a on a.id =b.id 更正上次的错误
select * from table2 union select * from table1
更正:select * from (select * from table2 union select * from table2) t1 where t1.bb in(select bb from table2)
丢人地再更正: select * from test10 t2 union all select * from test9 t1 where t1.id not in (select id from test10) and t1.bb not in (select bb from test10)
declare @tableB table (itemname char(1),message char(10))insert @tableA values('A','AAAAAA')
insert @tableA values('B','AAAAAA')
insert @tableA values('C',null)
insert @tableA values('D','')
insert @tableA values('E','EEEEEE')insert @tableB values('A','AAAAAA')
insert @tableB values('B',null)
insert @tableB values('C','CCCCCC')
insert @tableB values('D','DDDDDD')
insert @tableB values('F','FFFFFF')select * into #t from @tableA
select * into #t1 from @tableBselect ItemName=case isnull(a.ItemName,'') when '' then b.ItemName else a.ItemName end
,Message=case isnull(a.Message,'') when '' then b.Message else a.Message end
from @tableA a full join @tableB b on a.itemname=b.itemnamedrop table #t
drop table #t1
IsNull(b.bb,a.bb) as bb
from table1 a
full join table2 b on a.id = b.id
t1.id=t2.id and t1.bb=t2.bb
from table1 a left join table2 b on a.bb=b.bb and a.id=b.id
declare @TABLE1 table(id varchar(2),aa varchar(2),bb varchar(2))
insert into @TABLE1
select '01','a1','01'
union all select '02','a2','01'
union all select '03','a3','01'
union all select '01','e1','02'
union all select '02','e2','02'declare @TABLE2 table(id varchar(2),aa varchar(2),bb varchar(2))
insert into @TABLE2
select '01','b1','01'
union all select '03','b2','01'
union all select '01','a1','02'--查询处理:
select id=isnull(b.id,a.id),aa=isnull(b.aa,a.aa),bb=isnull(b.bb,a.bb)
from @table1 a left join @table2 b on a.bb=b.bb and a.id=b.id/*--测试结果id aa bb
---- ---- ----
01 b1 01
02 a2 01
03 b2 01
01 a1 02
02 e2 02(所影响的行数为 5 行)
*/
IsNull(b.bb,a.bb) as bb
from table1 a left join table2 b on a.id = b.id and a.bb=b.bb
union
select * from table1
where (id,aa,bb) not in(
select id,aa,bb from table2)
where from table1 b left join table2 a on a.id =b.id
采用左连接的效率会高点
left join table2 a on a.id =b.id
更正上次的错误
union
select * from table1
union
select * from table2) t1 where t1.bb in(select bb from table2)
select * from test10 t2
union all
select * from test9 t1 where t1.id not in (select id from test10) and t1.bb not in (select bb from test10)