背景と状況

新システムにバージョンアップする際、端境期には一定期間、現システムと新システムの両方にデータ入力が必要になる場合があります。マスター登録がその代表ですが往々にして、まだ本番稼働していない新システムに入力をし忘れてしまいます。そうなると入力漏れしたデータを探さなければなりません。

やりたいこと

2つのテーブルを比較して異なるレコードのみを出力したい。要はSQLでデータを比較したい。
※IBM Db2 V11.1 Windowsで確認していますが他のDBMSでもできるはずです。

比較するテーブル

同じスキーマで内容の異なるテーブルを比較します。別のデータベースから一方のテーブルをコピーして名前を変更します。

現システムのマスター　mst_tbl_current

<-主キー->
MST_ID      MST_TEXT        MST_PRICE   MST_DATE
----------- --------------- ----------- ----------
          1 リンゴ                  200 2020-01-01
          2 バナナ                  150 2020-01-01
          3 イチゴ                  500 2020-01-01
          4 ミカン                  300 2020-01-01
          6 メロン                  900 2020-01-01 <-- 新システムにないレコード

新システムのマスター　mst_tbl_new

<-主キー->
MST_ID      MST_TEXT        MST_PRICE   MST_DATE
----------- --------------- ----------- ----------
          1 リンゴ                  200 2020-01-01
          2 バナナ                  150 2020-09-15 <-- 日付が違う
          3 イチゴ    価格が違う --> 650 2020-01-01
          4 ミカン                  300 2020-01-01
          5 スイカ                  850 2020-01-01 <-- 現システムにないレコード

簡単な比較方法

exceptを使うと簡単に比較できます。

except_mst_tbl_current.sql

SELECT * 
FROM   mst_tbl_current 
EXCEPT 
SELECT * 
FROM   mst_tbl_new 
ORDER  BY mst_id　;

現システムのテーブルを基準に新システムと異なるレコードのみを照会しています。

実行結果

F:\>db2 -tvf except_mst_tbl_current.sql
SELECT * FROM   mst_tbl_current EXCEPT SELECT * FROM   mst_tbl_new ORDER  BY mst_id　

MST_ID      MST_TEXT        MST_PRICE   MST_DATE
----------- --------------- ----------- ----------
          2 バナナ                  150 2020-01-01
          3 イチゴ                  500 2020-01-01
          6 メロン                  900 2020-01-01

  3 レコードが選択されました。

バナナは日付、イチゴは価格が異なり、メロンは現システムのみにあるレコードです。

問題点

単純にexceptを使うと新システムだけにあるスイカが表示されません。
逆に新システムを基準にして照会すると現システムだけにあるメロンが表示されません。
直感的にわかりにくく、コレジャナイ感がありますね。

対策

現システムを基準にしたexceptと新システムを基準にしたexceptとを主キーでフルジョインします。

except_mst_tbl_comp.sql

SELECT CASE 
         WHEN c.mst_id IS NULL THEN n.mst_id 
         ELSE c.mst_id 
       END         AS MST_ID, 
       c.mst_text  AS Cur_MST_TEXT, 
       c.mst_price AS Cur_MST_PRICE, 
       c.mst_date  AS Cur_MST_DATE, 
       n.mst_text  AS New_MST_TEXT, 
       n.mst_price AS New_MST_PRICE, 
       n.mst_date  AS New_MST_DATE 
FROM   (SELECT * 
        FROM   mst_tbl_current 
        EXCEPT 
        SELECT * 
        FROM   mst_tbl_new) c 
       FULL JOIN (SELECT * 
                  FROM   mst_tbl_new 
                  EXCEPT 
                  SELECT * 
                  FROM   mst_tbl_current) n 
              ON n.mst_id = c.mst_id 
ORDER  BY mst_id;

片方にしかないレコードも含めて異なるレコードのみが出力されます。

実行結果

