How can you compare two tables -- say, table A and table B -- and determine if their content is the same? Assuming...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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 editor@searchDatabase.com 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.