Comparing two database tables

Question

I need to compare ALL fields in two tables... they are the same size (four lines) and have the same amount of columns (five columns). The logic I'm looking for is...

If (table 1 = table 2)
do something
Else
do something else

In SQL I wrote something like...

If (Select * from table 1 = select * from table 2)
do something
else
do something else

THIS DOESN'T WORK!!!

I tried doing EXCEPT and UNION ALL statements... but I don't need to know the row that is different or even the values that are different, I just need to know BOOLEAN 'yes' the tables are different or 'no' they are not.

Answer 1

This blog post explains how to do this:

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION

Answer 2

Something like this should work, using Exist/Not Exists - then it's up to you how to interpret either getting or not getting a row back as True or False. The actual syntax will depend on the database. This is Transact-SQL

Create table A
( one int, two int, three int , four int)

Create table B
( one int, two int, three int, four int)

insert A values ( 1,2,3,4)
insert B values( 1,2,3,4)

select * from A a
where exists ( select 1 from B b where 
    a.one = b.one
and a.two = b.two
and a.three = b.three
and a.four = b.four)

one two three four

---

1 2 3 4

Answer 3

[Revised]

Here's how you'd do this with "SELECT...EXCEPT...":

IF not exists(select *
               from MyTable
              except select *
               from MyOtherTable)
 and not exists(select *
               from MyOtherTable
              except select *
               from MyTable)
    PRINT 'They match'
ELSE
    PRINT 'They do not match'

A bit quicker to write, unless you need to compare fewer than all the columns. This will require four table scans, so you should compare and contrast performance with the UNION strategies presented. In my experience SELECT...EXCEPT... tends to run very quickly -- I'd guess because all the unioning and column comparing is occuring internally.

Answer 4

If you want to compare only few columns (or tables with 2-5 columns), you may use this FULL JOIN (not tested):

select      COUNT(*) AS UnmatchedRows
from        table1 t1
full join   table2 t2
        on  t1.col1 = t2.col1
        and t1.col2 = t2.col2
        and t1.col3 = t2.col3
        and t1.col4 = t2.col4
        and t1.col5 = t2.col5
where       COALESCE(t1.col1, t2.col1) IS NULL

Still the solution referenced by Druid is very cool.