SQL 重複データ管理 — DISTINCT ON・ウィンドウ関数の応用

QUESTION 1

グループ代表行の最短取得 — DISTINCT ON で各顧客の最新注文を一発で

DISTINCT ON代表行抽出ORDER BY設計PostgreSQL

前提知識 ▼

基礎編では CTE + ROW_NUMBER で「各キーの代表1行」を取りました。PostgreSQL には同じことを1段のクエリで書ける DISTINCT ON があります。「ORDER BY で並べたとき、指定キーごとに先頭の1行だけを残す」構文です。

SELECT DISTINCT ON (customer_id)   -- 重複を判定するキー
  customer_id, order_id, ...
FROM orders
ORDER BY customer_id, ordered_at DESC;  -- キー → 残す優先順位 の順で並べる

ORDER BY が「どれを残すか」を決める：DISTINCT ON は並べ替え後の各キーの先頭行を採用します。そのため ORDER BY の先頭は必ず DISTINCT ON のキーと一致させ、続けて「残したい行が先頭に来る」並び（最新なら DESC）を指定します。ROW_NUMBER の PARTITION BY ＝ DISTINCT ON のキー、ORDER BY ＝そのまま、と1対1で対応します。

問題 ▼

orders には同じ顧客の注文が複数あります。顧客（customer_id）ごとに最新（ordered_at が最も新しい）の注文1件だけを、DISTINCT ON を使って取得してください。

使用テーブル

▸ orders

order_id	customer_id	amount	ordered_at
1	101	1200	2025-04-01
2	102	3400	2025-04-02
3	101	5600	2025-04-10
4	103	980	2025-04-05
5	102	2100	2025-04-12
6	101	780	2025-04-03

期待出力 ▼

customer_id	order_id	amount	ordered_at
101	3	5600	2025-04-10
102	5	2100	2025-04-12
103	4	980	2025-04-05

模範解答コード ▼

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, amount, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC;  -- 各顧客内で最新が先頭に来る並び

/*
  実行順序:
  1. FROM orders                            → 行を読み込む
  2. ORDER BY customer_id, ordered_at DESC  → 顧客ごと最新が先頭になるよう整列
  3. DISTINCT ON (customer_id)              → 各顧客の先頭1行を採用
  4. SELECT ...                             → 列を射影して出力
  */

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

SELECT DISTINCT ON (customer_id)
  customer_id, order_id, amount, ordered_at
FROM orders
ORDER BY customer_id, ordered_at DESC;

LEGEND ▼

データ取得・読込対象

① 元データ ▼

FROM ordersorders テーブル（6行）を読み込みます。customer_id=101 が3件、102 が2件と、同じ顧客の注文が複数並んでいます。ここから「顧客ごとの最新1件」を抜き出します。

1 / 4

order_id	customer_id	amount	ordered_at
1	101	1200	2025-04-01
2	102	3400	2025-04-02
3	101	5600	2025-04-10
4	103	980	2025-04-05
5	102	2100	2025-04-12
6	101	780	2025-04-03

orders: 6行（customer_id に重複あり）

学習ポイント

DISTINCT ON

並べ替え後の「各キーの先頭1行」を1段の SELECT で取得

ROW_NUMBER + WHERE rn=1 の定番パターンを最短記法で
DISTINCT ON (キー) ... ORDER BY キー, 優先順位

ORDER BY が重複排除のルールそのもの：DISTINCT ON では ORDER BY の設計＝「どれを残すか」の設計です。先頭にキー列（customer_id）、続けて優先順位（ordered_at DESC）を置くことで「顧客ごとに最新を残す」が表現されます。「最古を残す」なら ASC、「金額最大を残す」なら amount DESC に変えるだけで、排除ルールを自在に切り替えられます。

ROW_NUMBER 方式との使い分け：「rn=1 だけ欲しい」なら DISTINCT ON が短く読みやすい一方、rn=2 以降も使いたい（例: 2番目に新しい注文、削除対象の列挙）場合や、他DBへの移植性が必要な場合は基礎編の ROW_NUMBER 方式を選びます。両者は同じ問題の「省略記法」と「汎用記法」の関係です。

