extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both table
s
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repea
t
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:

> what is the best way of extracting just the rows that existing in both tab
les
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then rep
eat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>