Difference between two datasets with which column has changed

Transact-SQL
1

Table 1
ID Data1 Data2
1 Dog Cat
2 Rabbit Frog

Table 2
ID Data1 Data2
1 Dog Lion
2 Rabbit Frog
3 Snake Lizard

Result set

Data1 Change1 Data2 Change2
Dog False Lion True
Snake True Lizard True

1 Like
2 
SELECT T2.Data1, CASE WHEN T1.Data1 = T2.Data1 THEN 'True' ELSE 'False' END AS Change1,
       T2.Data2, CASE WHEN T1.Data2 = T2.Data2 THEN 'True' ELSE 'False' END AS Change2
FROM Table1 AS T1
     JOIN Table2 AS T2
          ON T2.ID = T1.ID

EDIT: Corrected version as per @ScottPletcher version below

SELECT T2.Data1, CASE WHEN T1.Data1 = T2.Data1 THEN 'True' ELSE 'False' END AS Change1,
       T2.Data2, CASE WHEN T1.Data2 = T2.Data2 THEN 'True' ELSE 'False' END AS Change2
FROM Table2 AS T2
     LEFT OUTER JOIN Table1 AS T1
          ON T1.ID = T2.ID
2 Likes
3 
SELECT t2.ID,
    t2.Data1,
    CASE WHEN t1.Data1 IS NULL OR t1.Data1 <> t2.Data1 THEN 'True' ELSE 'False' END AS Change1,
    t2.Data2,
    CASE WHEN t1.Data2 IS NULL OR t1.Data2 <> t2.Data2 THEN 'True' ELSE 'False' END AS Change2
FROM Table2 t2
LEFT OUTER JOIN Table1 t1 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR (t1.Data1 <> t2.Data1 OR t1.Data2 <> t2.Data2)
1 Like
4

I did mine the other way round:

CASE WHEN T1.Data1 = T2.Data1 THEN 'True' ELSE 'False' END AS Change1

to avoid having to make the test for IS NULL

Anything I missed in doing that?

5

You did an INNER JOIN, so you couldn't test for unmatched Table2 data at all. I'm trying to explicitly indicate a missing row condition. I don't see how your code could ever return the "Snake Lizard" row.

6

Indeed, I noted that you had used OUTER JOIN (didn't bother to check if it was needed and comment though, sorry about that [I'll fix mine now], I just assumed as your code was more thorough than mine yours would be right :slight_smile: )

Curious about the IS NULL point though, do you have a preference / opinion on that?

7

If it makes the code clearer, I will add an IS NULL check. That's a bit comparison so it doesn't really hurt performance.

8

Interesting, so your code is explicitly providing a hint (for future maintenance / DEV) that the IS NULL state is anticipated, whereas my code is just accommodating that as a "side effect"

I need to calculate how much more profit I can make for the extra typing :toast: :sunglasses: