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
- What do you think about this tip? E-mail us at [email protected] with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL gurus are waiting to answer your technical questions.