do I compare two table in Oracle? I want to compare
two tables to see if they have the same rows.
You can use SQL to compare two tables. When you talk
about comparing two tables, there are two different types of
Compare table structure: Compare
column names, column datatypes, and compare indexes and
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
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
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
select id, name, 1 src from A
select id, name, 2 src from B
group by id, name
where cnt1 <> cnt2;
can also use this SQL script to compare two tables:
where not exists
t1.field3 = t2.field3
t1.field4 = t2.field4);
complete scripts to compare two tables, download the
Oracle script collection.
Oracle Training from Don Burleson
The best on site
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
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
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
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
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.