SQL Quiz Book

SQL NULL — NOT INの罠・FILTER集計・LEFT JOINの応用

応用NULL応用NOT IN の罠 / NOT EXISTSFILTER集計ON句 vs WHERE句LAG / LEAD と NULLPostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 1

GROUP BY と NULL — NULL は「ひとつのグループ」として扱われる

GROUP BY + NULLCOALESCE + GROUP BYNULL グループ化COUNT DISTINCT と NULL
前提知識 ▼

GROUP BY で NULL を含む列をグループ化すると、NULL 行はすべて 「NULL」という1つのグループ にまとめられます。通常の比較 (NULL = NULL → UNKNOWN) と異なり、GROUP BY は NULL 同士を「同じグループ」として扱います。

/* GROUP BY region のみ: NULLはそのまま集約 */
SELECT region, COUNT(*) FROM sales GROUP BY region;
-- > 東京|2, 大阪|2, NULL|2

/* COALESCE で NULL グループに名前を付ける */
SELECT COALESCE(region, '不明'), COUNT(*) FROM sales
GROUP BY COALESCE(region, '不明');
-- > 東京|2, 大阪|2, 不明|2
COUNT(DISTINCT col) と NULL：COUNT(DISTINCT region) は NULL を除外した ユニーク件数 を返します（東京・大阪の2種類のみ）。COUNT(DISTINCT COALESCE(region, '不明')) にすることで NULL グループも含めた3種類になります。
問題 ▼

sales テーブルから、地域別の売上合計（total_amount）と注文件数（sale_count）を計算してください。region が NULL の場合は '不明' として集計し、total_amount 降順で返してください。出力列は region, total_amount, sale_count。

使用テーブル
► sales（6行）
sale_idregionamount
1東京15000
2大阪8000
3東京12000
4NULL5000
5大阪9000
6NULL7000
期待出力 ▼
regiontotal_amountsale_count
東京270002
大阪170002
不明120002
模範解答コード ▼ 
SELECT
  COALESCE(region, '不明')  AS region,  -- NULL を '不明' に変換
  SUM(amount)              AS total_amount,
  COUNT(*)                 AS sale_count
FROM   sales
GROUP BY COALESCE(region, '不明')       -- 変換後の値で集約（NULL を1グループに）
ORDER BY total_amount DESC;

