SQL パフォーマンス最適化 — NOT IN/NULL・Anti Joinの基礎

QUESTION 6

NOT IN と NULL の罠 — 除外条件は NOT EXISTS で Anti Join に

NOT EXISTS三値論理NULLHash Anti Join

前提知識 ▼

「〜に存在しない行」を探すとき、NOT IN (サブクエリ) には2つの罠があります。第一に正しさの罠：サブクエリの結果に NULL が1つでも含まれると、SQL の三値論理により x NOT IN (...) は全行で UNKNOWN（真でも偽でもない）になり、結果が0行になります。第二に性能の罠：NULL の可能性を考慮するためプランナが効率的な Anti Join 系のプランを選びにくくなります。

-- ✗ サブクエリに NULL が混ざると結果0行 + プランも非効率
WHERE member_id NOT IN (SELECT member_id FROM event_entries)

-- ✓ NULL の影響を受けず Hash Anti Join に最適化される
WHERE NOT EXISTS (SELECT 1 FROM event_entries e
                  WHERE e.member_id = m.member_id)

三値論理の核心：x NOT IN (2, 4, NULL) は x <> 2 AND x <> 4 AND x <> NULL と展開されます。最後の x <> NULL は常に UNKNOWN なので AND 全体も真になれず、どの行も WHERE を通過できません。「データが増えたある日、突然0件になった」の典型原因です。

問題 ▼

members テーブル（実体10万行）と event_entries テーブル（実体300万行・member_id は NULL 許容でゲスト参加を表す）があります。イベントに一度も参加していないメンバーを、NULL に壊されず、Anti Join に最適化される形で取得してください。出力列は member_id, name（member_id 昇順）。

使用テーブル

- members

member_id	name
1	Aoki
2	Baba
3	Chiba
4	Doi
5	Endo
6	Fujii

- event_entries（member_id は NULL 許容）

entry_id	member_id
1	2
2	4
3	NULL
4	2

期待出力 ▼

member_id	name
1	Aoki
3	Chiba
5	Endo
6	Fujii

模範解答コード ▼

SELECT   m.member_id, m.name
FROM     members m
WHERE    NOT EXISTS (              -- 「対応行が見つからない」を直接表現
           SELECT 1
           FROM   event_entries e
           WHERE  e.member_id = m.member_id  -- NULL は = で一致しないので無害
         )
ORDER BY m.member_id;

/*
  実行順序:
  1. event_entries でハッシュ表を構築  → member_id を登録
  2. members を走査しハッシュ照合       → Hash Anti Join
  3. 見つからない行だけ通過              → WHERE を通す
  4. member_id 昇順に整列          → 返却
  */

解説（テーブル変化・ポイント） ▼

SELECT   m.member_id, m.name
FROM     members m
WHERE    NOT EXISTS (
  SELECT 1 FROM event_entries e
  WHERE e.member_id = m.member_id
)
ORDER BY m.member_id;

LEGEND ▼

データ取得・読込対象

1. 対象テーブル — members（外側） ▼

FROM members m「参加していない人」を探す対象です。実体は10万行。1行ずつサブクエリを投げ直すのではなく、相手側を1回だけ読んで照合表を作るのが理想形です。

1 / 5

member_id	name
1	Aoki
2	Baba
3	Chiba
4	Doi
5	Endo
6	Fujii

6行（実テーブルは10万行）

学習ポイント

NOT IN は「比較の連鎖」、NOT EXISTS は「存在の判定」：NOT IN は値同士の <> を AND で繋いだものなので、NULL が1つ入ると全体が UNKNOWN に落ちます。NOT EXISTS は「一致行が見つかったか」だけを見るため NULL の影響を受けません。意味的に欲しいのは後者なので、除外条件は最初から NOT EXISTS で書く癖をつけると事故が消えます。

性能面でも NOT EXISTS が優位：PostgreSQL は NOT EXISTS を Hash Anti Join / Merge Anti Join に変換でき、両テーブルを各1回ずつ読むだけで済みます。NOT IN は「NULL が来たら結果が変わる」という意味論を守る必要があるため最適化の自由度が低く、列が NULL 許容だと巨大な実行計画（全行に対する具体化サブプラン評価）に化けることがあります。

