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 appreciate开发者_开发知识库d ;)

---

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
      ,a.col3
  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'
              );

---

If it is no matter what is in column col2 and you don't want deleted items(items that are in old but not in new) to be in result then that could help:

select new.col1, new.col3 from new 
where new.col3 not in (select old.col3 from old where old.col1 = new.col1)