テーブルの変更（INSERT,UPDATE,DELETE）の更新前後の差分を確認するSQL

サンプル

テーブル T1 に対して更新を行い

T1

ID	VALUE
1	aa
2	bb
3	cc
4	aa
5	dd

更新後の状態を T2 とした場合
T2

ID	VALUE
1	aa
3	kk
4	aa
5	dd
6	ee

更新内容は以下の通り
ID=2のレコードは削除されている
ID=3のレコードは値が更新されている
ID=6のレコードは追加されている

更新内容を一覧で確認できるSQLです。

SELECT
  ID
, VALUE
, SUM(FLG) AS CHK
, CASE SUM(FLG) WHEN 1 THEN '旧' WHEN 2 THEN '新' END AS 新旧
FROM 
  (
  SELECT * , 1 AS FLG FROM T1
  UNION
  SELECT * , 2 AS FLG FROM T2
  ) AS T
GROUP BY
  ID
, VALUE
-- GROUP BYには、テーブルのすべてのカラムを指定します
HAVING SUM(FLG) <> 3
ORDER BY ID

結果

ID	VALUE	CHK	新旧
2	bb	1	旧
3	cc	1	旧
3	kk	2	新
6	ee	2	新

CHK列が
1のみのレコード⇒削除されたレコード
1,2のレコード⇒いずれかの値が更新されたレコード（1が更新前、2が更新後）
2のみのレコード⇒追加されたレコード
3のみのレコード(HAVING句で対象外としているので表示されない)⇒変更のなかったレコード
となります。

WINDOW関数が使えるなら、より分かり易く以下のようにすることも可能です。

WITH SUB AS (
SELECT
  ID
, VALUE
, SUM(FLG) AS CHK
FROM 
  (
  SELECT * , 1 AS FLG FROM T1
  UNION
  SELECT * , 2 AS FLG FROM T2
  ) AS T
GROUP BY
  ID
, VALUE
-- GROUP BYには、テーブルのすべてのカラムを指定します
)

SELECT
  ID
, VALUE
, CHK
, CASE CHK 
  WHEN 1 THEN
    -- PARTITION BY にはテーブルのKEYを指定する(複数列の指定も可)
    CASE WHEN COUNT(CHK) OVER(PARTITION BY ID) = 2 THEN '更新前' ELSE '削除' END
  WHEN 2 THEN
    CASE WHEN COUNT(CHK) OVER(PARTITION BY ID) = 2 THEN '更新後' ELSE '追加' END
-- WHEN 3 THEN
--  '変更なし'
  END AS 変更
FROM SUB
 WHERE CHK <> 3
ORDER BY 
  ID
, CHK

結果

ID	VALUE	CHK	変更
2	bb	1	削除
3	cc	1	更新前
3	kk	2	更新後
6	ee	2	追加

応用記事