/*
  実行順序:
  1. FROM: salesテーブル読込（6行）
  2. GROUP BY: COALESCEで NULL を '不明' に変換し、3グループ（東京, 大阪, 不明）に分割
  3. SELECT: SUM(amount) と COUNT(*) でグループごとに集計
  4. ORDER BY: 合計金額の降順でソート
*/
解説（テーブル変化・ポイント） ▼
SELECT COALESCE(region, '不明') AS region, SUM(amount) AS total_amount, COUNT(*) AS sale_count FROM sales GROUP BY COALESCE(region, '不明') ORDER BY total_amount DESC;
LEGEND ▼
データ取得・読込対象
① FROM sales（6行） ▼
FROM salessales テーブルの6行を読み込みます。region 列に NULL（地域未設定）が2行含まれています。
1 / 5
sale_idregionamount
1東京15000
2大阪8000
3東京12000
4NULL5000
5大阪9000
6NULL7000
6行読込
学習ポイント
GROUP BY は NULL 同士を「同じグループ」として扱う：通常の比較では NULL = NULL は UNKNOWN ですが、GROUP BY は例外的に NULL 行をすべて同一グループにまとめます。GROUP BY region で NULL は「NULL グループ」になりますが、SELECT 出力では NULL のまま表示されます。COALESCE を GROUP BY 式に使うことで NULL グループに意味のあるラベルを付けられます。
GROUP BY と SELECT のグループキーを明示的にそろえる：PostgreSQL では SELECT COALESCE(region,'不明') AS r ... GROUP BY r のように SELECT 別名を GROUP BY で参照できます。ただし、別名が入力列名と同じで曖昧な場合は入力列名が優先されます。移植性と可読性を重視するなら GROUP BY にも同じ COALESCE 式を記述するか、サブクエリで事前に変換してから GROUP BY するのが安全です。COALESCE の引数が多い場合は CTE で先変換する方法が可読性を高めます。
COUNT(DISTINCT col) は NULL を除外してカウントする：COUNT(DISTINCT region) は NULL を除いたユニーク値の件数を返します（東京・大阪の2種類）。NULL グループも含めたユニーク件数を得るには COUNT(DISTINCT COALESCE(region, '不明')) にする必要があります。GROUP BY と COUNT(DISTINCT) で NULL の扱いが一貫しているかを確認してください。
アンチパターン
SELECT 別名を入力列と同じ名前にして GROUP BY を曖昧にする：SELECT COALESCE(region,'不明') AS region ... GROUP BY region のように別名を元列と同じ名前にすると、PostgreSQL では GROUP BY の region が入力列として解釈され、意図した SELECT 別名を参照しないことがあります。このデータでは結果が一致しても、正規化ルールが増えるとグループと表示ラベルがずれる原因になります。region_label のような別名を使うか、GROUP BY に同じ COALESCE 式を明記してください。
NULL グループを HAVING IS NULL で絞り込む：COALESCE で変換済みの場合 HAVING COALESCE(region,'不明') IS NULL は一致しません（'不明' は NULL ではない文字列）。COALESCE 変換後は HAVING COALESCE(region,'不明') = '不明' を使い、変換前の元列で絞る場合は HAVING region IS NULL を使ってください。
実務コラム：NULL グループの可視化とデータ品質モニタリング
データ分析で NULL グループの割合が高い場合、データ収集・入力の品質問題を示すことが多いです。GROUP BY COALESCE(region,'不明') で「不明」グループを明示的に計上し、全体に占める割合（NULL 率）を定期モニタリングすることがデータ品質管理の基本です。dbt では NULL グループを含む集計ビューを作成し、ダッシュボードで NULL 率のトレンドを監視する設計が推奨されます。NULL が急増した場合はデータパイプラインの上流（ETL・フォーム入力）に問題がある可能性を示します。
1 / 5
問題を選択
QUESTION 2

ウィンドウ関数と NULL — LAG の「境界 NULL」と「データ NULL」を区別する

LAG / LEADNULL 伝搬ウィンドウ関数NULLS FIRST/LAST
前提知識 ▼

ウィンドウ関数 LAG(col) は前の行の値を参照しますが、返される NULL には 2種類 あり、意味が異なります。

/* ① 境界 NULL: 先頭行に「前行」が存在しないため NULL */
LAG(amount) OVER (ORDER BY dt)

/* ② データ NULL: 前行の値自体が NULL (欠損) のため NULL */
-- デフォルト値引数は ①境界NULL のみ回避し、②データNULL には効かない
LAG(amount, 1, 0) OVER (ORDER BY dt)
NULL 伝搬：NULL を含む算術演算の結果は 常に NULL になります。amount / prev_amount - 1 において amount か prev_amount のどちらかが NULL であれば成長率 growth_rate も NULL になります。これは欠損データ間の成長率が「計算不能」であることを正確に表現しています。
問題 ▼

daily_sales テーブルを使い、各日の前日比成長率（growth_rate）を計算してください。CTE で LAG を使って prev_amount を求め、growth_rate = (amount / prev_amount − 1) × 100 を計算してください。amount または prev_amount が NULL の日は growth_rate も NULL になります。出力列は dt, amount, prev_amount, growth_rate、dt 昇順、growth_rate は小数第1位まで丸めてください。

使用テーブル
► daily_sales（7行）
dtamount
2024-01-0110000
2024-01-0212000
2024-01-03NULL
2024-01-049000
2024-01-0511000
2024-01-06NULL
2024-01-0713000
期待出力 ▼
dtamountprev_amountgrowth_rate
2024-01-0110000NULLNULL
2024-01-02120001000020.0
2024-01-03NULL12000NULL
2024-01-049000NULLNULL
2024-01-0511000900022.2
2024-01-06NULL11000NULL
2024-01-0713000NULLNULL
模範解答コード ▼ 
WITH lagged AS (
  SELECT
    dt,
    amount,
    LAG(amount) OVER (ORDER BY dt) AS prev_amount  -- 前日の値（先頭行は NULL）
  FROM   daily_sales
)
SELECT
  dt,
  amount,
  prev_amount,
  ROUND(
    (amount::numeric / NULLIF(prev_amount, 0) - 1) * 100,  -- 0 除算を回避（0なら NULL）
    1
  ) AS growth_rate
