CODEWITHSUNDEEP
sqlsql-server
Ludovic Aubert
Ludovic Aubert

Reputation: 10526

Query to compare two tables with the same structure?

I want to compare two tables with the same exact structure (same column names).

DROP TABLE IF EXISTS my_before;
DROP TABLE IF EXISTS my_after;

CREATE TABLE my_before
(
     pk INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
     myvalue INT NULL
);

CREATE TABLE my_after
(
     pk INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
     myvalue INT NULL
);

INSERT INTO my_before (myvalue) VALUES (10), (NULL), (12), (NULL);
INSERT INTO my_after (myvalue) VALUES (10), (11), (NULL), (NULL), (NULL), (14);

I want to output the difference between my_before and my_after.

enter image description here

What would the query look like?

Upvotes: 1

Views: 164

Answers (3)

Max Zolotenko
Max Zolotenko

Reputation: 1132

Records missed in "after" table:

select * from my_before
except
select * from my_after

Records missed in "before" table:

select * from my_after
except
select * from my_before

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You would do this using full join, but the correct logic (to handle NULL values) is:

SELECT a.*, b.*
FROM my_before a FULL OUTER JOIN
     my_after b
     ON a.pk = b.pk
WHERE a.pk IS NULL OR
      b.pk IS NULL OR
      a.my_value <> b.my_value OR
      (a.my_value IS NULL AND b.my_value IS NOT NULL) OR
      (a.my_value IS NOT NULL AND b.my_value IS NULL);

Upvotes: 3

Ludovic Aubert
Ludovic Aubert

Reputation: 10526

SELECT a.*, b.*
FROM my_before AS a
FULL OUTER JOIN my_after AS b ON a.pk=b.pk
WHERE a.pk IS NULL OR b.pk IS NULL
 OR COALESCE(a.myvalue,0) != COALESCE(b.myvalue,0)

enter image description here

Without the COALESCE, the result would be completely different (and wrong)

SELECT a.*, b.*
    FROM my_before AS a
    FULL OUTER JOIN my_after AS b ON a.pk=b.pk
    WHERE a.pk IS NULL OR b.pk IS NULL
     OR a.myvalue != b.myvalue

enter image description here

Upvotes: 1

Related Questions