CODEWITHSUNDEEP
sqlsql-server
Greg
Greg

Reputation: 1387

check if all table rows are equal

There are two tables with identical structure.
Let's assume the number of rows in both is equal.
how would you check if all rows are equal? Is there any faster way than comparing every column value of a given row with the same id in both tables?

Upvotes: 12

Views: 19295

Answers (2)

Eric Brown
Eric Brown

Reputation: 409

Abe's answer is correct, but only if they have the same number of rows. (I misread the question when I wrote my original response "condemning" his answer.) If table1 may be a subset of (a larger) table2 or vice versa, I would try:

if 
(
not exists ( select * from table1
             except
             select * from table2
           )
and
not exists ( select * from table2
             except
             select * from table1
           )
)

This gives true if they are the same, and false if they are different.

Upvotes: 8

Abe Miessler
Abe Miessler

Reputation: 85126

Try this:

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

If anything is returned then they are not equal.

Upvotes: 21

Related Questions