FROM   lagged
ORDER BY dt;

/*
  実行順序:
  1. CTE (lagged): daily_sales を読み込み、LAG関数で前日の amount を取得
     (先頭行は「境界NULL」、前日が欠損している行は「データNULL」となる)
  2. SELECT: 取得した prev_amount を用いて前日比を計算
     (amount か prev_amount のいずれかが NULL の場合、計算結果も NULL に伝搬する)
  3. ORDER BY: 日付の昇順でソート
*/
解説（テーブル変化・ポイント） ▼
WITH lagged AS ( SELECT dt, amount, LAG(amount) OVER (ORDER BY dt) AS prev_amount FROM daily_sales ) SELECT dt, amount, prev_amount, ROUND( (amount::numeric / NULLIF(prev_amount, 0) - 1) * 100, 1 ) AS growth_rate FROM lagged ORDER BY dt;
LEGEND ▼
データ取得・読込対象
① FROM daily_sales（7行） ▼
FROM daily_salesdaily_sales テーブルの7行を読み込みます。amount 列に NULL（欠損: 2024-01-03, 2024-01-06）が含まれています。LAG がこの NULL をどのように伝搬させるかがポイントです。
1 / 4
dtamount
2024-01-0110000
2024-01-0212000
2024-01-03NULL
2024-01-049000
2024-01-0511000
2024-01-06NULL
2024-01-0713000
7行読込
学習ポイント
LAG の NULL は2種類で意味が異なる：①「境界 NULL」は先頭行（前行が物理的に存在しない）から発生します。LAG(col, 1, 0) の第3引数（デフォルト値）で回避できます。②「データ NULL」は前行の値自体が NULL（欠損データ）から発生します。デフォルト値引数は①にのみ効き、②には効かない点が重要です。分析でこの2種類を区別する場合は、LAG の後に追加のフラグ列で判別します。
NULL 伝搬：NULL を含む算術演算は常に NULL を返す：NULL + 1、NULL * 100、NULL / 5 はすべて NULL です。計算式の中に1つでも NULL があれば式全体が NULL になるため、欠損を補完する場合は COALESCE を計算式の中に入れる前に適用します。これが「NULL が静かに計算を壊す」本質的なメカニズムです。
NULLS FIRST / NULLS LAST で ORDER BY の NULL 位置を制御する：PostgreSQL のデフォルトは ASC で NULLS LAST（NULL を最後に）、DESC で NULLS FIRST（NULL を最初に）です。ORDER BY col DESC NULLS LAST と明示することで DBMS 依存のデフォルトに頼らず意図した順序を保証できます。ウィンドウ関数の OVER (ORDER BY dt NULLS FIRST) でも同様に機能します。
アンチパターン
LAG のデフォルト値でデータ NULL も補完できると思い込む：LAG(amount, 1, 0) OVER (ORDER BY dt) は先頭行のみ 0 を返します。2024-01-04 のように前行（2024-01-03）の amount が NULL の場合、デフォルト値は使われず NULL がそのまま返ります。デフォルト値引数は「前行が存在しない」場合にのみ使われ、「前行の値が NULL」の場合には使われません。
NULL 伝搬で生じた NULL を後から COALESCE で 0% に置換する：COALESCE(growth_rate, 0) は growth_rate が NULL のとき 0% に置換しますが「欠損日の成長率は 0%」という誤ったビジネス解釈になります。NULL は「計算不能・欠損」を正確に表しているため、安易に 0 に置換せず NULL のまま保持してダッシュボード側でラベル表示を工夫するのが正しい設計です。
実務コラム：時系列分析での欠損日と NULL の扱い
前日比・前月比・前年同期比は分析の定番ですが、欠損日（NULL）が混入すると成長率が連鎖的に NULL になります。実務では Snowflake の LAG(amount) IGNORE NULLS OVER (ORDER BY dt) を使うと NULL をスキップして直前の非 NULL 値を参照できます。BigQuery の LAG には IGNORE NULLS 構文がないため、LAST_VALUE(amount IGNORE NULLS) OVER (ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) などで前方補完を実装します。PostgreSQL では IGNORE NULLS をサポートしていないため、CTE で欠損行を除外してから LAG を使うか、累積グループと MAX などで前方補完を実装する方法が使われます。欠損日をどう扱うか（スキップ vs NULL 保持）は KPI 定義の問題であり、データ仕様書に明記することが重要です。
2 / 5
問題を選択
QUESTION 3

