とあるデータベースのテーブルと、そのテーブルと構造が全く同じ他のテーブルを比較する方法をご紹介します。
SQLと言ってもデータベースによって多少異なりますが、今回はOracleをベースにご紹介していきたいと思います。
基本的なSQLは他のデータベースと変わりません。
今回使う演算子はMINUSとUNIONです！
MINUS（EXCEPT）演算子
MINUSとEXCEPTのどちらの演算子を使うかですが、MINUSはOracle、EXCEPTはPostgreSQLやSQLServerなどで使うそうな。
試してはないですが、OracleでもEXCEPTを使えるっぽいです。
もちろん結果は変わりません。
UNION（UNION ALL）演算子
次に検索結果を統合するUNION演算子です。
ここで気になるのが、似たような意味を持つJOINがありますが、これは“統合“ではなく”結合“なのでお間違えないように。
二つの意味を比較してみると、統合は重複する2つのデータがあれば1つにまとめる、結合は同じキーが存在したら後ろ（もしくは前）にくっつけるという意味です。
以下のサイトにUNIONに関する詳しい内容がありますので参考までに。
差分比較SQL
それでは、上記のMINUSとUNIONを利用して2つの同じ構造のテーブルの差分を抽出するSQL文を作成します。
具体的には「差集合」と言うらしいです。
MINUS（マイナス）はその名の通り、計算式で表すと「TABLE_A – TABLE_B」となります。
単純に差分を取得するだけなら、
|
1 2 3 |
SELECT * FROM TABLE_A MINUS SELECT * FROM TABLE_B |
となりますが、このままだと差分があっても片方のテーブルのレコードしか表示されないため、どのカラムで差分が発生しているか一目でわかりません。
比較するにはもう片方のデータも出力する必要があります。
その場合はテーブルを逆にしてMINUSしてあげれば良いです。
|
1 2 3 |
SELECT * FROM TABLE_B MINUS SELECT * FROM TABLE_A |
そして、これら2つの差分を統合して一気に出力する場合はUNIONを使って、以下のようなSQL文を流します。
|
1 2 3 4 5 6 7 8 9 10 11 |
( SELECT * FROM TABLE_A MINUS SELECT * FROM TABLE_B ) UNION ALL ( SELECT * FROM TABLE_B MINUS SELECT * FROM TABLE_A ) |
これで、全カラムでどれか1つでも差分があるデータを含む、TABLE_AとTABLE_B両方のレコードを出力します。
【おまけ】もっと比較しやすく！
さらに！さらに！！
上記の方法だけでは、どちらのレコードが出力されているか判り辛いです。
そこで、テーブル名をカラムとして出力しちゃって、さらに共通するキー項目（主キーなど）でソートしてあげましょう！
|
1 2 3 4 5 6 7 8 9 10 11 12 |
( 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句を使ってまとめちゃいます！
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 LIKE '%Japan%' ORDER BY 2; |
ざっとこんな感じです。
説明すると、まず差分を検索した結果をWITH句を使って宣言したDIFFというビュー？(仮想のテーブル？)に入れちゃいます！
そして、最後にそのDIFFから、WHERE句でADDRESSにJapanを含む差分のみを検索して結果を返しています。以上です！
いかがでしたでしょうか？
期待通りに動いて、皆さんのお役に立てれば幸いです。
ポチップ
ポチップ
ポチップ
コメント
コメント一覧 （2件）
載せるSQLはちゃんとテストしましょう。
OracleだとMINUSより先にUNION ALLが走る。
hogeraさん、ご指摘ありがとうございます！
おっしゃる通り、OracleのSQLではMINUS演算子がUNION ALLよりも先に評価されます。
このため、意図しない結果が得られる可能性があります。
MINUSよりもUNION ALLが先に実行されると、その結果にMINUSが適用されることになり、期待する差分の抽出ができません。
掲載していたSQLを改善しました。
改善点として、UNION ALLとMINUSの間に括弧を追加し、それぞれの結果を明示的に区別しています。