Identify rows that match on a key but differ in other columns

Question

Supposed I have table temp A and table temp B:

IF OBJECT_ID('tempdb..#A') IS NOT NULL
BEGIN DROP TABLE #A END

IF OBJECT_ID('tempdb..#B') IS NOT NULL
BEGIN DROP TABLE #B END

CREATE TABLE #A 
(
   ID Int,
   Descr VARCHAR(100),
   Qty Int
)

CREATE TABLE B
(
   ID INT,
   Descr VARCHAR(100),
   Qty INT
)

INSERT INTO #A VALUES (1, 'Data 1' , 10)
INSERT INTO #A VALUES (2, 'Data 2', 5)

INSERT INTO #B VALUES (1, 'Data x' , 8)
INSERT INTO #B VALUES (2, 'Data x' , 1)

The ID is a key in this case. The problem is, how we can fetch the column value differences and showing the column differ on our select query?

I have tried the following:

using except

SELECT * FROM A
EXCEPT
SELECT * FROM B

using join and case.. when

select Case When #A.Descr = Before.Descr THen Null Else #A.Descr End,
       Case When #A.Qty = Before.Qty THen Null Else #A.Qty End
from #A
Left Join #B Before on #A.ID = Before.ID

This query shows me if different values exist then it will show the differences column, else if no difference value found on certain column, then will show null value.

Can you recommend a better solution?

score 1 · Answer 1 · answered Sep 01 '15 at 10:26

Another solution, close one to this before:

WITH AllData AS (
    SELECT ID, Descr, Qty FROM A
    UNION ALL
    SELECT ID, Descr, Qty FROM B
)
SELECT ad.ID, ad.Descr, ad.Qty
FROM AllData ad
    JOIN A ON A.ID=ad.ID
    JOIN B ON B.ID=ad.ID
GROUP BY ad.ID, ad.Descr, ad.Qty
HAVING COUNT(*) = 1
ORDER BY ad.ID

Or this one. Fastest of those three but could be most expensive at CPU cost. (Checked for table A with 15 000 rows and B with 30 000)

WITH AllData AS (
    SELECT ID, Descr, Qty FROM A 
    UNION
    SELECT ID, Descr, Qty FROM B 
)
SELECT ad.ID, ad.Descr, ad.Qty
FROM AllData ad
    JOIN A ON A.ID=ad.ID
    JOIN B ON B.ID=ad.ID
ORDER BY ad.ID

score 1 · Accepted Answer · answered Feb 03 '15 at 13:05

I think that this is what you're looking for...

WITH AllData AS (
  -- Get All Records that exist in both tables
  SELECT ID, Descr, Qty FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
  UNION ALL
  SELECT ID, Descr, Qty FROM B WHERE EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
)
SELECT ID, Descr, Qty
FROM AllData
GROUP BY ID, Descr, Qty
HAVING COUNT(*) = 1
ORDER BY ID

It first selects all of the rows where the ID exists in both tables (since if an ID exists in only one table, it can't have different values in the other table.) Then it groups by the columns that you want to compare. If the count is > 1, then they matched. If the count = 1 then there was a difference. Here is the SQL Fiddle that I used to verify this.

Edit: For large data sets, the performance will likely be poor. But indexing your tables should help with that.

Identify rows that match on a key but differ in other columns

2 Answers2