同率（タイ）対策のタイブレーク：同じ顧客に同時刻の注文が2件あると、どちらが先頭になるかは不定です。ORDER BY customer_id, ordered_at DESC, order_id DESC のように一意な列で必ず順序が決まるようタイブレークを足すのが、再現性のあるクエリにする実務の作法です。

アンチパターン

ORDER BY の先頭が DISTINCT ON のキーと不一致：DISTINCT ON (customer_id) ... ORDER BY ordered_at DESC のように書くと、PostgreSQL は「SELECT DISTINCT ON expressions must match initial ORDER BY expressions」というエラーを返します。必ず ORDER BY の先頭にキー列を置くのが構文上のルールです。

PostgreSQL 専用構文だと知らずに移植で詰まる：DISTINCT ON は標準SQLではなく PostgreSQL の拡張です。MySQL や SQL Server には存在しないため、複数DBで動かすコードやBIツール経由のクエリでは ROW_NUMBER 方式に書き換える必要があります。便利さと移植性のトレードオフを理解して使い分けましょう。

実務コラム

「キーごとの最新1件」は実務で最も登場頻度の高い重複制御です。応用例: 顧客ごとの最終ログイン・最終購入の抽出、商品ごとの最新価格（価格履歴テーブルから）、デバイスごとの最新ステータス。PostgreSQL 環境のアドホック分析では DISTINCT ON が圧倒的に速く書けるため、ROW_NUMBER と並ぶ第二の武器として手に馴染ませておくと、日々の調査クエリが一気に短くなります。

QUESTION 2

重複行を行のまま洗い出す — COUNT(*) OVER (PARTITION BY) の重複フラグ

Window集計重複検出PARTITION BYCTE

前提知識 ▼

基礎編の GROUP BY + HAVING は「重複している値と件数」しか返せず、重複行の中身（IDや他の列）は潰れてしまいます。ウィンドウ集計 COUNT(*) OVER (PARTITION BY ...) を使うと、行を1行も潰さずに各行へ「同じキーが何件あるか」を付与できます。

COUNT(*) OVER (PARTITION BY product_name, maker) AS dup_cnt
-- GROUP BY と違い、行はそのまま・集計値が列として付く

GROUP BY は行をまとめる、OVER は行に添える：GROUP BY はグループを1行に集約しますが、集計関数 OVER (PARTITION BY ...) は全行を保持したままグループ集計値を各行の横に添えます。「重複している行そのものを全列付きで一覧したい」ときに効く考え方の転換です。

問題 ▼

商品マスタ products に二重登録が混入しました。(product_name, maker) の組が重複しているすべての行を、元の全列＋重複件数（dup_cnt）付きで洗い出してください。

使用テーブル

▸ products

product_id	product_name	maker	price
1	消しゴム	ABC文具	120
2	ノートA5	XYZ製紙	300
3	消しゴム	ABC文具	150
4	ボールペン	ABC文具	200
5	ノートA5	XYZ製紙	300
6	消しゴム	ABC文具	110

期待出力 ▼

product_id	product_name	maker	price	dup_cnt
1	消しゴム	ABC文具	120	3
3	消しゴム	ABC文具	150	3
6	消しゴム	ABC文具	110	3
2	ノートA5	XYZ製紙	300	2
5	ノートA5	XYZ製紙	300	2

模範解答コード ▼

WITH flagged AS (
  SELECT
    product_id, product_name, maker, price,
    COUNT(*) OVER (
      PARTITION BY product_name, maker   -- 重複の判定キー（複合）
    ) AS dup_cnt
  FROM products
)
SELECT
  product_id, product_name, maker, price, dup_cnt
FROM flagged
WHERE dup_cnt > 1            -- 重複している行だけに絞る
ORDER BY dup_cnt DESC, product_id;

/*
  実行順序:
  1. CTE(flagged)                       → products を読み込む
  2. COUNT(*) OVER (...)                → 組ごとの件数を各行に付与
  3. FROM flagged                       → 派生テーブルを参照
  4. WHERE dup_cnt > 1                  → 重複行だけ残す
  5. SELECT ...                         → 列を射影
  6. ORDER BY dup_cnt DESC, product_id  → 並べ替えて出力
  */

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