第3の書き方 LEFT JOIN … IS NULL：LEFT JOIN event_entries e ON … WHERE e.entry_id IS NULL でも同じ結果が得られ、プランもほぼ同等の Anti Join になります。ただし結合キーが重複していると結合段階で行が膨らむ（後段の DISTINCT が必要になる）ため、可読性と安全性の点で第一候補は NOT EXISTS、LEFT JOIN 方式は除外と同時に相手の列も見たいときの選択肢、と覚えておきましょう。

アンチパターン

「今は NULL が無いから」NOT IN を放置する：NOT IN が正しく動くかどうかはコードでなくデータが決めます。今日 NULL が無くても、明日のインポートで1行混ざれば結果が0件に変わります。列定義が NULL 許容である限り、NOT IN は時限爆弾です。どうしても使うなら WHERE member_id IS NOT NULL をサブクエリに明示します。

除外リストをアプリ側で配列にして渡す：30万件の ID を NOT IN (1, 2, 3, …) の巨大リテラルとして SQL に埋め込むと、パース・プラン作成だけで重くなります。除外対象がテーブルにあるならサブクエリ/結合で DB 内で照合、アプリ由来なら一時テーブルや = ANY(配列) の否定形ではなく NOT EXISTS + VALUES 句を検討します。

実務コラム：NULL は「値」ではなく「不明」

SQL の NULL は「空文字」でも「0」でもなく「値が不明」というマークです。だから NULL = NULL も NULL <> 1 も答えは UNKNOWN（不明同士は比べられない）。WHERE 句は「真」の行しか通さないので、UNKNOWN は false と同じ扱いで落ちます。この三値論理は NOT IN のほかにも、col <> 'x' が NULL 行を返さない、COUNT(col) が NULL を数えない、など至るところに顔を出します。「否定形・集約・比較を書くときは、まず NULL が来たらどうなるかを1秒考える」——これだけで本番障害の一群を未然に防げます。次の Q7 では、同じテーブルを何度も走査してしまう「集計の重ね掛け」を1回の走査にまとめる技を見ます。

QUESTION 7

条件付き集約 — FILTER / CASE で複数の全表走査を1回にまとめる

FILTER句条件付き集約CASE式走査1回

前提知識 ▼

「completed の件数と売上、pending の件数、cancelled の件数」のように同じテーブルから条件違いの集計を複数取りたいとき、集計ごとにサブクエリやクエリを分けるとテーブルを集計の数だけ走査してしまいます。条件付き集約を使えば、1回の走査で各行を読みながら「どのカウンタに加算するか」を振り分けられます。

-- ✗ 集計の数だけ全表走査（500万行 × 4回）
SELECT
  (SELECT COUNT(*) FROM orders WHERE status = 'completed'),
  (SELECT SUM(amount) FROM orders WHERE status = 'completed'), ...

-- ✓ 1回の走査で全集計（PostgreSQL の FILTER 句）
SELECT COUNT(*) FILTER (WHERE status = 'completed'), ...
FROM orders;

移植性メモ：FILTER 句は標準SQLですが未対応のDBもあります。その場合は SUM(CASE WHEN status = 'completed' THEN amount END) / COUNT(CASE WHEN … THEN 1 END) が完全な等価表現です（CASE の ELSE 省略時は NULL になり、SUM / COUNT が NULL を無視する性質を利用）。

問題 ▼

orders テーブル（実体500万行）から、1回の走査だけで次の4つの値を1行で取得してください：completed_cnt（completed の件数）、completed_amt（completed の売上合計）、pending_cnt（pending の件数）、cancelled_cnt（cancelled の件数）。

使用テーブル

- orders

order_id	status	amount
1	completed	1200
2	pending	800
3	completed	2000
4	cancelled	500
5	completed	700
6	pending	1500

期待出力 ▼

