How To Compare Data in Two Talbes Real Quick

create table #a1(i int primary key, c char(1))
insert into #a1 values(1, 'A')
insert into #a1 values(2, 'B')
insert into #a1 values(3, 'C')
insert into #a1 values(5, null)
go
create table #a2(i int primary key, c char(1))
insert into #a2 values(1, 'A')
insert into #a2 values(2, 'U')
insert into #a2 values(4, 'V')
insert into #a2 values(5, null)
go
--- rows in only one table
select #a1.i, #a2.i
from #a1 full outer join #a2 on #a1.i = #a2.i
where #a1.i is null or #a2.i is null


/*


i i
----------- -----------
3 NULL
NULL 4


(2 row(s) affected)
*/


-- different rows
-- instead of comparing all the nullable columns
-- as follows
-- #a1.c = #a2.c
-- or #a1.c is null and #a2.c is not null
-- or #a2.c is null and #a1.c is not null
-- just let UNION do it for you
select i from(
select #a1.* from #a1 join #a2 on #a1.i = #a2.i
union
select #a2.* from #a2 join #a1 on #a1.i = #a2.i
) t
group by i
having count(*)>1


i
-----------
2