WITH flagged AS (
  SELECT
    product_id, product_name, maker, price,
    COUNT(*) OVER (
      PARTITION BY product_name, maker
    ) AS dup_cnt
  FROM products
)
SELECT
  product_id, product_name, maker, price, dup_cnt
FROM flagged
WHERE dup_cnt > 1
ORDER BY dup_cnt DESC, product_id;

LEGEND ▼

データ取得・読込対象

① CTE — 元データ ▼

FROM productsproducts テーブル（6行）を読み込みます。(product_name, maker) の組で見ると「消しゴム／ABC文具」が3件、「ノートA5／XYZ製紙」が2件あります。price が異なる重複（1,3,6）は DISTINCT では消せないタイプの重複です。

1 / 4

product_id	product_name	maker	price
1	消しゴム	ABC文具	120
2	ノートA5	XYZ製紙	300
3	消しゴム	ABC文具	150
4	ボールペン	ABC文具	200
5	ノートA5	XYZ製紙	300
6	消しゴム	ABC文具	110

products: 6行（複合キーに重複あり）

学習ポイント

WINDOW DUPLICATE FLAG

重複検出の進化形 — 行を潰さず dup_cnt を各行へ付与

GROUP BY+HAVING では失われる「行の中身」を保持したまま検出
COUNT(*) OVER (PARTITION BY キー) → WHERE dup_cnt > 1

GROUP BY 方式との決定的な違い：基礎編の GROUP BY email HAVING COUNT(*) > 1 は「どの値が重複か」までしか分かりません。重複行の中身を見るには元テーブルへ再JOIN（自己結合）が必要でした。ウィンドウ集計なら1回のスキャンで「検出」と「行の保持」を同時に達成でき、クエリも実行コストもシンプルになります。

「一部の列が違う重複」を可視化できる：product_id=1,3,6 は price がバラバラなので、完全一致しか見ない DISTINCT では検出も除去もできません。複合キー PARTITION BY product_name, maker で「業務上同じ商品とみなすか」を定義することで、どの price が正なのかという次のクレンジング判断に進める一覧が得られます。

ROW_NUMBER との合わせ技：同じ CTE に ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) AS rn を並べて書けば、「dup_cnt>1 の重複一覧（調査用）」と「rn>1 の削除対象一覧（排除用）」を1つの CTE から両方取り出せます。検出（本問）→排除（基礎編Q4）が地続きであることが分かる、重複管理の中核パターンです。

アンチパターン

WHERE に直接 Window 関数を書く：WHERE COUNT(*) OVER (...) > 1 は構文エラーです。Window 関数は WHERE より後（SELECT 段階）で評価されるため、CTE やサブクエリでいったん列にしてから外側で絞る2段構えが必須です。基礎編の ROW_NUMBER と同じ制約です。

PARTITION BY のキー選びが甘く誤検出する：product_name だけで区切ると、別メーカーの同名商品（OEM品など）まで重複扱いになります。逆にキーに price まで含めると 1,3,6 の重複を見逃します。「業務上なにをもって同一とみなすか」の定義がクエリより先。キー設計を間違えると、正しい構文でも間違った検出になります。

実務コラム

この「重複フラグ列」パターンは調査業務の万能ナイフです。応用例: マスタ統合前の重複候補レポート（全列付きでそのまま関係者に共有できる）、二重取込された売上明細の特定、名寄せ候補のスコアリング前処理。GROUP BY の集計表では「で、どのレコードを直せばいいの？」に答えられませんが、dup_cnt 付きの行一覧ならそのまま修正作業のチェックリストになります。

QUESTION 3

二重決済の調査レポート — 複合キー検出 + STRING_AGG で対象IDを集約

STRING_AGG複合キーHAVING調査レポート

前提知識 ▼

「同じユーザーが同じ金額を同じ日に複数回決済している」— 二重決済の疑いは複数列の組み合わせ（複合キー）で検出します。さらに STRING_AGG を使うと、各重複グループに属する決済IDをカンマ区切りの1セルへ集約でき、そのまま調査レポートになります。

GROUP BY user_id, amount, paid_at        -- 複合キーで「同一」を定義
HAVING COUNT(*) > 1
-- グループ内の値を文字列として連結する集約関数
STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id)