completed_cnt	completed_amt	pending_cnt	cancelled_cnt
3	3900	2	1

模範解答コード ▼

SELECT
  COUNT(*)    FILTER (WHERE status = 'completed') AS completed_cnt,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_amt,
  COUNT(*)    FILTER (WHERE status = 'pending')   AS pending_cnt,
  COUNT(*)    FILTER (WHERE status = 'cancelled') AS cancelled_cnt
FROM orders;   -- テーブル走査はこの1回だけ

/*
  実行順序:
  1. orders を先頭から1回だけ走査（Seq Scan）
  2. 行を読むたびに、各集約の FILTER 条件を判定
  3. 条件を満たした集約のカウンタ/合計にだけ加算（満たさない集約はスキップ）
  4. 全行を読み終えたら 4つの集約値を1行にして返却
  */

解説（テーブル変化・ポイント） ▼

SELECT
  COUNT(*)    FILTER (WHERE status = 'completed'),
  SUM(amount) FILTER (WHERE status = 'completed'),
  COUNT(*)    FILTER (WHERE status = 'pending'),
  COUNT(*)    FILTER (WHERE status = 'cancelled')
FROM orders;

LEGEND ▼

グループ化キー・集計対象

グループ分類

1. 対象テーブル — orders ▼

FROM ordersstatus 列で3種類に分かれたデータです。背景色は status のグループを表します。この6行（実体500万行）を何回読むかが今回の論点です。

1 / 5

order_id	status	amount
1	completed	1200
2	pending	800
3	completed	2000
4	cancelled	500
5	completed	700
6	pending	1500

6行（実テーブルは500万行）

学習ポイント

I/O は「走査回数 × テーブルサイズ」で決まる：集計を分けても1本にまとめても、計算する内容は同じです。違うのはディスク/キャッシュからの読込回数だけ。条件判定（FILTER / CASE）は読み込んだ行に対する CPU 演算なので、I/O を1回に圧縮して CPU に肩代わりさせるのがこのパターンの本質です。大きなテーブルほど効果が劇的になります。

FILTER と CASE は等価、使い分けは可読性と移植性：COUNT(*) FILTER (WHERE c) = COUNT(CASE WHEN c THEN 1 END)、SUM(x) FILTER (WHERE c) = SUM(CASE WHEN c THEN x END)。FILTER のほうが意図が読みやすく、PostgreSQL ではどの集約関数にも付けられる（AVG, MIN, MAX, ARRAY_AGG…）のが強みです。MySQL など未対応DBへ移植する可能性があるなら CASE 式で書きます。

GROUP BY と直交して使える：条件付き集約は GROUP BY region などと組み合わせると「地域ごとの status 別件数」のようなクロス集計（行→列のピボット）が1走査で完成します。BIツールに渡す前のサマリ作成、日次バッチのKPI算出など、実務での出番が非常に多い形です（総合問題で実際に組み合わせます）。

アンチパターン

アプリ側でクエリをループ発行する：「status のリストを取って、status ごとに COUNT のクエリを投げる」実装は、走査の重複に加えてネットワーク往復（N+1問題）まで抱え込みます。集計のバリエーションが動的でも、GROUP BY status 1本で全グループぶんを取得し、表示側で並べ替えるのが正解です。

UNION で集計を縦に積む：SELECT 'completed', COUNT(*) … UNION SELECT 'pending', COUNT(*) … も走査が集計の数だけ発生する点はサブクエリ4本と同じです。さらに UNION（ALL なし）だと不要な重複排除コストまで上乗せされます（→ Q9）。縦持ちが欲しいなら GROUP BY status、横持ちが欲しいなら FILTER、と覚えてください。

実務コラム：「1パスで済むか」をクエリ設計の口癖に

