How to compare rows from 2 tables with compound primary keys?

Question

Here's the script:

I have 2 tables with data, one of them 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 that each of them has several lines, and I need to find out the differences.

As a rule, in setting up a "normal" primary key, I would simply look for the values ​​of the primary key NOT IN in the list of primary keys from another table. But I do not know how to do this with a composite primary key (or even if it is possible).

So how can I compare rows from these two tables?

---

EDIT: In particular, I am trying to find the difference between tables and not series in common

Answer 1

, :

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

() , where:

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

Answer 2

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

Answer 3

, .

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

, .

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

:

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;

:

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;

:

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;

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

Answer 6

! .

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  ;

Answer 7

-- 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;