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 first published in September 2001
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close