CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
ruohola
ruohola

Reputation: 24038

How to check if the contents of two columns from different tables are identical?

I have two tables in my SQL database. I want to check if the Specifier columns have the exact same data in the exact same order.

OK case, since both tables have the same data in the same order in the Specifier column:

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     1         
183     2

ERROR case, since the data is different:

-- Table1:
RowID   Specifier
187     1         
188     2         
189     3         

-- Table2:
RowID   Specifier
181     1         
182     2         
183     2

ERROR case, since the data is in different order:

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     2         
183     1

ERROR case, since different amount of data:

-- Table1:
RowID   Specifier
187     1         
188     1         
189     2         

-- Table2:
RowID   Specifier
181     1         
182     1         
183     2
184     1

I've written the following query, which almost works, and gives correctly an error if one table has a value the other doesn't, but it will incorrectly give no error if only the order is incorect:

IF EXISTS
    (SELECT Specifier FROM Table1 EXCEPT SELECT Specifier FROM Table2
    UNION ALL
    SELECT Specifier FROM Table2 EXCEPT SELECT Specifier FROM Table1)
BEGIN
    THROW 99999, 'Mismatching Specifiers between the two tables', 1;
END;

Upvotes: 1

Views: 1039

Answers (2)

Thom A
Thom A

Reputation: 95561

Seems like it might be easier with

IF EXISTS (SELECT 1
           FROM (SELECT ROW_NUMBER() OVER (ORDER BY RowID) AS RN,
                        Specifier
                 FROM Table1) T1
                FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY RowID) AS RN,
                                        Specifier
                                 FROM Table2) T2 ON T1.RN = T2.RN
                                                AND T1.Specifier = T2.Specifier
           HAVING COUNT(CASE WHEN T1.RN IS NULL OR T2.RN IS NULL THEN 1 END) >= 1) ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can use full join and row_number(). The following gets the exceptions:

select *
from (select t1.*, row_number() over (order by rowid) as seqnum
      from table1 t1
     ) t1 full join
     (select t2.*, row_number() over (order by rowid) as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum and t1.specifier = t2.specifier
where t1.seqnum is null or t2.seqnum is null;

If you simply want a flag:

select (case when count(*) > 1 then 1 else 0 end)
from (select t1.*, row_number() over (order by rowid) as seqnum
      from table1 t1
     ) t1 full join
     (select t2.*, row_number() over (order by rowid) as seqnum
      from table2 t2
     ) t2
     on t1.seqnum = t2.seqnum and t1.specifier = t2.specifier
where t1.seqnum is null or t2.seqnum is null;

If you care about performance, using the first query with exists should be faster.

Upvotes: 3

Related Questions