SQL:レコードの差異を見つけたい

# 背景と状況
新システムにバージョンアップする際、端境期には一定期間、現システムと新システムの両方にデータ入力が必要になる場合があります。マスター登録がその代表ですが往々にして、まだ本番稼働していない新システムに入力をし忘れてしまいます。そうなると入力漏れしたデータを探さなければなりません。
# やりたいこと
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を使うと簡単に比較できます。

```sql: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とを主キーでフルジョインします。

```sql: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を取ることもできます。

```sql: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する際、日付の列以外を指定にします。

```sql: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を使います。

```sql: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を使うと出力が見やすいと思います。

```sql: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徹底指南書](https://www.shoeisha.co.jp/book/detail/9784798157825)