データ処理の世界では「同じデータを何度も読む設計は負け」という感覚が共通言語になっています。条件付き集約はその最小単位の実践です。応用として、複数の粒度の集計（日別と月別など）は GROUPING SETS / ROLLUP で1走査に、集計と明細の同時取得はウィンドウ関数で1走査に、それぞれまとめられます。逆に「1パスにこだわりすぎて1本の巨大クエリが解読不能になる」のも本末転倒なので、走査回数を意識した上で、CTE で論理構造を整理するのがバランスの良い落とし所です。次の Q8 では、読み終わった後の工程——ソート——を index で丸ごと省略する方法を見ます。

QUESTION 8

ORDER BY とインデックス — ソート工程を消す・Top-N heapsort と work_mem

ORDER BYwork_memTop-N heapsortSortノード消滅

前提知識 ▼

ORDER BY は「最後にちょっと並べ替えるだけ」に見えて、実は全行が揃わないと開始できないブロッキング工程です。行数が多いとメモリ（work_mem）に収まらず、ディスクを使った外部ソート（external merge）に転落して激しく遅くなります。一方、B-tree index は常にソート済みなので、ORDER BY の列と index の並びが一致していればソート工程そのものを消せます。

-- ✗ index なし: 全行読込 → ソート → 先頭3件
Limit → Sort (Sort Method: top-N heapsort) → Seq Scan

-- ✓ published_at の index あり: ソート工程が消える
Limit → Index Scan Backward using idx_articles_pub
       -- 並び順は index が保証。3件読んだら即終了

Sort Method を読む：EXPLAIN ANALYZE の Sort ノードには方式が表示されます。quicksort（全件がメモリ内）、top-N heapsort（LIMIT 付きで上位N件だけ保持）、external merge（work_mem 超過でディスク使用 = 危険信号）。Disk: nnnn kB が見えたら、index でソートを消すか work_mem を見直すサインです。

問題 ▼

articles テーブル（実体200万行）から最新の記事3件を published_at の降順で取得してください。あわせて、このクエリのソート工程を消すためのインデックスを定義してください。出力列は article_id, title, published_at。

使用テーブル

- articles（ヒープ上は挿入順で並んでいない）

article_id	title	published_at
1	PostgreSQL入門	2026-05-01
2	インデックス設計	2026-06-09
3	実行計画の読み方	2026-04-12
4	JOINの基礎	2026-06-11
5	NULLの話	2026-03-30
6	ソートとメモリ	2026-06-02
7	CTE活用	2026-05-20

期待出力 ▼

article_id	title	published_at
4	JOINの基礎	2026-06-11
2	インデックス設計	2026-06-09
6	ソートとメモリ	2026-06-02

模範解答コード ▼

CREATE INDEX idx_articles_pub
  ON articles (published_at DESC);  -- ORDER BY と同じ並びの index を用意

SELECT   article_id, title, published_at
FROM     articles
ORDER BY published_at DESC   -- index の並びと一致 → Sort ノード不要
LIMIT    3;                    -- 3件読んだ時点で走査も終了

/*
  実行順序:
  1. idx_articles_pub の先頭（published_at が最大の側）に位置づけ
  2. index 順に 1エントリずつ読み、ヒープから3列を取得
  3. 3行返した時点で LIMIT が走査を打ち切り（Sort 工程は存在しない）
  */

解説（テーブル変化・ポイント） ▼

CREATE INDEX idx_articles_pub
  ON articles (published_at DESC);

SELECT   article_id, title, published_at
FROM     articles
ORDER BY published_at DESC
LIMIT    3;

LEGEND ▼

データ取得・読込対象

1. 対象テーブル — ヒープは日付順に並んでいない ▼

FROM articles（物理順 = 挿入や更新の都合）テーブル本体（ヒープ）の行は published_at の順には並んでいません。「並び」が欲しければ誰かが作る必要がある——それをクエリ実行時の Sort でやるか、index に前払いさせるかが分かれ道です。

1 / 5

article_id	title	published_at
1	PostgreSQL入門	2026-05-01
2	インデックス設計	2026-06-09
3	実行計画の読み方	2026-04-12
4	JOINの基礎	2026-06-11
5	NULLの話	2026-03-30
6	ソートとメモリ	2026-06-02
7	CTE活用	2026-05-20

