Hi Tom,
What is the easiest way to find matched, unmatched and missing rows between 2 tables?
Currently I am using the following approach -
To find all the rows that match between the 2 tables
SELECT * FROM A
INTERSECT
SELECT * FROM B
To find the rows that are unmatched between the 2 tables
SELECT * FROM A
MINUS
SELECT * FROM B
To find the missing rows from table A
SELECT * FROM B
MINUS
SELECT * FROM A WHERE the unmatched rows between A MINUS B are filtered out
The first 2 queries seem to be working fine, but the query to find missing rows doesn't work as expected. I realize that I am doing something incorrect, not sure what, and hoping that you can help.
Thanks for your time.
Regards,
Scott.
ops$tkyte%ORA10GR2> create table t1
2 as
3 select username a, user_id b, created c
4 from all_users
5 where username like '%D%';
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2
2 as
3 select username a, user_id b, created c
4 from all_users
5 where username like '%B%';
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select case when t1.rowid is not null and t2.rowid is not null then 'The same in both'
2 when t1.rowid is not null then 'This is in T1 only'
3 when t2.rowid is not null then 'This is in T2 only'
4 else 'This cannot happen :)'
5 end tag,
6 coalesce( t1.a, t2.a ) a,
7 coalesce( t1.b, t2.b ) b,
8 coalesce( t1.c, t2.c ) c
9 from t1 full outer join t2
10 on (t1.a = t2.a and t1.b = t2.b and t1.c = t2.c)
11 /
TAG A B C
--------------------- ------------------------------ ---------- --------
The same in both DBSNMP 24 30/06/05
The same in both XDB 38 30/06/05
This is in T1 only DMSYS 35 30/06/05
This is in T1 only DIP 19 30/06/05
This is in T1 only ORDPLUGINS 44 30/06/05
This is in T1 only MDSYS 46 30/06/05
This is in T1 only MDDATA 50 30/06/05
This is in T1 only ORDSYS 43 30/06/05
This is in T2 only BIG_TABLE 58 14/12/05
This is in T2 only B 474 12/11/08
10 rows selected.
that would be one approach, if you are primarily interested in "what is different", you can use:
https://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html look for the heading "Comparing the Contents of Two Tables"