F:\>db2 -tvf except_mst_tbl_comp.sql
SELECT CASE WHEN c.mst_id IS NULL THEN n.mst_id ELSE c.mst_id END         AS MST_ID, c.mst_text  AS Cur_MST_TEXT, c.mst_price AS Cur_MST_PRICE, c.mst_date  AS Cur_MST_DATE, n.mst_text  AS New_MST_TEXT, n.mst_price AS New_MST_PRICE, n.mst_date  AS New_MST_DATE FROM   (SELECT * FROM   mst_tbl_current EXCEPT SELECT * FROM   mst_tbl_new) c FULL JOIN (SELECT * FROM   mst_tbl_new EXCEPT SELECT * FROM   mst_tbl_current) n ON n.mst_id = c.mst_id ORDER  BY mst_id

MST_ID      CUR_MST_TEXT    CUR_MST_PRICE CUR_MST_DATE NEW_MST_TEXT    NEW_MST_PRICE NEW_MST_DATE
----------- --------------- ------------- ------------ --------------- ------------- ------------
          2 バナナ                    150 2020-01-01   バナナ                    150 2020-09-15
          3 イチゴ                    500 2020-01-01   イチゴ                    650 2020-01-01
          5 -                           - -            スイカ                    850 2020-01-01
          6 メロン                    900 2020-01-01   -                           - -

  4 レコードが選択されました。

思っていたイメージのものができました。さらに差分を比較したいなら以下のSQLでdiffを取ることもできます。

except_mst_tbl_diff.sql

(SELECT 'MST_TBL_CURRENT' AS SOURCE, 
        * 
 FROM   (SELECT * 
         FROM   mst_tbl_current 
         EXCEPT 
         SELECT * 
         FROM   mst_tbl_new)) 
UNION ALL 
(SELECT 'MST_TBL_NEW' AS SOURCE, 
        * 
 FROM   (SELECT * 
         FROM   mst_tbl_new 
         EXCEPT 
         SELECT * 
         FROM   mst_tbl_current)) 
ORDER  BY mst_id;

差異があるレコードは2行で、片方にしかないレコードは1行で出力されます。

実行結果

F:\>db2 -tvf except_mst_tbl_diff.sql
(SELECT 'MST_TBL_CURRENT' AS SOURCE, * FROM   (SELECT * FROM   mst_tbl_current EXCEPT SELECT * FROM   mst_tbl_new)) UNION ALL (SELECT 'MST_TBL_NEW' AS SOURCE, * FROM   (SELECT * FROM   mst_tbl_new EXCEPT SELECT * FROM   mst_tbl_current)) ORDER  BY mst_id

SOURCE          MST_ID      MST_TEXT        MST_PRICE   MST_DATE
--------------- ----------- --------------- ----------- ----------
MST_TBL_NEW               2 バナナ                  150 2020-09-15
MST_TBL_CURRENT           2 バナナ                  150 2020-01-01
MST_TBL_NEW               3 イチゴ                  650 2020-01-01
MST_TBL_CURRENT           3 イチゴ                  500 2020-01-01
MST_TBL_NEW               5 スイカ                  850 2020-01-01
MST_TBL_CURRENT           6 メロン                  900 2020-01-01

  6 レコードが選択されました。

さらに

全ての列ではなく特定の列だけ比較したい場合はどうすればいいでしょうか。例えば、日付の列は比較対象から外してみます。方法としてはexceptでselectする際、日付の列以外を指定にします。

except_mst_tbl_current_part.sql

SELECT mst_id, 
       mst_text, 
       mst_price
FROM   mst_tbl_current 
EXCEPT 
SELECT mst_id, 
       mst_text, 
       mst_price 
FROM   mst_tbl_new 
ORDER  BY mst_id　;

バナナは日付が異なりますが比較対象外なので同じとみなされ、出力されません。

実行結果

F:\>db2 -tvf except_mst_tbl_current_part.sql
SELECT mst_id, mst_text, mst_price FROM   mst_tbl_current EXCEPT SELECT mst_id, mst_text, mst_price FROM   mst_tbl_new ORDER  BY mst_id　

MST_ID      MST_TEXT        MST_PRICE
----------- --------------- -----------
          3 イチゴ                  500
          6 メロン                  900

  2 レコードが選択されました。

但し、これだと日付列を表示することができません。比較対象外にしたいが列は表示したいときはnot existsを使います。

not_exists_mst_tbl_current.sql

SELECT * 
FROM   mst_tbl_current c 
WHERE  NOT EXISTS (SELECT * 
                   FROM   mst_tbl_new n 
                   WHERE  c.mst_id = n.mst_id 
                          AND c.mst_text = n.mst_text 
                          AND c.mst_price = n.mst_price) 
