I have a problem similar to this StackOverflow question, except that I need to exclude certain fields from the comparison but still include it in the result set.
I'm penning the problem as locally symmetric difference.
For example Table A and B have columns X,Y,Z and I want to compare only Y,Z for differences but I still want the result set to include X.
Old style SQL for a full join - A concatenated with B, excluding rows in B also in A (the middle):
-- all rows in A with or without matching B
select a.x, a.y, a.z
from a
left join b
on a.x = b.x
and a.y = b.y
union all
-- all rows in B with no match in A to "exclude the middle"
select b.x, b.y, null as z
from b
where not exists (select null
from a
where b.x = a.x
and b.y = a.y)
ANSI Style:
select coalesce(a.x, b.x) as x,
coalesce(a.y, b.y) as y,
a.z
from a
full outer join b
on a.x = b.x
and a.y = b.y
The coalesce's are there for safety; I've never actually had cause to write a full outer join in the real world.
If what you really want to find out if two table are identical, here's how:
SELECT COUNT(*)
FROM (SELECT list_of_columns
FROM one_of_the_tables
MINUS
SELECT list_of_columns
FROM the_other_table
UNION ALL
SELECT list_of_columns
FROM the_other_table
MINUS
SELECT list_of_columns
FROM one_of_the_tables)
If that returns a non-zero result, then there is a difference. It doesn't tell you which table it's in, but it's a start.