NOT IN サブクエリの NULL トラップ — NULL が1件でも全行が「静かに消える」

NOT IN と NULLNOT EXISTSサブクエリ NULL 罠アンチジョイン応用
前提知識 ▼

NOT IN のリストに NULL が1件でも含まれると、全行が UNKNOWN → WHERE に除外されて0件になる致命的な罠があります。

/* NOT IN に NULL が含まれると全行が UNKNOWN となり除外される */
dept_id NOT IN (10, NULL)
-- dept_id=20 の場合: IN評価が UNKNOWN → NOT IN も UNKNOWN → 除外(バグ)

/* NOT EXISTS は NULL に影響されない安全なアンチジョイン */
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.dept_id = c.dept_id)
-- 一致行がない場合(NULL比較含む)は EXISTS=FALSE → NOT EXISTS=TRUE(通過)
NOT IN リストの NULL は「全行を UNKNOWN にする時限爆弾」：サブクエリ NOT IN (SELECT col FROM ...) でサブクエリの列に NULL が1件でもあると全行除外されます。NOT EXISTS が NULL-safe な推奨代替手段です。
問題 ▼

candidates テーブルから、blocked_dept_ids に含まれる dept_id を持つ候補者を除外して取得してください。ただし blocked_dept_ids テーブルの dept_id には NULL が含まれていることに注意してください。また、候補者の dept_id が NULL の場合（佐藤）は「ブロック対象であると確認できない」ため、除外せず結果に含めてください。出力列は candidate_id, name, dept_id、candidate_id 昇順。

使用テーブル
► candidates（5行）
candidate_idnamedept_id
1田中10
2鈴木20
3佐藤NULL
4伊藤10
5山田30
► blocked_dept_ids（2行）
dept_id
10
NULL
期待出力 ▼
candidate_idnamedept_id
2鈴木20
3佐藤NULL
5山田30
模範解答コード ▼ 
SELECT
  candidate_id,
  name,
  dept_id
FROM   candidates c
WHERE  NOT EXISTS (             -- 一致行が無い候補のみ通過
  SELECT 1
  FROM   blocked_dept_ids b
  WHERE  b.dept_id = c.dept_id  -- 相関比較（NULL dept は不一致＝通過）
)
ORDER BY candidate_id;

