【SQL】2つのテーブルのデータを比較して差分を検証する

sql-minus-unionテクノロジー

とあるデータベースのテーブルと、そのテーブルと構造が全く同じ他のテーブル比較する方法をご紹介します。
SQLと言ってもデータベースによって多少異なりますが、今回はOracleをベースにご紹介していきたいと思います。
基本的なSQLは他のデータベースと変わりません。
今回使う演算子MINUSUNIONです!

MINUS(EXCEPT)演算子

MINUSEXCEPTのどちらの演算子を使うかですが、MINUSOracleEXCEPTPostgreSQLSQLServerなどで使うそうな。

試してはないですが、OracleでもEXCEPTを使えるっぽいです。
もちろん結果は変わりません。

UNION(UNION ALL)演算子

次に検索結果を統合するUNION演算子です。
ここで気になるのが、似たような意味を持つJOINがありますが、これは統合“ではなく”結合なのでお間違えないように。
二つの意味を比較してみると、統合は重複する2つのデータがあれば1つにまとめる、結合は同じキーが存在したら後ろ(もしくは前)にくっつけるという意味です。
以下のサイトにUNIONに関する詳しい内容がありますので参考までに。

差分比較SQL

それでは、上記のMINUSUNIONを利用して2つの同じ構造のテーブルの差分を抽出するSQL文を作成します。
具体的には「差集合」と言うらしいです。
MINUSマイナス)はその名の通り、計算式で表すと「TABLE_A – TABLE_B」となります。
単純に差分を取得するだけなら、

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B

となりますが、このままだと差分があっても片方のテーブルのレコードしか表示されないため、どのカラムで差分が発生しているか一目でわかりません。
比較するにはもう片方のデータも出力する必要があります。
その場合はテーブルを逆にしてMINUSしてあげれば良いです。

SELECT * FROM TABLE_B
MINUS
SELECT * FROM TABLE_A

そして、これら2つの差分統合して一気に出力する場合はUNIONを使って、以下のようなSQL文を流します。

SELECT * FROM TABLE_A
MINUS
SELECT * FROM TABLE_B
UNION ALL
SELECT * FROM TABLE_B
MINUS
SELECT * FROM TABLE_A

これで、全カラムでどれか1つでも差分があるデータを含む、TABLE_ATABLE_B両方のレコードを出力します。

【おまけ】もっと比較しやすく!

さらに!さらに!!
上記の方法だけでは、どちらのレコードが出力されているか判り辛いです。
そこで、テーブル名カラムとして出力しちゃって、さらに共通するキー項目(主キーなど)でソートしてあげましょう!

SELECT 'TABLE_A' AS table_name A.* FROM TABLE_A A
MINUS
SELECT 'TABLE_B' AS table_name B.* FROM TABLE_B B
UNION ALL
SELECT 'TABLE_B' AS table_name B.* FROM TABLE_B B
MINUS
SELECT 'TABLE_A' AS table_name A.* FROM TABLE_A A
ORDER BY 2

【おまけのおまけ】絞り込む!

もっと絞り込むためにWHERE句を付けましょうか。
SELECT文が4つあるので、普通ならそれぞれにWHERE句を付けるのですが、今回の例では2つのテーブル構造が全く同じなので、それでは大変面倒です。
そこで、WITH句を使ってまとめちゃいます!

WITH DIFF AS (
SELECT 'TABLE_A' AS table_name A.* FROM TABLE_A A
MINUS
SELECT 'TABLE_B' AS table_name B.* FROM TABLE_B B
UNION ALL
SELECT 'TABLE_B' AS table_name B.* FROM TABLE_B B
MINUS
SELECT 'TABLE_A' AS table_name A.* FROM TABLE_A A
)
SELECT * FROM DIFF
WHERE ADDRESS = '%Japan%'
ORDER BY 2

ざっとこんな感じです。
説明すると、まず差分を検索した結果をWITH句を使って宣言したDIFFというビュー?(仮想のテーブル?)に入れちゃいます!
そして、最後にそのDIFFから、WHERE句でADDRESSにJapanを含む差分のみを検索して結果を返しています。以上です!
いかがでしたでしょうか?
期待通りに動いて、皆さんのお役に立てれば幸いです。

東京生まれ福岡育ちの文系プログラマー。
テクノロジーの恩恵を感じながら日々精進しています。
広く浅くをモットーに、最近ではプログラミングだけでなく、仮想サーバーからセキュリティ、監視システムなども勉強中です。

管理者をフォローする

コメント

タイトルとURLをコピーしました