7行（実テーブルは200万行）

学習ポイント

ソートは「ブロッキング + メモリ食い」の二重苦：Sort ノードは入力が全部揃うまで1行も出力できず、行数に応じてメモリを消費します。work_mem（既定 4MB）を超えると一時ファイルへの書き出し（external merge）が始まり、I/O が爆発します。EXPLAIN ANALYZE で Sort Method と Disk: を確認する習慣をつけましょう。

index は「ソートの前払い」：B-tree は挿入のたびに正しい位置へ値を置くことで、常時ソート済み状態を維持しています。ORDER BY の列・方向・NULL の扱い（NULLS FIRST/LAST）が index 定義と一致すれば、プランナは Sort ノードを丸ごと省略できます。昇順 index でも逆向き読み（Index Scan Backward）ができるため、単一列なら ASC/DESC どちらの ORDER BY にも対応可能です。

WHERE と ORDER BY を1本の index で兼ねる：WHERE category = 'tech' ORDER BY published_at DESC LIMIT 3 のような形は、複合 index (category, published_at DESC) で「絞り込み → 整列済み区間の先頭3件」が一撃になります。基礎編1で学んだ「等値 → 範囲」の列順ルールの続きとして、「等値 → ソート列」も同じ発想で並べる、と覚えてください。

アンチパターン

work_mem を全体で爆上げして解決した気になる：external merge を見て work_mem = 1GB のような設定にすると、ソートはセッション・ノードごとに work_mem を消費するため、同時実行で簡単にメモリ枯渇（OOM）します。まず index でソート自体を消す、消せない分析クエリだけ SET LOCAL work_mem でセッション限定に増やす、が正しい順序です。

表示用の多段 ORDER BY を盲目的に index 化する：ORDER BY a, b DESC, c のような画面都合の並びに毎回 index を貼ると index が乱立します。Sort が問題になるのは「行数が多い × 実行頻度が高い」場合だけ。数百行を画面表示用に並べ替えるソートは放置してよく、本当に重い新着系・ランキング系クエリに絞って index を設計します。

実務コラム：ソートはどこに現れるか — ORDER BY だけではない

Sort ノードは ORDER BY 以外からも生まれます。Merge Join の前処理、DISTINCT や GROUP BY（ハッシュ方式が選ばれない場合）、ウィンドウ関数の PARTITION BY / ORDER BY、そして次の Q9 で見る UNION の重複排除。EXPLAIN を読むときは「この Sort は誰の都合で入ったのか」を特定するのが第一歩です。発生源が分かれば、index で消す・ハッシュ方式に誘導する・そもそも重複排除を不要にする、と対処が選べます。「遅いクエリの裏に黙って立っている Sort を見つける」——これが中級への入り口です。

QUESTION 9

UNION と UNION ALL — 不要な重複排除のコストを払わない

UNION ALL重複排除AppendSort/Hash回避

前提知識 ▼

UNION と UNION ALL の違いは「重複を消すか・消さないか」ですが、性能面ではまったく別物です。UNION は重複排除のために結合後の全行・全列を対象にソートまたはハッシュ化を行います。UNION ALL は2つの結果をつなげるだけ（Append）で、追加コストはほぼゼロ。重複が起こり得ない設計なら、重複排除は払う必要のない税金です。

-- UNION: Append のあとに重複排除（全行が Sort / HashAggregate を通る）
HashAggregate  -- ← 500万行ぶんのハッシュ表 or ソート
  └─ Append → Seq Scan ×2

-- UNION ALL: つなげるだけ
Append → Seq Scan ×2   -- 重複排除工程そのものが存在しない

判断基準はデータ設計：「国内注文と海外注文」「今月分とアーカイブ分」のように行の出どころが排他なら重複は構造的に発生しません。消す重複が無いのに UNION を書くのは、全行ソートを無償奉仕しているのと同じです。逆に重複があり得て消したいときだけ UNION を選びます。

問題 ▼