/*
  1. 相関サブクエリ(NOT EXISTS)でブロック対象を判定
  2. dept_id=10 は EXISTS=TRUE となり除外される
  3. dept_id=NULL(佐藤)や20,30は EXISTS=FALSE となり通過する
*/
解説（テーブル変化・ポイント） ▼
SELECT candidate_id, name, dept_id FROM candidates c WHERE NOT EXISTS ( SELECT 1 FROM blocked_dept_ids b WHERE b.dept_id = c.dept_id ) ORDER BY candidate_id;
LEGEND ▼
データ取得・読込対象
① FROM candidates（5行）+ blocked_dept_ids: (10, NULL) ▼
FROM candidates / blocked_dept_ids には NULL が混在candidates の5行を確認します。blocked_dept_ids の dept_id は (10, NULL) の2件です。この NULL が NOT IN の罠になります。
1 / 3
candidate_idnamedept_id
1田中10
2鈴木20
3佐藤NULL
4伊藤10
5山田30
candidates: 5行 / blocked_dept_ids: (10, NULL)
学習ポイント
NOT IN リストに NULL が含まれると全行が UNKNOWN で除外される：x NOT IN (..., NULL, ...) は NOT (x = v1 OR x = v2 OR x = NULL) に展開され、x = NULL が UNKNOWN になります。OR チェーンで UNKNOWN があると IN = UNKNOWN → NOT IN = UNKNOWN → WHERE が除外します。「NULL に等しくない」ことを証明できないため全行が除外される3値論理の帰結です。
NOT EXISTS は NULL-safe：相関サブクエリが「一致行が0件 → EXISTS=FALSE」を確認するため、NULL = NULL が UNKNOWN になっても EXISTS=FALSE（0行）となり NOT EXISTS=TRUE になります。NOT EXISTS は「一致を証明できなければ通過」という論理です。dept_id=NULL の候補者も「ブロックリストに含まれる証明ができない」として通過します。
NOT IN を安全にする方法は3つある：①NOT EXISTS（推奨）: NULL-safe かつ可読性が高い。② LEFT JOIN + IS NULL: アンチジョインパターンと同じ原理（NULL-safe）。③ NOT IN + WHERE IS NOT NULL: NOT IN (SELECT col FROM t WHERE col IS NOT NULL) で NULL を除外してから NOT IN を使う。実務では①または②が推奨されます。
アンチパターン
NOT IN サブクエリの列に NULL があっても大丈夫と思い込む：実務ではサブクエリの元テーブルに NOT NULL 制約がない場合、データ入力ミス・外部ソース由来のデータなどで NULL が混入することがあります。サブクエリに NULL が含まれうる可能性がある場合は NOT IN を使わず、NOT EXISTS か LEFT JOIN + IS NULL を使ってください。「テストデータに NULL がなかったから動いていた」という潜在バグが本番データで爆発するパターンです。
dept_id=NULL の候補者（佐藤）の扱い：NOT EXISTS では dept_id=NULL の候補者は「ブロックリストにいることを証明できない」として通過します。「NULL の候補者は除外すべき」というビジネス要件の場合は AND dept_id IS NOT NULL を WHERE に追加してください。NULL の候補者を通過させるか除外するかはビジネス要件次第であり、実装前に仕様を確認することが重要です。
実務コラム：NOT IN vs NOT EXISTS vs LEFT JOIN + IS NULL の実務選択
アンチジョイン（除外パターン）の3つの書き方には特性があります。NOT EXISTS: 最も安全で NULL-safe。相関サブクエリのため行ごとに評価されるが、オプティマイザが最適化するので実用上のパフォーマンス差は小さい。LEFT JOIN + IS NULL: 見通しが良く、複数列での結合や追加条件の記述が容易。実行計画が分かりやすい。NOT IN: サブクエリ列が NOT NULL 保証のある場合のみ安全。実務では NOT EXISTS か LEFT JOIN + IS NULL を使い、NOT IN はサブクエリを使う場合は避けるのがベストプラクティスです。
3 / 5
問題を選択
QUESTION 4

FILTER 句と CASE WHEN 集計 — NULL を「条件付き」で集計する実務パターン

FILTER 集計CASE WHEN + COUNT/AVG条件付き集計NULL と AVG（0件グループ）
前提知識 ▼

集約関数に FILTER (WHERE ...) を付けると、条件一致行だけを集計できます。CASE WHEN との等価性と、集計列に NULL が含まれる場合の扱いの違いを理解することが重要です。

/* FILTER 句: 条件一致行のみを集計 */
COUNT(*) FILTER (WHERE event_type = 'click')
-- COUNT(*) は行を数えるため、他列が NULL でも 1 としてカウントされる

/* 対象列の指定と NULL のスキップ */
AVG(duration_sec) FILTER (WHERE event_type = 'purchase')
-- duration_sec が NULL の行は計算から除外される
集計結果が 0 になるか NULL になるか：COUNT は条件に一致する行が 0 件のとき 0 を返します。一方 AVG や SUM は 0 件のとき NULL を返します。また、AVG(col) は計算対象の col がすべて NULL の場合も NULL を返します。
問題 ▼

