CODEWITHSUNDEEP
sqlsql-server
user2761667
user2761667

Reputation: 63

Counting the number of rows which do not match

I have two tables with exactly the same structure i.e. same columns. I want to find out number of rows which exist in both tables but do not have exactly the same matching values for all columns. E.g. Table has colums Id, Name, Country. Id is the primary key. If Id=1 exists in both tables then other values should also match. Currently, I am using this kind of statement.

SELECT  COUNT(*)
FROM    ##Table1 t1 ,
        ##Table2 t2
WHERE   t1.Id = t2.Id
        AND ( t1.Name != t2.name
              OR t1.Country != t2.Country
            )

Table has too many columns hence this is becoming too unwieldly. Is there a better way to do it?

Upvotes: 6

Views: 2386

Answers (2)

Martin Smith
Martin Smith

Reputation: 453727

SELECT COUNT(*)
FROM   ##Table1 t1
       JOIN ##Table2 t2
         ON t1.Id = t2.Id
            AND EXISTS (SELECT t1.*
                        EXCEPT
                        SELECT t2.*)

SQL Fiddle

Upvotes: 4

Fast Old Man
Fast Old Man

Reputation: 105

Perhaps you can concatenate the columns into a single field and check for equality that way?

Upvotes: 0

Related Questions