国内注文 domestic_orders（実体300万行）と海外注文 overseas_orders（実体200万行）は注文IDの体系が分かれており、同じ注文が両方に入ることはありません。2つを統合した注文一覧（order_id, customer, amount）を、不要な重複排除コストを発生させない形で取得してください。

使用テーブル

- domestic_orders（order_id は 1000番台）

order_id	customer	amount
1001	Sato	5000
1002	Suzuki	3200
1003	Tanaka	7800

- overseas_orders（order_id は 9000番台）

order_id	customer	amount
9001	Smith	12000
9002	Lee	4500

期待出力 ▼

order_id	customer	amount
1001	Sato	5000
1002	Suzuki	3200
1003	Tanaka	7800
9001	Smith	12000
9002	Lee	4500

模範解答コード ▼

SELECT order_id, customer, amount
FROM   domestic_orders
UNION ALL                       -- 出どころが排他 → 重複排除は不要
SELECT order_id, customer, amount
FROM   overseas_orders;

/*
  実行順序:
  1. domestic_orders を走査し、行をそのまま出力ストリームへ流す
  2. 続けて overseas_orders を走査し、後ろに連結（Append）
  3. ソート・ハッシュ・重複比較は一切行わない
  */

解説（テーブル変化・ポイント） ▼

SELECT order_id, customer, amount
FROM   domestic_orders
UNION ALL
SELECT order_id, customer, amount
FROM   overseas_orders;

LEGEND ▼

グループ化キー・集計対象

グループ分類

1. 入力 — 出どころが排他な2テーブル ▼

domestic_orders / overseas_ordersID 体系（1000番台 / 9000番台）が分かれているため、同一行が両方に存在することは構造上あり得ません。背景色は出どころを表します。この「設計上の保証」が UNION ALL を選ぶ根拠です。

1 / 5

出どころ	order_id	customer	amount
domestic	1001	Sato	5000
domestic	1002	Suzuki	3200
domestic	1003	Tanaka	7800
overseas	9001	Smith	12000
overseas	9002	Lee	4500

3行 + 2行（実体は300万 + 200万行）

学習ポイント

UNION の重複排除は「全列・全行」が対象：消すかどうかの判定は SELECT したすべての列の組で行われます。列が多い・行が長いほどハッシュ表やソートキーが太り、コストは行数以上に膨らみます。さらに Sort と同じくブロッキング（全行揃うまで出力できない）なので、LIMIT 付きクエリやストリーミング処理との相性も最悪です。

UNION ALL はプランナにも優しい：Append は各ブランチを独立に最適化でき、外側の WHERE 条件を各ブランチに押し込む（プルーニング）ことも可能です。テーブルパーティショニングが内部的に Append で動くのも同じ仕組み。月別テーブルの横断検索など「分割して保存し、ALL で束ねる」設計は PostgreSQL と相性の良い王道パターンです。

重複を消したい場合もまず「どこで重複が生まれるか」：本当に重複があり得るケースでも、UNION でまとめて消すより各ブランチの WHERE で発生源を断つ（例：移行期間の二重登録を期間条件で除外）ほうが安いことが多いです。Q3（基礎編2前半）の「OR を UNION に分解」で UNION を使ったのは、同一テーブル由来で同じ行が両ブランチにヒットし得たから——使い分けの良い対比例です。

アンチパターン

「とりあえず UNION」をテンプレ化する：「ALL を付け忘れて重複が出るより安全だから」と常に UNION で書くチームは、データ増加とともに静かに遅くなる集合演算を量産します。正しくは逆で、デフォルトは UNION ALL、重複排除は明示的な意思決定。コードレビューで「この UNION、消したい重複は何？」と聞ける文化が健全です。

UNION を DISTINCT の代わりに使う：1つのクエリの重複を消したいだけなのに … UNION SELECT … (同じクエリ) や、逆に UNION ALL の結果へ後から SELECT DISTINCT を被せる書き方は、走査やソートを二重に払います。重複の発生源（JOIN による行膨張など）を特定し、EXISTS 化や結合条件の修正で根本から断つのが先決です。

