How to compare two tables column by column in oracle
I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key开发者_JAVA百科 is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked.
I want to compare these two tables column by column and find out which records are not matching. i want the specific column in each row in two tables that are not matching.
select *
from
(
( select * from TableInSchema1
minus
select * from TableInSchema2)
union all
( select * from TableInSchema2
minus
select * from TableInSchema1)
)
should do the trick if you want to solve this with a query
As an alternative which saves from full scanning each table twice and also gives you an easy way to tell which table had more rows with a combination of values than the other:
SELECT col1
, col2
-- (include all columns that you want to compare)
, COUNT(src1) CNT1
, COUNT(src2) CNT2
FROM (SELECT a.col1
, a.col2
-- (include all columns that you want to compare)
, 1 src1
, TO_NUMBER(NULL) src2
FROM tab_a a
UNION ALL
SELECT b.col1
, b.col2
-- (include all columns that you want to compare)
, TO_NUMBER(NULL) src1
, 2 src2
FROM tab_b b
)
GROUP BY col1
, col2
HAVING COUNT(src1) <> COUNT(src2) -- only show the combinations that don't match
Credit goes here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710
It won't be fast, and there will be a lot for you to type (unless you generate the SQL from user_tab_columns), but here is what I use when I need to compare two tables row-by-row and column-by-column.
The query will return all rows that
- Exists in table1 but not in table2
- Exists in table2 but not in table1
- Exists in both tables, but have at least one column with a different value
(common identical rows will be excluded).
"PK" is the column(s) that make up your primary key. "a" will contain A if the present row exists in table1. "b" will contain B if the present row exists in table2.
select pk
,decode(a.rowid, null, null, 'A') as a
,decode(b.rowid, null, null, 'B') as b
,a.col1, b.col1
,a.col2, b.col2
,a.col3, b.col3
,...
from table1 a
full outer
join table2 b using(pk)
where decode(a.col1, b.col1, 1, 0) = 0
or decode(a.col2, b.col2, 1, 0) = 0
or decode(a.col3, b.col3, 1, 0) = 0
or ...;
Edit Added example code to show the difference described in comment. Whenever one of the values contains NULL, the result will be different.
with a as(
select 0 as col1 from dual union all
select 1 as col1 from dual union all
select null as col1 from dual
)
,b as(
select 1 as col1 from dual union all
select 2 as col1 from dual union all
select null as col1 from dual
)
select a.col1
,b.col1
,decode(a.col1, b.col1, 'Same', 'Different') as approach_1
,case when a.col1 <> b.col1 then 'Different' else 'Same' end as approach_2
from a,b
order
by a.col1
,b.col1;
col1 col1_1 approach_1 approach_2
==== ====== ========== ==========
0 1 Different Different
0 2 Different Different
0 null Different Same <---
1 1 Same Same
1 2 Different Different
1 null Different Same <---
null 1 Different Same <---
null 2 Different Same <---
null null Same Same
Try to use 3rd party tool, such as SQL Data Examiner which compares Oracle databases and shows you differences.
Using the minus
operator was working but also it was taking more time to execute which was not acceptable.
I have a similar kind of requirement for data migration and I used the NOT IN
operator for that.
The modified query is :
select *
from A
where (emp_id,emp_name) not in
(select emp_id,emp_name from B)
union all
select * from B
where (emp_id,emp_name) not in
(select emp_id,emp_name from A);
This query executed fast. Also you can add any number of columns in the select query. Only catch is that both tables should have the exact same table structure for this to be executed.
SELECT *
FROM (SELECT table_name, COUNT (*) cnt
FROM all_tab_columns
WHERE owner IN ('OWNER_A')
GROUP BY table_name) x,
(SELECT table_name, COUNT (*) cnt
FROM all_tab_columns
WHERE owner IN ('OWNER_B')
GROUP BY table_name) y
WHERE x.table_name = y.table_name AND x.cnt <> y.cnt;
Used full outer join -- But it will not show - if its not matched -
SQL> desc aaa - its a table Name Null? Type
A1 NUMBER B1 VARCHAR2(10)
SQL> desc aaav -its a view Name Null? Type
A1 NUMBER B1 VARCHAR2(10)
SQL> select a.column_name,b.column_name from dba_tab_columns a full outer join dba_tab_columns b on a.column_name=b.column_name where a.TABLE_NAME='AAA' and B.table_name='AAAV';
COLUMN_NAME COLUMN_NAME
A1 A1 B1 B1
精彩评论