app_events テーブルを使い、ユーザーごとにイベントタイプ別の件数（view_count, click_count, purchase_count）と購入イベントの平均所要時間（avg_purchase_sec）を計算してください。出力列は user_id, view_count, click_count, purchase_count, avg_purchase_sec、user_id 昇順、avg_purchase_sec は小数第1位まで丸めてください。

使用テーブル
► app_events（8行）
event_iduser_idevent_typeduration_sec
1U1view15
2U1clickNULL
3U1purchase45
4U2view20
5U2clickNULL
6U2clickNULL
7U3purchase60
8U3view10
期待出力 ▼
user_idview_countclick_countpurchase_countavg_purchase_sec
U111145.0
U2120NULL
U310160.0
模範解答コード ▼ 
SELECT
  user_id,
  COUNT(*) FILTER (WHERE event_type = 'view')     AS view_count,    -- 条件に合う行だけ集計
  COUNT(*) FILTER (WHERE event_type = 'click')    AS click_count,
  COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count,
  ROUND(
    AVG(duration_sec) FILTER (WHERE event_type = 'purchase'),  -- purchase 行のみ平均
    1
  )                                                AS avg_purchase_sec
FROM   app_events
GROUP BY user_id
ORDER BY user_id;

/*
  実行順序:
  1. FROM: app_events を読込
  2. GROUP BY: user_id 単位で分割
  3. SELECT (FILTER集計)
  4. ORDER BY: ユーザーIDでソート
  */
解説（テーブル変化・ポイント） ▼
SELECT user_id, COUNT(*) FILTER (WHERE event_type = 'view') AS view_count, COUNT(*) FILTER (WHERE event_type = 'click') AS click_count, COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchase_count, ROUND( AVG(duration_sec) FILTER (WHERE event_type = 'purchase'), 1 ) AS avg_purchase_sec FROM app_events GROUP BY user_id ORDER BY user_id;
LEGEND ▼
データ取得・読込対象
除外・非表示データ
① FROM app_events（8行） ▼
FROM app_eventsapp_events テーブルの8行を読み込みます。click イベントは duration_sec がすべて NULL になっています。
1 / 5
event_iduser_idevent_typeduration_sec
1U1view15
2U1clickNULL
3U1purchase45
4U2view20
5U2clickNULL
6U2clickNULL
7U3purchase60
8U3view10
8行読込
学習ポイント
FILTER (WHERE ...) 句で集約関数に条件を付ける：PostgreSQL 9.4+ の標準 SQL 機能です。COUNT(*) FILTER (WHERE cond) は cond=TRUE の行のみカウントします。CASE WHEN との等価関係は COUNT(CASE WHEN cond THEN 1 END) ですが、FILTER の方が可読性が高く、集計計画もシンプルです。1つの GROUP BY で複数の条件別集計を書くときに特に有効です。
CASE WHEN の ELSE 省略は ELSE NULL と等価：CASE WHEN cond THEN 1 END の ELSE は省略すると ELSE NULL が暗黙補完されます。COUNT は NULL をスキップするため、条件一致行だけがカウントされます。ELSE 0 を追加すると全行が非 NULL になり COUNT(*) と同じ結果（全行カウント）になる罠があります。SUM でも同様で SUM(CASE WHEN cond THEN amount ELSE 0 END) は全件の amount 合計になります。
0件グループの AVG は NULL（0 ではない）：AVG(col) FILTER (WHERE cond) でグループ内に条件一致行が0件のとき、AVG は NULL を返します。COUNT は0件で 0 を返すのに対し、AVG/SUM は0件で NULL を返すという違いがあります。U2 の avg_purchase_sec が NULL になるのはこのためです。0に置換したい場合は COALESCE(AVG(...) FILTER (...), 0) を使いますが、「購入なし = 平均0秒」が正しい解釈かビジネス要件を確認してください。
アンチパターン
CASE WHEN に ELSE 0 を付けて COUNT を壊す：COUNT(CASE WHEN event_type='view' THEN 1 ELSE 0 END) は ELSE 0 で全行が非 NULL になるため、COUNT の結果がグループ内の全行数（= COUNT(*)）になります。条件別カウントには ELSE を省略するか、明示的に ELSE NULL と書いてください。SUM では ELSE 0 が「0円として集計に含める」意図になるため正しい場合もありますが、COUNT では必ず ELSE を省略します。
複数の条件で GROUP BY を複数回実行する：イベントタイプ別の集計のために GROUP BY を複数回実行して結合する設計は、JOIN の複雑化とパフォーマンス悪化を招きます。FILTER 句や CASE WHEN を使えば1回の GROUP BY で複数条件の集計が可能です。これは「ピボット集計」パターンとも呼ばれます。
実務コラム：FILTER 句 vs CASE WHEN の使い分けと実行計画
FILTER 句と CASE WHEN はほぼ等価ですが、実務では FILTER 句の方が読みやすく、多くのオプティマイザで効率的に処理されます。BigQuery では COUNTIF(cond)、Redshift では CASE WHEN の方が使われるなど DBMS によって慣習が異なります。また NULL が含まれる列に対する AVG FILTER と SUM FILTER は、NULL スキップが FILTER 前に行われるのか FILTER 後に行われるのかを意識することが重要です（実際は FILTER で対象行を絞り込んだ後、その中で NULL をスキップして集計します）。
4 / 5
問題を選択
QUESTION 5