実務コラム：ALL の有無は「意味」の宣言でもある

UNION ALL と UNION の選択は性能だけの話ではなく、「この2つの集合は排他である」という設計知識の表明でもあります。レビューアは UNION ALL を見れば「重複しない前提なのだな」と読み取れますし、その前提が崩れたら（ID 体系の統合など）修正点も明確です。逆に根拠なき UNION は「重複するかもしれないし、しないかもしれない」という曖昧さの放置。クエリは仕様書の一部——速い書き方は、たいてい意図の明確な書き方でもあります。仕上げの Q10 では、ここまでの NOT EXISTS・FILTER・UNION ALL を1本のレポートSQLに組み上げます。

QUESTION 10

総合問題 — UNION ALL + NOT EXISTS + FILTER でレポートSQLを組み立てる

総合UNION ALLNOT EXISTSFILTER集約

前提知識 ▼

基礎編2後半の総仕上げです。実務のレポートSQLは、今回学んだ部品の組み合わせでできています：① 複数ソースの統合は UNION ALL、② 除外条件は NOT EXISTS（NULL 安全 + Anti Join）、③ 条件別の集計は FILTER で1走査。逆に悪い見本は「UNION + NOT IN + 集計ごとのサブクエリ」——同じ結果を何倍ものコストで（しかも NULL 次第で間違った結果を）返します。

-- 組み立ての型（内側 → 外側へ）
SELECT 集約 FILTER (...)        -- ③ 1走査で条件別集計
FROM ( ... UNION ALL ... ) s    -- ① 排他ソースの統合
WHERE NOT EXISTS ( ... )        -- ② NULL 安全な除外
GROUP BY ...

読み方のコツ：複合クエリはデータの流れ（FROM → WHERE → GROUP BY → SELECT）の順に読みます。「統合 → 除外 → 集計」という処理の物語が、そのまま実行順序です。

問題 ▼

出荷レポートを作ります。国内出荷 shipments_jp と海外出荷 shipments_intl（ID体系は排他・実体それぞれ数百万行）を統合し、テストアカウントの出荷を除外した上で、region ごとに pending / shipped の件数を集計してください。test_accounts.customer_id は NULL 許容（申請中の行が混ざる）です。出力列は region, pending_cnt, shipped_cnt（region 昇順）。

使用テーブル

- shipments_jp

ship_id	status	customer_id
1	pending	101
2	shipped	102
3	pending	103
4	shipped	101

- shipments_intl

ship_id	status	customer_id
51	pending	201
52	shipped	202
53	shipped	999

- test_accounts（customer_id は NULL 許容）

account_id	customer_id
1	999
2	NULL

期待出力 ▼

region	pending_cnt	shipped_cnt
INTL	1	1
JP	2	2

模範解答コード ▼

SELECT   s.region,
         COUNT(*) FILTER (WHERE s.status = 'pending') AS pending_cnt,
         COUNT(*) FILTER (WHERE s.status = 'shipped') AS shipped_cnt
FROM (
  SELECT 'JP'   AS region, status, customer_id FROM shipments_jp
  UNION ALL                       -- 排他ソースの統合は ALL（重複排除コストゼロ）
  SELECT 'INTL', status, customer_id FROM shipments_intl
) s
WHERE    NOT EXISTS (              -- NULL 安全な除外（Hash Anti Join）
           SELECT 1 FROM test_accounts t
           WHERE  t.customer_id = s.customer_id
         )
GROUP BY s.region
ORDER BY s.region;

/*
  実行順序:
  1. shipments_jp / intl を Append で連結  → region を付与
  2. test_accounts でハッシュ表を構築           → 照合用
  3. 一致行を除外                            → Anti Join
  4. region でグループ化し FILTER 集計          → 各カウンタへ加算
  5. region 昇順に整列                      → 返却
  */

解説（テーブル変化・ポイント） ▼

SELECT   s.region,
  COUNT(*) FILTER (WHERE s.status = 'pending'),
  COUNT(*) FILTER (WHERE s.status = 'shipped')
