Query Diffing

Given tables or query results A and B, we want to 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

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