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

Answer 1

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

Answer 2

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

Answer 3

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)

Answer 4

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;

Answer 5

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