CODEWITHSUNDEEP
sqlsqlite
Mark Harrison
Mark Harrison

Reputation: 304584

Differencing two tables in a SQL database?

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:

For example, given the data below, identify:

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

Upvotes: 0

Views: 97

Answers (1)

Mark Harrison
Mark Harrison

Reputation: 304584

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

Upvotes: 1

Related Questions