title: Differencing two tables in a SQL database?
body: <p>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:</p>
  <ul>
  <li>inserted rows:  The key appears in B, but not in A.</li>
  <li>deleted rows:  The key appears in A, but not in B.</li>
  <li>modified: The key appears in both tables, but with different values.</li>
  <li>unmodified:  the key appears in both tables, and the values are the same.</li>
  </ul>
  <p>For example, given the data below, identify:</p>
  <ul>
  <li>a is modified, 1 --&gt; 9</li>
  <li>b is deleted</li>
  <li>c is unmodified</li>
  <li>d is inserted</li>
  </ul>
  <pre><code>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
  </code></pre>