STRING_AGG は「集約関数」：SUM が数値を足し合わせるように、STRING_AGG はグループ内の値を区切り文字で連結した1つの文字列に集約します。数値列は ::TEXT でキャストし、ORDER BY を関数内に書くと連結順も制御できます（MySQLの GROUP_CONCAT に相当）。

問題 ▼

payments から二重決済の疑いを調査します。(user_id, amount, paid_at) の組が重複しているグループについて、件数（cnt）と、該当する決済IDの一覧（payment_ids、昇順カンマ区切り）を取得してください。

使用テーブル

▸ payments

payment_id	user_id	amount	paid_at
101	1	5000	2025-05-01
102	2	3000	2025-05-01
103	1	5000	2025-05-01
104	3	8000	2025-05-02
105	2	3000	2025-05-03
106	1	5000	2025-05-01
107	3	8000	2025-05-02

期待出力 ▼

user_id	amount	paid_at	cnt	payment_ids
1	5000	2025-05-01	3	101, 103, 106
3	8000	2025-05-02	2	104, 107

模範解答コード ▼

SELECT
  user_id, amount, paid_at,
  COUNT(*) AS cnt,
  STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id) AS payment_ids
FROM payments
GROUP BY user_id, amount, paid_at   -- 3列の組み合わせで重複を定義
HAVING COUNT(*) > 1            -- 2件以上＝二重決済の疑い
ORDER BY user_id;

/*
  実行順序:
  1. FROM payments                      → 行を読み込む
  2. GROUP BY user_id, amount, paid_at  → 3列の組でグループ化
  3. HAVING COUNT(*) > 1                → 重複グループだけ残す
  4. COUNT(*) / STRING_AGG(...)         → 件数と決済ID連結を計算
  5. SELECT ...                         → 列を射影
  6. ORDER BY user_id                   → 並べ替えて出力
  */

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

SELECT
  user_id, amount, paid_at,
  COUNT(*) AS cnt,
  STRING_AGG(payment_id::TEXT, ', ' ORDER BY payment_id) AS payment_ids
FROM payments
GROUP BY user_id, amount, paid_at
HAVING COUNT(*) > 1
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① 元データ ▼

FROM paymentspayments テーブル（7行）を読み込みます。user_id=1 は 5000円の決済が同日に3回、user_id=3 は 8000円が同日に2回あります。user_id=2 の 3000円×2件は日付が違うため、正常な別決済です。

1 / 5

payment_id	user_id	amount	paid_at
101	1	5000	2025-05-01
102	2	3000	2025-05-01
103	1	5000	2025-05-01
104	3	8000	2025-05-02
105	2	3000	2025-05-03
106	1	5000	2025-05-01
107	3	8000	2025-05-02

payments: 7行

学習ポイント

COMPOSITE KEY + STRING_AGG

複合キーで「同一」を定義し、対象IDを1セルに集約する

検出結果がそのまま調査レポートになる実務形
GROUP BY 3列 → HAVING > 1 → STRING_AGG(id)

重複の定義は「業務」が決める：本問の重複は (user_id, amount, paid_at) の3列一致と定義しました。user_id だけなら常連客が、(user_id, amount) だけなら日をまたいだ定額課金が誤検出されます。基礎編の単一キー検出との最大の差は、キーの組み合わせ設計こそが検出精度を決めるという点です。

STRING_AGG が「検出」と「特定」をつなぐ：HAVING だけでは「疑いがある」までしか分かりません。STRING_AGG で payment_id を集約することで、取消・返金すべき具体的なレコードまで1クエリで特定できます。関数内 ORDER BY payment_id により連結順が安定し、レポートの再現性も保てます。

集約関数は組み合わせて使う：同じ SELECT に MIN(payment_id)（残す代表）や SUM(amount)（影響金額）、ARRAY_AGG(payment_id)（配列のままプログラムへ渡す）を並べることもできます。1つの GROUP BY に複数の集約を載せる発想が、調査クエリを1本にまとめる鍵です。

アンチパターン

タイムスタンプのまま GROUP BY して検出ゼロ：paid_at が 2025-05-01 10:23:45.120 のような TIMESTAMP 型だと、ミリ秒違いで全行が別グループになり重複が1件も検出できません。日単位なら paid_at::DATE、「5分以内の再決済」なら date_trunc('hour', ...) や時間差条件など、粒度を丸めてから比較するのが実務の定石です。

