Differencing two tables in a SQL database?

Question

How can I compare two tables with unique keys and generate a difference set that can transform one table into the other? For example, given tables A and B, identify:

• inserted rows: The key appears in B, but not in A.
• deleted rows: The key appears in A, but not in B.
• modified: The key appears in both tables, but with different values.
• unmodified: the key appears in both tables, and the values are the same.

For example, given the data below, identify:

• a is modified, 1 --> 9
• b is deleted
• c is unmodified
• d is inserted

CREATE TABLE A AS SELECT 'a' AS key, 1 AS val
    UNION SELECT 'b', 2
    UNION SELECT 'c', 3;

CREATE TABLE b AS SELECT 'a' AS key, 9 AS val
    UNION SELECT 'c', 3
    UNION SELECT 'd', 4;

SELECT * FROM a ORDER BY KEY;
a,1
b,2
c,3

SELECT * FROM b ORDER BY KEY;
a,9
c,3
d,4

Answer 1

These queries will perform the specified operations.

• Modified Rows

select b.key, b.val from a left join b
    on a.key = b.key where a.val <> b.val;
a,9

• Deleted Rows

select key, val from a where key not in (select key from b);
b,2

• Unmodified Rows

select key, val from a intersect select key, val from b;
c,3

• Inserted Rows

select key, val from b where key not in (select key from a);
d,4

Note that if you have multiple columns in your key, you can specify them as (key1, key2, ...), etc. For example, if the tables had two key columns the Inserted query would be

select key1, key2, val from b
    where (key1, key2) not in (select key1, key2 from a);