【SQL】NOT EXISTSで片方のテーブルに存在しないデータを検索する

本記事の内容は PostgreSQL 15.4で検証しています。

以降のSQLは学習目的のサンプルであり、テーブル設計やデータ量は説明を分かりやすくするために単純化しています。

やりたいこと

顧客情報を保持している2つのテーブルのうち、片方に存在しない顧客を見つけたいというケースを考えます。例えば、基準となる顧客一覧（customers1）と、更新後の顧客一覧（customers2）があり、customers2に存在しない顧客だけを抽出するイメージです。

差分抽出を行うときに、LEFT OUTER JOINを使う方法とNOT EXISTSを使う方法で書き方や実行計画がどう変わるかを確認します。

テーブル定義

まず、テスト用の顧客テーブルを作成します。generate_seriesで100万件の顧客データを生成し、customers1に挿入します。その後、customers1の全件をcustomers2にコピーして、最初は同じ内容にします。

INSERT INTO customers1 (user_id, first_name, last_name)
SELECT
    'user_' || gs AS user_id,
    'first_' || gs AS first_name,
    'last_'  || gs AS last_name
FROM generate_series(1, 1000000) AS gs;

INSERT INTO customers2
SELECT * FROM customers1;

次に、customers2から一部の顧客を削除します。これにより「customers1に存在がcustomers2には存在しない顧客」を作ります。

DELETE FROM customers2 WHERE user_id = 'user_53';
DELETE FROM customers2 WHERE user_id = 'user_9000';
DELETE FROM customers2 WHERE user_id = 'user_39000';
DELETE FROM customers2 WHERE user_id = 'user_89008';
DELETE FROM customers2 WHERE user_id = 'user_500000';
DELETE FROM customers2 WHERE user_id = 'user_990000';

上記の6件が「customers2に存在しない顧客」として抽出対象になります。

SQL作成

JOINを使ったSQL

LEFT OUTER JOINでcustomers1を起点にcustomers2を結合し、結合できなかった行（c2.user_idがNULL）を抽出するというSQLを作成します。

SELECT * FROM customers1 c1
LEFT OUTER JOIN customers2 c2
ON c1.user_id = c2.user_id
WHERE c2.user_id IS NULL;

NOT EXISTSを使ったSQL

NOT EXISTSを使った場合、「customers1の各行についてcustomers2に対応する行が存在しないこと」を条件として書けます。存在しないことをそのまま表現できるため、意図が読み取りやすい書き方です。

SELECT * FROM customers1 c1 
WHERE NOT EXISTS (SELECT 1 FROM customers2 c2 WHERE c2.user_id = c1.user_id);

実行計画の確認

LEFT OUTER JOINを使ったSQLの実行計画

実行計画は以下のようになりました。どのような結合方式が選ばれ、どこで絞り込みが行われているかを確認します。

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=21954.94..45776.74 rows=6 width=68) (actual time=2061.351..2838.521 rows=6 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Hash Anti Join  (cost=20954.94..44776.14 rows=2 width=68) (actual time=2156.968..2741.558 rows=2 loops=3)
         Hash Cond: (c1.user_id = c2.user_id)
         ->  Parallel Seq Scan on customers1 c1  (cost=0.00..12490.67 rows=416667 width=34) (actual time=0.336..23.570 rows=333333 loops=3)
         ->  Parallel Hash  (cost=12490.64..12490.64 rows=416664 width=34) (actual time=1019.469..1019.469 rows=333331 loops=3)
               Buckets: 131072  Batches: 16  Memory Usage: 5472kB
               ->  Parallel Seq Scan on customers2 c2  (cost=0.00..12490.64 rows=416664 width=34) (actual time=0.067..30.333 rows=333331 loops=3)
 Planning Time: 0.642 ms
 Execution Time: 2838.586 ms
(11 rows)

実行計画を見ると、Parallel Hash Anti Joinが使われています。これは「片方に存在しない行を見つける」ための結合方式で、LEFT JOIN + IS NULLの意図に対応します。

customers1とcustomers2の双方がSeq Scanで読み取られ、customers2側はHash化されてから結合されます。今回のデータ量では並列処理が選択されています。

NOT EXISTSを使ったSQLの実行計画

NOT EXISTSでもどのような結合が選ばれるかを確認します。

test_db1=# EXPLAIN ANALYZE SELECT * FROM customers1 c1
WHERE NOT EXISTS (SELECT 1 FROM customers2 c2 WHERE c2.user_id = c1.user_id);
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=20733.94..43334.74 rows=6 width=34) (actual time=1705.720..2105.190 rows=6 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Hash Anti Join  (cost=19733.94..42334.14 rows=2 width=34) (actual time=1614.883..2047.514 rows=2 loops=3)
         Hash Cond: (c1.user_id = c2.user_id)
         ->  Parallel Seq Scan on customers1 c1  (cost=0.00..12490.67 rows=416667 width=34) (actual time=0.329..24.338 rows=333333 loops=3)
         ->  Parallel Hash  (cost=12490.64..12490.64 rows=416664 width=11) (actual time=545.384..545.385 rows=333331 loops=3)
               Buckets: 262144  Batches: 8  Memory Usage: 7968kB
               ->  Parallel Seq Scan on customers2 c2  (cost=0.00..12490.64 rows=416664 width=11) (actual time=0.038..37.530 rows=333331 loops=3)
 Planning Time: 31.774 ms
 Execution Time: 2105.217 ms
(11 rows)

こちらもParallel Hash Anti Joinが選択されており、結合方式自体はJOIN版と同じです。NOT EXISTSは内部的に「存在しない行を探す結合」に変換されるため、実行計画が似通うケースが多いです。

実行計画の比較

JOINはExecution Timeが約2839ms、NOT EXISTSは約2105msで、今回のデータ量ではNOT EXISTSの方が少し速かったのですが、どちらもParallel Hash Anti Joinのため実行計画の形はほぼ同じです。

今回の差は、読み込む列数やハッシュ作成時のデータ幅などによる影響と考えられます。データ量が増える場合は差が大きくなる可能性があるため、実データでの検証が重要です。

NOT EXISTSの方が意図を読み取りやすく、SQLもシンプルに書けるため、存在しないデータの検索ではNOT EXISTSを第一候補にして良さそうです。

PostgreSQL,SQL

Posted by fanfanta