CODEWITHSUNDEEP
oracle-databasecompareunion
Landon Statis
Landon Statis

Reputation: 839

Compare table content

I have 2 tables and I need to do a table compare:

TABLE A
  LABEL
  VALUE

TABLE B
  LABEL
  VALUE

Basically I want:

With that information, I can record the proper historical data I need to. It will show me where the value has changed, or where a label was added or deleted......you can say TABLE A is the "new" set of data, and TABLE B is the "old" set of data. So I can see what is being added, what was deleted, and what was changed.

Been trying with UNION & MINUS, but no luck yet.

Something like:

   A LABEL   A VALUE     B LABEL   B VALUE
   ---------------------------------------
     XXX        5         XXX         3
     YYY        2
                          ZZZ         4
     WWW        7         WWW         8

If the labels and values are the same, I do not need them in the result set.

Upvotes: 0

Views: 55

Answers (1)

user5683823
user5683823

Reputation:

Here is one way (and possibly the most efficient way) to solve this problem. The main part is the subquery that does a UNION ALL and GROUP BY on the result, keeping only groups consisting of a single row. (The groups with two rows are those where the same row exists in both tables.) This method was invented by Marco Stefanetti - first discussed on the AskTom discussion board. The benefit of this approach - over the more common "symmetric difference" approach - is that each base table is read just once, not twice.

Then, to put the result in the desired format, I use a PIVOT operation (available since Oracle 11.1); in earlier versions of Oracle, the same can be done with a standard aggregate outer query.

Note that I modified the inputs to show the handling of NULL in the VALUE column also.

Important: This solution assumes LABEL is primary key in both tables; if not, it's not clear how the required output would even make sense.

with
     table_a ( label, value ) as (
                 select 'AAA', 3    from dual
       union all select 'CCC', null from dual
       union all select 'XXX', 5    from dual
       union all select 'WWW', 7    from dual
       union all select 'YYY', 2    from dual
       union all select 'HHH', null from dual
     ),
     table_b ( label, value ) as (
                 select 'ZZZ', 4    from dual
       union all select 'AAA', 3    from dual
       union all select 'HHH', null from dual
       union all select 'WWW', 8    from dual
       union all select 'XXX', 3    from dual
       union all select 'CCC', 1 from dual
     )
-- End of test data (NOT PART OF THE SOLUTION!) SQL query begins below this line.
select a_label, a_value, b_label, b_value
from   (
         select   max(source) as source, label as lbl, label, value
         from     (
                    select 'A' as source, label, value
                      from table_a
                    union all
                    select 'B' as source, label, value
                      from table_b
                  )
         group by label, value
         having   count(*) = 1
       )
pivot ( max(label) as label, max(value) as value for source in ('A' as a, 'B' as b) )
;

Output:

A_LABEL  A_VALUE  B_LABEL  B_VALUE
-------  -------  -------  -------
YYY            2        
CCC               CCC            1
WWW            7  WWW            8
                  ZZZ            4
XXX            5  XXX            3

Upvotes: 1

Related Questions