Tom Tom - 10 months ago 46
SQL Question

Oracle SQL find different values in two tables with same schema

While there have been some similar questions I couldn't find a solution for exactly my problem.

I have two tables 'new', 'old' and both have the same schema col1, col2, col3.

Both don't necessarily have the same records, especially new could have more records than old.

Now what I'm looking for is a query the return all records where new.col3 is different from old.col3 (and new.col1 equals old.col1). col3 can have different values or null. col1 is not a PK, so there could be multiple records with the same col1 value.

All my attempts using minuses in different combinations either didn't result in all changes or included rows with both the old as well as the new col3 value. I only need/want the result to contain new.col1, new.col3 for all changed col3 values as explained above.

Any help greatly appreciated ;)

Answer Source

The following would find all rows in new_table such that there exists at least one row in old_table with a different value for col3.

select a.col1
  from new_table a
 where exists(select 'x' 
                from old_table b 
               where a.col1 = b.col1
                 and decode(a.col3, b.col3, 'same', 'diff') = 'diff'