sqloraclesymmetric-difference

Locally symmetric difference in sql


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.


Solution

  • 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.