Oracle Diff Table

What is the best way to perform "diff" between two structurally identical tables in Oracle? they are in two different patterns (visible to each other).

Thanks,

+4
source share
2 answers

If you do not have a tool like a PLSQL developer, you can completely break the join of the two tables. If they have a primary key, you can use it in the connection. This will give you an instant look at entries that are not in any table. Then for the records that exist in both tables, you can compare each of the fields. You should note that you cannot compare null with the regular = operator, so checking table1.field1 = table2.field1 will return false if both fields are zero. Thus, you will need to check each field if it has the same value as in the other table, or both values ​​are zero.

Your query might look like this (to return records that do not match):

select * from table1 t1 full outer join table2 t2 on t2.id = t1.id where -- Only one record exists t1.id is null or t2.id is null or ( -- The not = takes care of one of the fields being null not (t1.field1 = t2.field1) and -- and they cannot both be null (t1.field1 is not null or t2.field1 is not null) ) 

You will need to copy this field1 condition for each of your fields. Of course, you could write a function to compare field data to make your query simpler, but you should keep in mind that this can significantly reduce performance when you need to compare two large tables.

If your tables do not have a primary key, you will need to cross with them and perform these checks for each resulting record. You can speed this up a bit by using a full outer join in each required field, because it cannot be null and can be used in the join.

+4
source

Assuming you want to compare data (diff over whole rows) in two tables:

  SELECT * FROM (SELECT 's1.t' "Row Source", a.* FROM (SELECT col1, col2 FROM s1.t tbl1 MINUS SELECT col1, col2 FROM s2.t tbl2) a UNION ALL SELECT 's2.t', b.* FROM (SELECT col1, col2 FROM s2.t tbl2 MINUS SELECT col1, col2 FROM s1.t tbl1) b) ORDER BY 1; 

More information on comparing two tables.

+4
source

Source: https://habr.com/ru/post/1333114/


All Articles