複数条件 LEFT JOIN — ON 句フィルタと WHERE 句フィルタで結果が変わる

ON 句 vs WHERE 句CASE WHEN 集計LEFT JOIN 応用SUM=NULL / COUNT=0 の違い
前提知識 ▼

LEFT JOIN で結合した後のデータを集計する際、絞り込み条件を書く場所と集計方法によって結果が大きく変わります。

/* 1. ON 句 vs WHERE 句の違い */
LEFT JOIN orders o ON m.id = o.member_id AND o.status = 'completed'
-- ✓ 一致しない会員も残る（正しい LEFT JOIN の挙動）

LEFT JOIN orders o ON m.id = o.member_id
WHERE o.status = 'completed'
-- × 注文がない会員が消えてしまう（実質 INNER JOIN になる）

/* 2. 複数条件の集計 (CASE WHEN) */
SUM(CASE WHEN o.status = 'completed' THEN o.amount END)
COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END)
SUM の NULL と COUNT の NULL の違い：グループ内の全 CASE 結果が NULL のとき、SUM は NULL を返しますが COUNT は 0 を返します。SUM には COALESCE で NULL → 0 への変換が必要ですが、COUNT は不要です。
問題 ▼

members テーブルと orders テーブルを使い、会員ごとの完了注文合計金額（completed_amount）とキャンセル注文件数（cancelled_count）を計算してください。注文がない会員も 0 として出力してください。出力列は member_id, name, completed_amount, cancelled_count、member_id 昇順。

使用テーブル
► members（5行）
member_idname
1田中
2鈴木
3佐藤
4伊藤
5山田
► orders（8行）
order_idmember_idstatusamount
11completed5000
21cancelled2000
32completed8000
42completed3000
53pending1500
63cancelled1000
74completed6000
81pending4000
期待出力 ▼
member_idnamecompleted_amountcancelled_count
1田中50001
2鈴木110000
3佐藤01
4伊藤60000
5山田00
模範解答コード ▼ 
SELECT
  m.member_id,
  m.name,
  COALESCE(
    SUM(CASE WHEN o.status = 'completed' THEN o.amount END),  -- 完了注文のみ合計
    0
  )                  AS completed_amount,
  COALESCE(
    COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END),  -- キャンセル注文を計数
    0
  )                  AS cancelled_count
FROM   members m
LEFT JOIN orders o ON m.member_id = o.member_id  -- 未注文の会員も保持
GROUP BY m.member_id, m.name
ORDER BY m.member_id;

