select * from A
left join B on B.BID = A.BID
where B.BID is null
union all
select * from B
left join A on B.BID = A.BID
where A.BID is null
left join B on B.BID = A.BID
where B.BID is null
union all
select * from B
left join A on B.BID = A.BID
where A.BID is null
insert into a values(1 , 'a1')
insert into a values(2 , 'a2')
insert into a values(3 , 'a3')
insert into a values(4 , 'a4')
insert into a values(5 , 'a5')
insert into a values(6 , 'a6')
create table b(BID int, txt varchar(10))
insert into b values(4 , 'b4')
insert into b values(5 , 'b5')
insert into b values(6 , 'b6')
insert into b values(7 , 'b7')
insert into b values(8 , 'b8')
goselect isnull(a.bid , b.bid) bid , a.txt , b.txt from a full join b on a.bid = b.biddrop table a , b/*
bid txt txt
----------- ---------- ----------
1 a1 NULL
2 a2 NULL
3 a3 NULL
4 a4 b4
5 a5 b5
6 a6 b6
7 NULL b7
8 NULL b8(所影响的行数为 8 行)*/
if object_id('tablea')is not null drop table tablea
go
create table tablea(BID int, txt varchar(10))
insert into tablea values(1 , 'a1')
insert into tablea values(2 , 'a2')
insert into tablea values(3 , 'a3')
insert into tablea values(4 , 'a4')
insert into tablea values(5 , 'a5')
insert into tablea values(6 , 'a6')
if object_id('tableb')is not null drop table tableb
go
create table tableb(BID int, txt varchar(10))
insert into tableb values(4 , 'b4')
insert into tableb values(5 , 'b5')
insert into tableb values(6 , 'b6')
insert into tableb values(7 , 'b7')
insert into tableb values(8 , 'b8')
go
select a.txt , b.txt from tablea a full join tableb b on a.bid = b.bid
/*txt txt
---------- ----------
a1 NULL
a2 NULL
a3 NULL
a4 b4
a5 b5
a6 b6
NULL b7
NULL b8*/