Comparing the contents of two tables with SQL

A SQL script that compares the contents of two tables.

How can you compare two tables -- say, table A and table B -- and determine if their content is the same? Assuming...

that A and B have same structure, here's how:

First, from set theory, recall that:

If ((|A| = |B|) && |A U B| = |A|)) ====>>>  A = B 

|A| = NUMBER of rows in A 
|B| = NUMBER of rows in B 

Here's the SQL code (with Oracle syntax, but can be adapted for other DBMS's):

declare @cnt1 int 
declare @cnt2 int 
declare @cnt3 int 
declare @res bit 

select @cnt1 = count(*) from A 
select @cnt2 = count(*) from B 
select @cnt3 = count('x') 
     from (select * from A 
     UNION 
     select * from B) as t 

if (@cnt1 = @cnt2) and (@cnt2 = @cnt3) 
 begin 
   set @res = 1 
   print 'A = B' 
 end 
 else 
  begin 
    set @res = 0 
    print 'A <> B' 
  end 
go 

For More Information


This was last published in September 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close