/*
  実行順序:
  1. FROM & LEFT JOIN      → members と orders を結合（未注文も保持）
  2. GROUP BY              → 会員ごとにグループ化
  3. SELECT (CASE WHEN集計)  → 条件付き集計（COALESCEで0補正）
  4. ORDER BY              → 会員IDでソート
  */
解説（テーブル変化・ポイント） ▼
SELECT m.member_id, m.name, COALESCE( SUM(CASE WHEN o.status = 'completed' THEN o.amount END), 0 ) AS completed_amount, COALESCE( COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END), 0 ) AS cancelled_count FROM members m LEFT JOIN orders o ON m.member_id = o.member_id GROUP BY m.member_id, m.name ORDER BY m.member_id;
LEGEND ▼
データ取得・読込対象
① FROM members ▼
FROM members mベースとなる members テーブルの5行です。注文がない山田（member_id=5）もこの時点では存在します。
1 / 6
member_idname
1田中
2鈴木
3佐藤
4伊藤
5山田
5行
学習ポイント
ON 句 vs WHERE 句のフィルタ位置：LEFT JOIN の右テーブルへのフィルタを ON 句に書くと「一致なし行」は NULL のまま保持されます（LEFT JOIN の性質を維持）。WHERE 句に書くと、NULL 行が NULL = 'completed' → UNKNOWN → WHERE が除外し、実質 INNER JOIN になります。右テーブルへの絞り込みは ON 句に書くか、CASE WHEN を使うのが安全です。
CASE WHEN 集計で1回の LEFT JOIN から複数条件を集計する：SUM(CASE WHEN status='completed' THEN amount END) パターンで、1回の LEFT JOIN から複数の条件集計を安全に行えます。CASE の ELSE 省略 = ELSE NULL なので、SUM/COUNT は NULL をスキップして条件一致行のみを集計します。JOIN を複数回実行するより効率的で、NULL の扱いも一貫します。
SUM は0件で NULL / COUNT は0件で 0：グループ内の CASE WHEN 結果がすべて NULL のとき、SUM は NULL を返しますが COUNT は 0 を返します。SUM には COALESCE(SUM(...), 0) が必要ですが、COUNT には不要です（付けても問題なし）。この違いを意識することで不必要な COALESCE を省略でき、逆に必要な COALESCE の付け忘れを防げます。
アンチパターン
WHERE で LEFT JOIN を INNER JOIN 相当にする：LEFT JOIN orders o ON ... WHERE o.status = 'completed' と書くと o.status=NULL の行（注文なし・pending・cancelled の会員）が除外されます。注文なし会員（山田）や completed を持たない会員（佐藤）が消えるため、「全会員の集計」にならない重大なバグです。CASE WHEN 集計か、ON 句に AND o.status='completed' を追加してフィルタしてください。
複数回の LEFT JOIN で集計を分けようとする：completed と cancelled を別々の LEFT JOIN で取得して結合する設計は可能ですが、JOIN の順序と条件によってはデカルト積や行の重複が生じます。1回の LEFT JOIN + CASE WHEN 集計の方がシンプルで安全です。複数回 JOIN する場合は各サブクエリで GROUP BY を先に行ってから JOIN することで行数の爆発を防げます。
実務コラム：LEFT JOIN + CASE WHEN ピボット集計と dbt での設計
会員ごとのステータス別注文集計は典型的な「ピボット集計」パターンです。実務では 3〜5 種類のステータスを1クエリで集計することが多く、CASE WHEN を列として追加するだけで拡張できるのが利点です。dbt では intermediate モデルで LEFT JOIN + CASE WHEN 集計を行い、mart モデルで COALESCE NULL→0 の変換を行う分離設計が一般的です。COALESCE を一箇所に集約することで「NULL を0として扱う」ロジックの管理が容易になります。また PostgreSQL の crosstab 関数や BigQuery の PIVOT 句を使うと動的なピボットも可能ですが、静的な CASE WHEN の方が可読性と移植性が高いため通常は推奨されます。
5 / 5
問題を選択
次のセット →