How can I compare rows from 2 tables that have composite primary keys?

Question

Here's the scenario:

I have 2 tables with data, one is the 2009 version and the other is the 2010 version. The primary key for each of the tables is a composite key. I know there is a different number of rows in each one and I need to find out the differences.

Typically, in the "normal" primary key set-up, I would just look for primary key values NOT IN the list of primary keys from the other table. But I don't know how to do this with a composite primary key (or even if it's possible).

So, how can I compare the rows from these two tables?

EDIT: More specifically, I am trying to find the difference between the tables, rather than the rows in common

BorisOkunskiy · Accepted Answer · 2010-03-12T21:24:33.167

11

Just make a full outer join with condition based on your composite keys:

select t09.*, t10.*
from table2009 as t09
  full outer join table2010 as t10
    on t09.k1 = t10.k1 and t09.k2 = t10.k2 and ...

If you only wish to see unmatched rows (the difference) in result set, then filter them within the where clause:

where t09.k1 is null or t10.k1 is null

edited Mar 12 '10 at 21:24

answered Mar 12 '10 at 21:05

BorisOkunskiy

1,830
1
19
24

Added that "difference" you were talking about; however, you may want to find only the one side (e.g. the rows in 2010 that do not appear in 2009 table) -- then left join will do the trick. – BorisOkunskiy Mar 12 '10 at 21:27

score 1 · Answer 2 · answered Mar 12 '10 at 21:04

1

select * from [table2009] right outer join [table2010] on [table2009].[PK1] = [table2010].[PK2] and [table2009].[PK2] = [table2010].[PK2] where [table2009].[PK1] is null

answered Mar 12 '10 at 21:04

Crispy

1,396
1
8
3

davek · Answer 3 · 2010-03-12T21:11:42.367

0

You could take a checksum of the composite columns and compare across the tables, in order to make the join a bit more compact.

select ...
--------------------------
--or left outer join etc.
--------------------------
from a inner join b 
on checksum(a.col1, a.col2, a.col3) = checksum(b.col1, b.col2, b.col3)

edited Mar 12 '10 at 21:11

answered Mar 12 '10 at 21:05

davek

22,499
9
75
95

score 0 · Answer 4 · edited Mar 12 '10 at 21:39

Assuming you don't want to compare all the columns.

DECLARE @Table2009 TABLE
(  Year  INT
 ,Counter INT  IDENTITY (-2147483647 , 1)
 ,OtherData CHAR(1)
 ,PRIMARY KEY (Year, Counter)
);
DECLARE @Table2010 TABLE
(  Year  INT
 ,Counter INT  IDENTITY (-2147483647 , 1)
 ,OtherData CHAR(1)
 ,PRIMARY KEY (Year, Counter)
);
SELECT 'NOT IN Table2010' 
  ,Table2009.Year 
  ,Table2009.Counter
FROM  @Table2009 Table2009
LEFT JOIN @Table2010 Table2010 ON Table2009.Year  = Table2010.Year
          AND Table2009.Counter = Table2010.Counter
WHERE Table2010.Year IS NULL
UNION
SELECT 'NOT IN Table2010' 
  ,Table2009.Year 
  ,Table2009.Counter
FROM  @Table2010 Table2010
LEFT JOIN @Table2009 Table2009 ON Table2010.Year  = Table2009.Year
          AND Table2010.Counter = Table2009.Counter
WHERE Table2009.Year IS NULL;

score 0 · Answer 5 · answered Mar 12 '10 at 21:38

Some data to test:

CREATE TABLE Tbl_1 (
  ID1 integer NOT NULL
, ID2 int NOT NULL
, SomeData varchar(20) 
);
ALTER TABLE Tbl_1 ADD
    CONSTRAINT pk_tbl_1 PRIMARY KEY (ID1, ID2);

CREATE TABLE Tbl_2 (
  ID1 integer NOT NULL
, ID2 int NOT NULL
, SomeData varchar(20) 
);
ALTER TABLE Tbl_2 ADD
    CONSTRAINT pk_tbl_2 PRIMARY KEY (ID1, ID2);

INSERT INTO Tbl_1
 (ID1, ID2, SomeData)
VALUES
  (1, 1, '1_1')
