[小ネタ]SQL – EXCEPTを利用した重複排除インサート

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

SQLでテーブルへのデータINSERT時にデータ重複を除外する方法はいくつかあります。 その中で、集合演算子EXCEPTを利用した重複除外の方法を始めて知ったのでまとめたいと思います。

• 参考）集合演算子 — Snowflake Documentation

さきに結論

結論から書くと、以下のようなクエリを利用すると「データ登録元テーブルsrc_tblに重複データがあった場合に、データ登録先テーブルdest_tblが空テーブルであってもデータ重複無しで登録できる」という話です。

-- EXCEPTを利用してデータ登録先テーブルへ登録
INSERT INTO dest_tbl
SELECT id FROM src_tbl
EXCEPT
SELECT id FROM dest_tbl;

直感的なEXCEPTの利用方法

まず最初に直感的なEXCEPTでの利用方法についてです。以下のようなtbl_aとtbl_bがあったとします。

tbl_a

id
1
2

tbl_b

id
1

このとき、以下のクエリを実行します。

SELECT id FROM tbl_a
EXCEPT
SELECT id FROM tbl_b;

結果として出力されるのは以下になります。

id
2

これは非常に直感的でわかりやすいですね。ある集合Aから集合Bを取り除いただけです。イメージとして以下となります。

では、以下の場合はどうでしょうか？

tbl_a

id
1
2

tbl_b

id
(空)

これも簡単ですね。取り除く集合Bは空なので、集合Aはそのままです。

id
1
2

EXCEPTを利用した重複排除インサート

では本題です。今度は集合Bが空だった場合にEXCEPTを利用してINSERTするとどうなるでしょうか？

実際に試すクエリは以下のとおりです。

-- データ登録元、登録先テーブルを作成
CREATE TABLE src_tbl(
  id INTEGER
);
CREATE TABLE dest_tbl(
  id INTEGER
);

-- 重複値を持つデータをデータ登録元テーブルに登録
INSERT INTO src_tbl (id)
VALUES (1), (2), (2);

この時点ではこうですね。

src_tbl

id
1
2
2

dest_tbl

id
(空)

続けて下記のクエリを実行します。

-- EXCEPTを利用してデータ登録先テーブルへ登録
INSERT INTO dest_tbl
SELECT id FROM src_tbl
EXCEPT
SELECT id FROM dest_tbl;

-- 登録先テーブルをSELECT
SELECT id FROM dest_tbl;

結果は、以下になります。

dest_tbl

id
1
2

どうでしょうか？私は最初意味が分かりませんでした。

EXCEPTは標準SQL「SQL-92」で採用されていますが、念の為以下のDBでも挙動が同じか調べてみました。

• Redshift
• Snowflake
• PostgreSQL
• SQL Server

結果としては、いずれも挙動は同じでした。

このことから、INSERT-SELECTにおいてEXCEPTを用いると実行時に重複排除の評価がされているということになる認識です。とても意外でした。

大量データの処理時には、パフォーマンス影響があるかどうかは要検証ですが、少量データであれば問題なさそうです。ただ、直感的に理解しにくい面もあるので個人的にはおすすめできないかなと思いました。

まとめ

以上、EXCEPTを利用した重複排除インサートについてでした。SQLって面白いですね。

なお、今回書いた件は新卒研修の課題の中でJatinさんに回答として出してもらったもので始めて知りました。勉強になります。

どなたかのお役に立てば幸いです。それでは！