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