FROM (
  SELECT 'JP' AS region, status, customer_id
  FROM shipments_jp
  UNION ALL
  SELECT 'INTL', status, customer_id
  FROM shipments_intl
) s
WHERE    NOT EXISTS (
  SELECT 1 FROM test_accounts t
  WHERE t.customer_id = s.customer_id
)
GROUP BY s.region
ORDER BY s.region;

LEGEND ▼

データ取得・読込対象

1. 入力 — 排他な2ソース + 除外リスト ▼

shipments_jp / shipments_intl / test_accounts出荷データはID体系が排他な2テーブルに分かれ、test_accounts には customer_id = 999 と NULL の行があります。この NULL が NOT IN を使った瞬間にレポート全体を0行にする地雷です。

1 / 5

ソース	ship_id	status	customer_id
JP	1	pending	101
JP	2	shipped	102
JP	3	pending	103
JP	4	shipped	101
INTL	51	pending	201
INTL	52	shipped	202
INTL	53	shipped	999

JP 4行 + INTL 3行 / 除外リスト {999, NULL}

学習ポイント

各テーブル「1回ずつ」が達成できているか数える：このクエリの走査回数は shipments_jp ×1、shipments_intl ×1、test_accounts ×1 の合計3回で打ち止めです。悪い見本（UNION + NOT IN + ステータス別サブクエリ×2）だと出荷テーブルだけで4回以上走査し、さらに全行の重複排除まで払います。「FROM に出てくるテーブルを実行計画で数える」のは複合クエリ検証の最速チェックです。

正しさと速さは同じ選択から生まれる：NOT EXISTS を選んだ理由は「NULL に壊されない」（正しさ）と「Anti Join になる」（速さ）の両方、UNION ALL を選んだ理由も「排他という設計の表明」（正しさ）と「Append のみ」（速さ）の両方でした。良いSQLでは正しさの根拠と速さの根拠が一致する——これが基礎編を貫く原則です。

派生テーブルと述語の押し込み：外側に WHERE s.region = 'JP' のような条件を足すと、プランナは条件を UNION ALL の各ブランチへ押し込み、INTL 側の走査を丸ごと省略できます（Append のプルーニング）。「まず統合してから絞る」と書いても「絞ってから統合」に最適化される——UNION ALL がパーティショニングの土台になっている理由です。

アンチパターン

レポートSQLを「動いたら触らない」聖域にする：レポート系クエリはデータ増加の影響を最も受けやすいのに、出力が合っていると放置されがちです。UNION の ALL 漏れ・NOT IN・集計サブクエリの重複走査は今日学んだ3点セットでそのまま点検できます。月次で EXPLAIN ANALYZE を取り、走査回数と Sort/HashAggregate の有無を見る習慣をつけましょう。

除外を集計後に適用する：テストアカウント除外を「集計してから引き算」で実装すると、除外対象の行が集計に混ざった中間結果を作る無駄に加え、FILTER 別の内訳から正確に引き戻せないバグの温床になります。除外・絞り込みはできる限り上流（WHERE / 結合前）で行うのが、正しさでも速さでも原則です。

実務コラム：基礎編2 後半の総まとめ — 「クエリの内側」5原則

この5問を整理すると、6. 除外条件は NOT EXISTS（NULL 安全 + Anti Join）、7. 条件別集計は FILTER / CASE で1走査、8. 頻出の ORDER BY + LIMIT は index でソートごと消す（work_mem の節約）、9. 排他ソースの統合は UNION ALL（重複排除は明示的な意思決定）、10. 統合 → 除外 → 集計の型で各テーブル1回走査を守る。前半の5原則（型を揃える / LIKE 前方一致 / OR の分解 / WHERE vs HAVING / カバリング index）、基礎編1の10原則と合わせて計20項目のチェックリストが完成しました。ここまでの知識で、実務の SELECT 文チューニングの大半は「どの原則に違反しているか」の照合作業に変わります。次の中級編では、実行計画の行数見積もり（統計情報）と JOIN 戦略の制御へ進みます。