STRING_AGG の結果をSQLで再分解しようとする：連結した payment_ids 文字列を後続クエリで LIKE や split で解析し始めたら設計ミスのサインです。文字列集約は人が読む最終レポート専用。後続処理で行として使うなら、集約せずウィンドウ方式で行のまま持ち回りましょう。

実務コラム

複合キー検出＋ID集約は不正・異常調査の定番フォーマットです。応用例: 二重決済・二重請求の監査レポート、同一人物による複数アカウントの疑い（氏名×生年月日×電話）、バッチ二重実行で重複した取込ロットの特定。「件数だけの報告」と「対象IDまで添えた報告」では後工程の速度がまったく違います。検出クエリは次のアクションに必要な情報まで運ぶ、を合言葉にしましょう。

QUESTION 4

連続する重複だけを除く — LAG と IS DISTINCT FROM で変化点を抽出

LAG変化点抽出IS DISTINCT FROM時系列

前提知識 ▼

監視ログのように同じ値が延々と続くデータでは、「全体の重複除去」ではなく直前と同じ値の行だけを除く（連続重複の圧縮）が必要です。DISTINCT では「OK→ERROR→OK」の2回目の OK まで消えてしまうため、LAG で直前の値を取り寄せて比較します。

LAG(status) OVER (ORDER BY logged_at) AS prev_status
-- 時系列順で「1つ前の行」の status を現在行へ持ってくる（先頭行は NULL）

WHERE prev_status IS DISTINCT FROM status   -- NULL も正しく比較できる「不一致」演算子

NULL 比較の罠と IS DISTINCT FROM：先頭行の prev_status は NULL です。prev_status <> status は NULL との比較が UNKNOWN になり先頭行が消えます。IS DISTINCT FROM は NULL を「1つの値」として扱う比較演算子で、NULL IS DISTINCT FROM 'OK' は真。先頭行を安全に残せます。

問題 ▼

サーバー監視ログ status_logs は5分ごとに状態を記録するため、同じ status が連続して大量に並びます。状態が直前から変化した行（変化点）だけを抽出し、ログを圧縮してください。

使用テーブル

▸ status_logs

log_id	status	logged_at
1	OK	09:00
2	OK	09:05
3	ERROR	09:10
4	ERROR	09:15
5	ERROR	09:20
6	OK	09:25
7	OK	09:30

期待出力 ▼

log_id	status	logged_at
1	OK	09:00
3	ERROR	09:10
6	OK	09:25

模範解答コード ▼

WITH with_prev AS (
  SELECT
    log_id, status, logged_at,
    LAG(status) OVER (ORDER BY logged_at) AS prev_status  -- 直前行の status
  FROM status_logs
)
SELECT
  log_id, status, logged_at
FROM with_prev
WHERE prev_status IS DISTINCT FROM status  -- 直前と違う＝変化点のみ（先頭行も残る）
ORDER BY logged_at;

/*
  実行順序:
  1. CTE(with_prev)                             → status_logs を読み込む
  2. LAG(status) OVER (...)                     → 直前の status を付与
  3. FROM with_prev                             → 派生テーブルを参照
  4. WHERE prev_status IS DISTINCT FROM status  → 変化点だけ残す
  5. SELECT ...                                 → 列を射影
  6. ORDER BY logged_at                         → 時系列順で出力
  */

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

WITH with_prev AS (
  SELECT
    log_id, status, logged_at,
    LAG(status) OVER (ORDER BY logged_at) AS prev_status
  FROM status_logs
)
SELECT
  log_id, status, logged_at
FROM with_prev
WHERE prev_status IS DISTINCT FROM status
ORDER BY logged_at;

LEGEND ▼

データ取得・読込対象

① CTE — 元データ ▼

FROM status_logsstatus_logs テーブル（7行）を読み込みます。OK が2回 → ERROR が3回 → OK が2回と、同じ状態が連続しています。欲しいのは「状態が切り替わった瞬間」の行だけです。

1 / 4

log_id	status	logged_at
1	OK	09:00
2	OK	09:05
3	ERROR	09:10
4	ERROR	09:15
5	ERROR	09:20
6	OK	09:25
7	OK	09:30