ORDER  BY mst_id　;

こちらは日付を比較対象にせず、日付列は表示します。

実行結果

F:\>db2 -tvf not_exists_mst_tbl_current.sql
SELECT * FROM   mst_tbl_current c WHERE  NOT EXISTS (SELECT * FROM   mst_tbl_new n WHERE  c.mst_id = n.mst_id AND c.mst_text = n.mst_text AND c.mst_price = n.mst_price) ORDER  BY mst_id　

MST_ID      MST_TEXT        MST_PRICE   MST_DATE
----------- --------------- ----------- ----------
          3 イチゴ                  500 2020-01-01
          6 メロン                  900 2020-01-01

  2 レコードが選択されました。

最終形

指定した列のみを比較して異なるレコードのみを表示させたい場合、SQLは複雑になりますがnot existsを使うと出力が見やすいと思います。

not_exists_mst_tbl_comp.sql

SELECT CASE 
         WHEN c.mst_id IS NULL THEN n.mst_id 
         ELSE c.mst_id 
       END         AS MST_ID, 
       c.mst_text  AS Cur_MST_TEXT, 
       c.mst_price AS Cur_MST_PRICE, 
       c.mst_date  AS Cur_MST_DATE, 
       n.mst_text  AS New_MST_TEXT, 
       n.mst_price AS New_MST_PRICE, 
       n.mst_date  AS New_MST_DATE 
FROM   (SELECT * 
        FROM   mst_tbl_current c 
        WHERE  NOT EXISTS (SELECT * 
                           FROM   mst_tbl_new n 
                           WHERE  c.mst_id = n.mst_id 
                                  AND c.mst_text = n.mst_text 
                                  AND c.mst_price = n.mst_price)) c 
       FULL JOIN (SELECT * 
                  FROM   mst_tbl_new n 
                  WHERE  NOT EXISTS (SELECT * 
                                     FROM   mst_tbl_current c 
                                     WHERE  n.mst_id = c.mst_id 
                                            AND n.mst_text = c.mst_text 
                                            AND n.mst_price = c.mst_price)) n 
              ON n.mst_id = c.mst_id 
ORDER  BY mst_id;

以下が出力結果です。バナナは日付が異なりますが比較対象外ですので同じとみなされ、出力されません。

実行結果

F:\>db2 -tvf not_exists_mst_tbl_comp.sql
SELECT CASE WHEN c.mst_id IS NULL THEN n.mst_id ELSE c.mst_id END         AS MST_ID, c.mst_text  AS Cur_MST_TEXT, c.mst_price AS Cur_MST_PRICE, c.mst_date  AS Cur_MST_DATE, n.mst_text  AS New_MST_TEXT, n.mst_price AS New_MST_PRICE, n.mst_date  AS New_MST_DATE FROM   (SELECT * FROM   mst_tbl_current c WHERE  NOT EXISTS (SELECT * FROM   mst_tbl_new n WHERE  c.mst_id = n.mst_id AND c.mst_text = n.mst_text AND c.mst_price = n.mst_price)) c FULL JOIN (SELECT * FROM   mst_tbl_new n WHERE  NOT EXISTS (SELECT * FROM   mst_tbl_current c WHERE  n.mst_id = c.mst_id AND n.mst_text = c.mst_text AND n.mst_price = c.mst_price)) n ON n.mst_id = c.mst_id ORDER  BY mst_id

MST_ID      CUR_MST_TEXT    CUR_MST_PRICE CUR_MST_DATE NEW_MST_TEXT    NEW_MST_PRICE NEW_MST_DATE
----------- --------------- ------------- ------------ --------------- ------------- ------------
          3 イチゴ                    500 2020-01-01   イチゴ                    650 2020-01-01
          5 -                           - -            スイカ                    850 2020-01-01
          6 メロン                    900 2020-01-01   -                           - -

  3 レコードが選択されました。

まとめ

簡単に比較したいならexcept
主キーでフルジョインすることによる見やすい出力
列指定で細かく制御したいならnot exists

参考

以下の書籍を参考にしています。
達人に学ぶSQL徹底指南書