, (2, 2, '2_2')
, (3, 3, '3_3')
;

INSERT INTO Tbl_2
 (ID1, ID2, SomeData)
VALUES
  (1, 1, '1_1')
, (3, 3, '3_3')
, (4, 4, '4_4')
;

...

-- All rows that are in the first table,
-- but not in the second one
SELECT 
     a.ID1 AS t1_ID1
    ,a.ID2 AS t1_ID2
    ,a.SomeData AS t1_SomeData
    ,b.ID1 AS t2_ID1
    ,b.ID2 AS t2_ID2
    ,b.SomeData AS t2_SomeData
FROM Tbl_1 as a
LEFT JOIN Tbl_2 as b ON b.ID1 = a.ID1 AND b.ID2 = a.ID2
WHERE b.ID1 IS NULL;

Returns:

t1_ID1  t1_ID2   t1_SomeData   t2_ID1   t2_ID2   t2_SomeData
------- -------- ------------- -------- -------- -----------
2       2        2_2           NULL     NULL     NULL

...

-- All rows that are in the second table,
-- but not in the first one
SELECT 
     a.ID1 AS t1_ID1
    ,a.ID2 AS t1_ID2
    ,a.SomeData AS t1_SomeData
    ,b.ID1 AS t2_ID1
    ,b.ID2 AS t2_ID2
    ,b.SomeData AS t2_SomeData
FROM Tbl_1 as a
RIGHT JOIN Tbl_2 as b ON b.ID1 = a.ID1 AND b.ID2 = a.ID2
WHERE a.ID1 IS NULL;

Returns:

t1_ID1  t1_ID2  t1_SomeData   t2_ID1   t2_ID2  t2_SomeData
------- ------- ------------- -------- ------- ------------
NULL    NULL    NULL          4        4       4_4

...

-- Common to both tables
SELECT 
     a.ID1 AS t1_ID1
    ,a.ID2 AS t1_ID2
    ,a.SomeData AS t1_SomeData
    ,b.ID1 AS t2_ID1
    ,b.ID2 AS t2_ID2
    ,b.SomeData AS t2_SomeData
FROM Tbl_1 as a
JOIN Tbl_2 as b ON b.ID1 = a.ID1 AND b.ID2 = a.ID2;

Returns

t1_ID1      t1_ID2      t1_SomeData    t2_ID1      t2_ID2     t2_SomeData
----------- ----------- ------------- ----------- ----------- ------------
1           1           1_1           1           1           1_1
3           3           3_3           3           3           3_3

score 0 · Answer 6 · answered Mar 19 '12 at 15:51

i used like this! with just an left outer join.

and i used another field to check the state

SELECT * FROM actas LEFT OUTER JOIN acta_detalle_proceso on actas.id = acta_detalle_proceso.id
and actas.tipo_acta_id =  acta_detalle_proceso.tipo_acta_id and acta_detalle_proceso.estado = 1
WHERE acta_detalle_proceso.id IS NULL and acta_detalle_proceso.tipo_acta_id IS NULL  ;

score 0 · Answer 7 · answered Mar 20 '12 at 11:06

-- Entity in Tbl_1 only
SELECT ID1, ID2
  FROM Tbl_1
EXCEPT
SELECT ID1, ID2
  FROM Tbl_2;

-- Entity in Tbl_2 only
SELECT ID1, ID2
  FROM Tbl_2
EXCEPT
SELECT ID1, ID2
  FROM Tbl_1;

-- Entity in both tables 
-- with same attribute values
SELECT ID1, ID2, SomeData
  FROM Tbl_1
INTERSECT 
SELECT ID1, ID2, SomeData
  FROM Tbl_2;

-- Entity in both tables but 
-- with different attribute values
SELECT DISTINCT T1.ID1, T1.ID2, 
       T1.SomeData AS Tbl_1__SomeData,
       T2.SomeData AS Tbl_2__SomeData
  FROM Tbl_1 AS T1, Tbl_2 AS T2
 WHERE T1.ID1 = T1.ID1
       AND T1.ID2 = T2.ID2
       AND T1.SomeData <> T2.SomeData;

How can I compare rows from 2 tables that have composite primary keys?

7 Answers7

Linked