status_logs: 7行（連続する重複あり）

学習ポイント

CONSECUTIVE DEDUP

連続重複の圧縮 — 直前行と比較して変化点だけ残す

DISTINCT では消えてしまう「再登場した値」を時系列文脈ごと保持
LAG(値) OVER (ORDER BY 時刻) → IS DISTINCT FROM で比較

「全体の重複」と「連続する重複」は別物：DISTINCT や GROUP BY はテーブル全体で同じ値をまとめるため、復旧後の OK（log_id=6）まで消えて「ERROR のまま」に見える事故が起きます。時系列データの重複除去は順序を前提にした「直前との比較」で行う、という発想の切り替えがこの問題の本質です。

LAG / LEAD は行間比較の基本道具：LAG(列, n) は n 行前（省略時1）、LEAD は n 行後の値を取り寄せます。OVER (ORDER BY ...) の並び順が「前後」の定義そのものなので、ORDER BY の省略は厳禁です。変化点抽出のほか、前日比・前回購入との間隔など「隣の行との差」を見る分析全般に使えます。

IS DISTINCT FROM は NULL 安全な比較：通常の = / <> は片方が NULL だと結果が UNKNOWN になり WHERE で落ちます。IS DISTINCT FROM（否定は IS NOT DISTINCT FROM）は NULL同士=同じ、NULLと値=違う と直感どおりに判定します。LAG の先頭 NULL と相性が良く、覚えておくとNULL絡みの比較バグを根本から防げます。

アンチパターン

<> で比較して先頭行が消える：WHERE prev_status <> status と書くと、先頭行（prev_status が NULL）の比較結果が UNKNOWN になり初回の記録が静かに消えます。エラーが出ないぶん気づきにくい、NULL比較の代表的なバグです。IS DISTINCT FROM か prev_status IS NULL OR prev_status <> status で守りましょう。

複数系列を1本の LAG で処理してしまう：サーバーが複数ある実データで PARTITION BY を忘れると、別サーバーのログ同士が「直前の行」として比較され、誤った変化点が混入します。系列（server_id など）があるなら LAG(status) OVER (PARTITION BY server_id ORDER BY logged_at) と必ず系列ごとに区切ります。

実務コラム

連続重複の圧縮はログ・履歴系データの定番処理です。応用例: 監視ステータスの状態遷移履歴の生成（インシデントレポートの土台）、会員ランクや契約プランの変更履歴の抽出（スナップショットテーブルから変化日だけ拾う）、センサー値の同値連続の間引き（ストレージ削減）。「変化した瞬間」だけが意思決定に必要な情報であることは多く、LAG → 比較 → 絞り込みの3手はそのままテンプレートとして使い回せます。

QUESTION 5

テーブル突合と差分検出 — EXCEPT × UNION ALL で移行検証

EXCEPT集合演算データ突合UNION ALL

前提知識 ▼

基礎編の UNION（和集合）に続き、集合演算の差集合 EXCEPT を使います。A EXCEPT B は「Aにあって B にない行」を返し、テーブル移行・同期の突合（つきあわせ）検証の主役です。

SELECT ... FROM members_old
EXCEPT                       -- 旧にあって新にない＝移行漏れ
SELECT ... FROM members_new;

EXCEPT も「重複管理」の仲間：UNION と同じく EXCEPT も全列の組み合わせで行の同一性を判定し、結果から重複を除去します（残したいなら EXCEPT ALL）。さらに NOT IN と違い NULL を「同じ値」として正しく扱えるため、突合用途では最も安全な書き方です。なお共通部分だけ欲しい場合は INTERSECT を使います。

問題 ▼

会員テーブルを members_old から members_new へ移行しました。移行漏れ（旧にあって新にない行: missing）と混入（新にあって旧にない行: extra）を、diff_type ラベル付きの1つの結果で検出してください。

使用テーブル

▸ members_old（移行元）

member_id	email
1	tanaka@ex.com
2	sato@ex.com
3	suzuki@ex.com
4	yamada@ex.com

▸ members_new（移行先）

member_id	email
1	tanaka@ex.com
2	sato@ex.com
4	yamada@ex.com
5	kato@ex.com

期待出力 ▼

