Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

How to compare two tables

Oracle Database Tips by Donald BurlesonAugust 21, 2013

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!

Oracle training
 
 


 

 

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.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster