--prepare test data
declare @testa table
(
id int,
a int
)
insert into @testa
select 1,2 union all
select 2,3
declare @testb table
(
id int,
b int
)
insert into @testb
select 2,3 union all
select 3,4
--1 by union/union all
--1.1 union
select * from @testa
union
select * from @testb
--1.2 union all
select * from @testa
union all
select * from @testb
--2 join
select isnull(a.id,b.id) as id,isnull(a.a,b.b) as a
from @testa a full join @testb b
on a.id=b.id and a.a=b.b
order by id
--3 exists/in
--3.1 exists
insert into @testa
select * from @testb b where not exists(select 1 from @testa where b.id=id and b.b=a)
select * from @testa
--3.2 in
insert into @testa
select * from @testb b where id not in (select id from @testa where b.id=id and b.b=a)
select * from @testa