Question: How
do I compare two table in Oracle? I want to compare
two tables to see if they have the same rows.
Answer:
You can use SQL to compare two tables. When you talk
about comparing two tables, there are two different types of
comparison methods:
Compare table structure: Compare
column names, column datatypes, and compare indexes and
constraints
Compare table rows: Compare
the row contents of two tables.
Oracle Corporate developer Vadim Tropashko has some
interesting notes on Oracle SQL queries that compare
the contents of two tables. Tropashko has a remarkable approach to solving
the problem of comparing two tables, a worthy read for anyone who writes SQL in
Oracle.
Vadim also shows a great example of using the hidden
parameter _convert_set_to_join to improve SQL
execution speed for queries that find the semantic
difference between two tables, and he shows how to compare
the rows and columns of two tables with Oracle SQL syntax.
-
Compare two tables with SQL
-
Using dbms_comparison to compare two tables
Vadim Tropashko's new book "SQL
Design Patterns: The Expert Guide to SQL Programming",
is the very first book to apply the mathematical foundation
of SQL in-terms of design patterns, a must-own book for any
professional SQL developer.
You can also use this SQL to compare two Oracle tables:
with t as (select *
from tab1 a
where
not exists
(select
1
from
tab2 b
where
a.field1=b.field1
and
a.field2=b.field2))
select
*
from
t
where exists
(select 1
from
tab2 b
where
t.field1=b.field1
and
t.field3=b.field3
and
t.field4=b.field4);
Tropashko notes that this anti-union semantic difference
problem can be used to compare two tables.
select * from (
select id, name,
sum(case when src=1 then 1 else 0 end) cnt1,
sum(case when src=2 then 1 else 0 end) cnt2
from
(
select id, name, 1 src from A
union all
select id, name, 2 src from B
)
group by id, name
)
where cnt1 <> cnt2;
You
can also use this SQL script to compare two tables:
select
field1,
field2
from
table1 t1
where not exists
(select
1
from
table2 t2
where
t1.field2 =
t2.field2)
and
exists
(select
1
from
table2 t2
where
t1.field3 = t2.field3
and
t1.field4 = t2.field4);
For
complete scripts to compare two tables, download the
Oracle script collection.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
