Locally symmetric difference in sql

Question

I have a problem similar to the stackoverflow question posed in the link below 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 has columns X,Y,Z and I want to compare only Y,Z for differences but I still want the result set to include X.

link text

Answer 1

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.

Answer 2

Sounds like this is basically what you want. Match rows between two tables on columns Y and Z, find the unmatched rows, and output the values of columns X, Y, and Z.

SELECT a.x, a.y, a.z, b.x, b.y, b.z
  FROM a FULL OUTER JOIN b ON a.y = b.y AND a.z = b.z
  WHERE a.y IS NULL OR b.y IS NULL