diff_type	member_id	email
extra	5	kato@ex.com
missing	3	suzuki@ex.com

模範解答コード ▼

SELECT 'missing' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_old
  EXCEPT                                -- 旧 − 新 ＝ 移行漏れ
  SELECT member_id, email FROM members_new
) AS d1
UNION ALL                               -- 2方向の差分を縦に束ねる（重複しないのでALL）
SELECT 'extra' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_new
  EXCEPT                                -- 新 − 旧 ＝ 混入
  SELECT member_id, email FROM members_old
) AS d2
ORDER BY diff_type, member_id;

/*
  実行順序:
  1. d1: EXCEPT                     → 旧にあり新にない行（移行漏れ）
  2. d2: EXCEPT                     → 新にあり旧にない行（混入）
  3. ラベル列を付与                        → missing / extra を付与
  4. UNION ALL                      → 差分を縦に連結
  5. ORDER BY diff_type, member_id  → 並べ替えて出力
  */

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

SELECT 'missing' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_old
  EXCEPT
  SELECT member_id, email FROM members_new
) AS d1
UNION ALL
SELECT 'extra' AS diff_type, member_id, email
FROM (
  SELECT member_id, email FROM members_new
  EXCEPT
  SELECT member_id, email FROM members_old
) AS d2
ORDER BY diff_type, member_id;

LEGEND ▼

データ取得・読込対象

① 移行元 — members_old ▼

SELECT member_id, email FROM members_old移行元テーブル（4行）です。本来この4行すべてが移行先に存在しなければなりません。これを基準に突合していきます。

1 / 5

member_id	email
1	tanaka@ex.com
2	sato@ex.com
3	suzuki@ex.com
4	yamada@ex.com

members_old: 4行（あるべき姿）

学習ポイント

SET DIFFERENCE

EXCEPT で双方向突合 — 漏れと混入を1クエリで検出

移行・同期検証の定番。「0行なら一致」が合格判定になる
(old EXCEPT new) UNION ALL (new EXCEPT old)

突合は必ず双方向で：old EXCEPT new だけでは混入（extra）を見逃し、行数比較だけでは本問のように漏れと混入が相殺して件数が一致するケースを見逃します。両方向の EXCEPT がともに0行であって初めて「2つのテーブルは同一」と言えます。この対称チェックの型をそのまま暗記する価値があります。

NOT IN / NOT EXISTS との使い分け：同じ突合は NOT EXISTS でも書けますが、EXCEPT は比較したい全列をSELECTに並べるだけで複数列の一致判定が完結し、さらに NOT IN で事故りがちな NULL を「同じ値」として扱える強みがあります。一方、比較キー以外の列（更新日時など）も結果に出したい場合は NOT EXISTS が向きます。

UNION / UNION ALL の判断が活きる：missing と extra は定義上交わらない（同じ行が両方に出ることはない）ため、重複除去のソートコストを払う UNION ではなく UNION ALL を選ぶのが基礎編Q5で学んだ判断基準の実践です。「重複しうるか？」を一瞬考えてから ALL の有無を決める癖が、正確さと速度を両立させます。

アンチパターン

列の並び順ズレで「全行が差分」になる：EXCEPT は列の位置で対応づけるため、片方だけ SELECT email, member_id と順序が違うと、本来一致する行まで全部差分として出てきます（型が合えばエラーにもなりません）。集合演算では両側の SELECT の列リストを一字一句そろえることを徹底しましょう。

変化しうる列まで比較対象に含める：last_login のような移行後に更新される列を SELECT に含めると、実質同じ会員が大量に差分として誤検出されます。突合では「同一性を定義する列」だけを比較し、差分が出た行の詳細はその後で個別に確認する、と2段階に分けるのが実務のコツです。

実務コラム

EXCEPT 突合はデータ品質保証の最終兵器です。応用例: DB移行・リプレイス後の全件突合検証、本番とDWH（BI基盤）の同期ズレ検知の日次バッチ、リファクタリング前後でクエリ結果が変わっていないことの確認（旧SQL結果 EXCEPT 新SQL結果）。特に最後の「クエリ改修のセルフチェック」は今日から使えるテクニックで、双方向EXCEPTが0行＝書き換えは安全という機械的な合格判定が手に入ります。