SQL Quiz Book

SQL NULL — 伝播・GROUP BY・FULL OUTER JOINの基礎

基礎NULL基礎NULL伝播GROUP BY と NULLNULLS FIRST / LASTFULL OUTER JOINPostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 6

NULL の算術・文字列伝播 — NULL は演算で「感染」し結果を静かに消す

NULL伝播算術演算文字列結合 ||COALESCE補正
前提知識 ▼

SQL では NULL を含む全ての算術演算（+・−・×・÷）と文字列結合（|| 演算子）は、例外なく NULL を返します。これを NULL の伝播（Propagation）と呼びます。

-- 算術演算: NULL との全演算は NULL になる
NULL + 100          -- NULL（加算しても消える）
NULL * 0            -- NULL（ゼロを掛けても消える！）
base_salary + NULL  -- NULL（一方が値でも伝播する）

-- 文字列結合（|| 演算子）: NULL が含まれると全体が NULL
'営業' || NULL      -- NULL（PostgreSQL: || は NULL を伝播）

-- CONCAT 関数は NULL を空文字扱い（PostgreSQL/MySQL 共通）
CONCAT(NULL, 'suffix') -- 'suffix'
NULL は「感染する」値：NULL を含む式は式全体が NULL になります。NULL * 0 = NULL（0 を掛けてもゼロにならない！）は特に直感に反します。演算の前に COALESCE(col, default) で先に補正することが鉄則です。
問題 ▼

employee_pay テーブルから、実質給与（total_pay = base_salary + bonus、bonus が NULL なら 0 として加算）と表示ラベル（label = dept || ': ' || name、dept が NULL なら '未配属' として結合）を計算してください。出力列は emp_id, name, total_pay, label、emp_id 昇順で返してください。

使用テーブル
► employee_pay（6行）
emp_idnamebase_salarybonusdept
1田中30000050000営業
2鈴木250000NULL開発
3佐藤40000080000NULL
4伊藤280000NULL営業
5山田35000030000開発
6高橋320000NULLNULL
期待出力 ▼
emp_idnametotal_paylabel
1田中350000営業: 田中
2鈴木250000開発: 鈴木
3佐藤480000未配属: 佐藤
4伊藤280000営業: 伊藤
5山田380000開発: 山田
6高橋320000未配属: 高橋
模範解答コード ▼ 
SELECT
  emp_id,
  name,
  base_salary + COALESCE(bonus, 0)           AS total_pay, -- NULL を 0 に補正して加算
  COALESCE(dept, '未配属') || ': ' || name   AS label      -- NULL を '未配属' に補正して結合
FROM  employee_pay
ORDER BY emp_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM employee_pay   → 行を読み込む
  2. SELECT              → 列を評価（total_pay, label）
  3. ORDER BY emp_id     → 並び替えて出力
*/
解説（テーブル変化・ポイント） ▼
SELECT emp_id, name, base_salary + COALESCE(bonus, 0) AS total_pay, COALESCE(dept, '未配属') || ': ' || name AS label FROM employee_pay ORDER BY emp_id;
LEGEND ▼
データ取得・読込対象
① FROM employee_pay（6行） ▼
FROM employee_payemployee_pay テーブルの6行を読み込みます。bonus が NULL の行（emp_id=2,4,6）と dept が NULL の行（emp_id=3,6）があります。これらをそのまま算術演算・文字列結合すると NULL が全体に伝播します。
1 / 4
emp_idnamebase_salarybonusdept
1田中30000050000営業
2鈴木250000NULL開発
3佐藤40000080000NULL
4伊藤280000NULL営業
5山田35000030000開発
6高橋320000NULLNULL
6行読込
学習ポイント
NULL は算術演算・文字列結合で「感染」し式全体を NULL にする：NULL との全算術演算（+・−・×・÷）と文字列結合（||）は例外なく NULL を返します。NULL * 0 = NULL（0 を掛けてもゼロにならない！）、100 - NULL = NULL、'prefix' || NULL = NULL はすべて NULL 伝播の結果です。NULL は「値が不明」を意味するため、不明な値との演算結果も「不明（NULL）」になります。
「演算の前に COALESCE で補正する」が基本パターン：NULL が伝播する可能性がある列は演算の前に COALESCE(col, default) で NULL を安全な値に変換します。base_salary + COALESCE(bonus, 0) は bonus が NULL のとき 0 として加算し、total_pay が NULL になる問題を防ぎます。この「入口での NULL 解消」がデータ品質の鉄則です。
PostgreSQL の || 演算子と CONCAT 関数の NULL 挙動の違い：PostgreSQL の || は NULL を伝播させますが、CONCAT(a, b) 関数は NULL 引数を空文字として扱い連結します。ただし CONCAT の NULL 挙動は DBMS によって微妙に異なるため、移植性を優先するなら COALESCE で先に補正してから || を使うのが最も安全です。
アンチパターン
SUM(base_salary + bonus) で bonus=NULL の行が 0 として扱われると誤解する：base_salary + NULL は NULL になり SUM の集計から除外されます（Q2 で学んだ集計関数の NULL 無視と合わさって二重に消える）。正しくは SUM(base_salary + COALESCE(bonus, 0))。NULL 伝播バグは集計結果をこっそり小さくする最も静かなバグです。
NULL * 0 = 0 と思い込む：実績なし（NULL）のスコアに重みを掛けて 0 にしようとして score * weight を計算すると、score が NULL なら NULL が返ります。ゼロを掛けても NULL は消えません。COALESCE(score, 0) * weight か CASE WHEN score IS NULL THEN 0 ELSE score * weight END が正しい書き方です。
実務コラム：データパイプラインでの「早期 NULL 補正」パターン
NULL 伝播バグは静かに進行します。月次売上 = base_revenue + bonus_revenue の計算で特定の月だけ bonus_revenue が NULL になると、累積集計・前月比・グラフ表示に連鎖します。dbt では staging モデルで COALESCE(bonus_revenue, 0) AS bonus_revenue を施し、下流では NULL を意識しない「早期 NULL 補正パターン」が推奨されます。NULL 伝播バグの発見には COUNT(*) - COUNT(computed_col) で NULL になった行数を確認するアプローチが効果的です。
1 / 5
問題を選択
QUESTION 7

GROUP BY と NULL グループ — NULL 同士は「同じ仲間」として集約される

GROUP BY NULLSUM + GROUP BYNULL グループ化COALESCE ラベル変換
前提知識 ▼

WHERE 句では col = NULL が UNKNOWN になり行が除外されますが、GROUP BY では NULL 同士の行が同じグループにまとめられます。これは SQL 標準の特例動作です。

-- GROUP BY の特例: NULL = NULL は等値比較では UNKNOWN なのに
-- GROUP BY では NULL 同士が同一グループに集約される
SELECT device, COUNT(*)
FROM   page_views
GROUP BY device;
-- device = NULL の行が 1 グループにまとめられてカウントされる

-- HAVING で NULL グループを選択・除外できる
HAVING device IS NULL      -- NULL グループだけ抽出
HAVING device IS NOT NULL  -- NULL グループを除外
WHERE と GROUP BY の NULL 挙動の違い：WHERE の NULL 比較は UNKNOWN → 行除外。GROUP BY の NULL は同一グループに集約（特例）。WHERE で NULL を除外しない限り、NULL グループが集計結果に現れます。COALESCE でラベルを付与して可視化する設計が重要です。
問題 ▼

page_views テーブルから、ページ（page）とデバイス（device）ごとの総ビュー数を集計してください。device が NULL の行は '不明' として表示し（COALESCE を使用）、出力列は page, device_label, total_views、page 昇順 → device_label 昇順で返してください。

使用テーブル
► page_views（9行）
view_idpagedeviceviews
1topmobile500
2topNULL200
3toppc300
4topmobile400
5aboutpc100
6aboutNULL150
7aboutmobile200
8topNULL50
9aboutpc80
期待出力 ▼
pagedevice_labeltotal_views
aboutmobile200
aboutpc180
about不明150
topmobile900
toppc300
top不明250
模範解答コード ▼ 
SELECT
  page,
  COALESCE(device, '不明')  AS device_label, -- 表示用ラベルに変換（GROUP BY は元列で行う）
  SUM(views)                 AS total_views
FROM  page_views
GROUP BY page, device          -- NULL も 1 グループとして集計（特例動作）
ORDER BY page, device_label;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM page_views              → 行を読み込む
  2. GROUP BY page, device        → グループ化
  3. SELECT                       → 集計・ラベル付与（SUM, COALESCE）
  4. ORDER BY page, device_label  → 並び替えて出力
*/
解説（テーブル変化・ポイント） ▼
SELECT page, COALESCE(device, '不明') AS device_label, SUM(views) AS total_views FROM page_views GROUP BY page, device ORDER BY page, device_label;
LEGEND ▼
データ取得・読込対象
① FROM page_views（9行） ▼
FROM page_viewspage_views テーブルの9行を読み込みます。device が NULL の行（view_id=2,6,8）がデバイス不明のアクセスです。GROUP BY でこれらがどのように扱われるかが今回のポイントです。
1 / 5
view_idpagedeviceviews
1topmobile500
2topNULL200
3toppc300
4topmobile400
5aboutpc100
6aboutNULL150
7aboutmobile200
8topNULL50
9aboutpc80
9行読込
学習ポイント
GROUP BY は NULL を 1 つのグループとして集約する（WHERE の NULL 挙動と異なる）：WHERE 句の col = NULL は UNKNOWN → 行除外ですが、GROUP BY では NULL 同士の行が同じグループにまとめられます。これは SQL 標準の特例動作で、NULL ≠ NULL（等値比較）でありながら GROUP BY では NULL が同一グループに集約という直感に反する挙動です。WHERE と GROUP BY では NULL の扱いが異なることを覚えておいてください。
HAVING で NULL グループを選択・除外できる：GROUP BY 後に HAVING device IS NULL で NULL グループだけを抽出、HAVING device IS NOT NULL で NULL グループを除外できます。NULL グループを「デバイス不明」として特別扱いしたい場合は COALESCE を SELECT の表示用にだけ使い、GROUP BY は元の列（device）に対して行うのが基本設計です。
GROUP BY の列と SELECT の COALESCE は独立して動作する：GROUP BY device でグループ化し SELECT で COALESCE(device, '不明') AS device_label で表示名を変換します。GROUP BY COALESCE(device, '不明') にすると device が NULL の行と device = '不明'（文字列）の行が同一グループに集約される可能性があり、意図しない集計になります。GROUP BY とラベル変換は分離して設計してください。
アンチパターン
GROUP BY COALESCE(device, '不明') と GROUP BY device の違いを見落とす：前者はグループ化キーが COALESCE(device, '不明') になるため、device が NULL の行と device = '不明'（実際にそういう文字列データが存在する場合）が同一グループに集約されます。意図的でない限り GROUP BY は元の列で行い、COALESCE は SELECT の表示変換にだけ使ってください。
GROUP BY の NULL グループを COALESCE なしで出力してダッシュボードに NULL ラベルを露出させる：SELECT に COALESCE(device, '不明') を付け忘れると NULL グループが NULL のまま出力されます。BI ツールでは NULL が空白やエラーとして表示されエンドユーザーへの混乱を招きます。常に COALESCE か CASE WHEN IS NULL でラベルを付与してください。
実務コラム：「Unknown」グループの明示と分析品質
device や region が NULL のデータを「不明」として明示的に集計に含めることで、データ品質の可視化ができます。例えば「device が不明な page_views が全体の 12%」と分かれば、ユーザーエージェント取得ロジックの改善検討につながります。NULL グループを HAVING device IS NOT NULL で除外すると問題が見えなくなります。分析品質の観点から、NULL グループは常に明示して total_views にも含め、欠損の規模を定期的にモニタリングする設計が推奨されます。
2 / 5
問題を選択
QUESTION 8

CASE WHEN と NULL の落とし穴 — NULL はすべての WHEN をすり抜け ELSE へ落ちる

CASE WHENIS NULL チェックNULL の ELSE 落下防御的分岐設計
前提知識 ▼

CASE WHEN は条件を上から順に評価し、最初に TRUE になった WHEN で結果が確定します。NULL との比較（>=、<=、=）は UNKNOWN になり、その WHEN をスキップして次の WHEN へ進みます。結果として NULL は全ての WHEN をすり抜け ELSE に落ちます。

-- score が NULL のとき各 WHEN の評価結果
CASE
  WHEN score >= 80 THEN 'A'  -- NULL >= 80 → UNKNOWN → スキップ
  WHEN score >= 60 THEN 'C'  -- NULL >= 60 → UNKNOWN → スキップ
  ELSE                  'D'  -- NULL はここへ落ちる（欠席なのに 'D' になる！）
END

-- 正しい書き方: IS NULL チェックを最初の WHEN に置く
CASE
  WHEN score IS NULL THEN '欠席'  -- IS NULL は TRUE/FALSE を確定的に返す
  WHEN score >= 80   THEN 'A'
END
ELSE を省略すると NULL が返る：全ての WHEN が TRUE にならず ELSE もない場合、CASE 全体が NULL を返します。ELSE は必ず明示し、NULL の誤分類バグを防いでください。
問題 ▼

exam_results テーブルから、スコアに応じて grade（A/B/C/D/欠席）を判定してください。
score >= 80: 'A' ／ 70 ≤ score < 80: 'B' ／ 60 ≤ score < 70: 'C' ／ score < 60: 'D' ／ score が NULL: '欠席'
出力列は student_id, name, score, grade、student_id 昇順で返してください。

使用テーブル
► exam_results（7行）
student_idnamescore
1田中85
2鈴木42
3佐藤NULL
4伊藤70
5山田55
6高橋NULL
7中村60
期待出力 ▼
student_idnamescoregrade
1田中85A
2鈴木42D
3佐藤NULL欠席
4伊藤70B
5山田55D
6高橋NULL欠席
7中村60C
模範解答コード ▼ 
SELECT
  student_id,
  name,
  score,
  CASE
    WHEN score IS NULL THEN '欠席'  -- 最初に NULL をチェック（重要）
    WHEN score >= 80   THEN 'A'
    WHEN score >= 70   THEN 'B'
    WHEN score >= 60   THEN 'C'
    ELSE                    'D'   -- score < 60 の全行（NULL は上で捕捉済み）
  END AS grade
FROM  exam_results
ORDER BY student_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM exam_results     → 行を読み込む
  2. SELECT               → 列を評価（CASE WHEN で grade 判定）
  3. ORDER BY student_id  → 並び替えて出力
*/
解説（テーブル変化・ポイント） ▼
SELECT student_id, name, score, CASE WHEN score IS NULL THEN '欠席' WHEN score >= 80 THEN 'A' WHEN score >= 70 THEN 'B' WHEN score >= 60 THEN 'C' ELSE 'D' END AS grade FROM exam_results ORDER BY student_id;
LEGEND ▼
データ取得・読込対象
① FROM exam_results（7行） ▼
FROM exam_resultsexam_results テーブルの7行を読み込みます。student_id=3,6 の score が NULL（欠席者）です。これらを CASE WHEN で正しく '欠席' に分類するため IS NULL チェックの位置が鍵になります。
1 / 4
student_idnamescore
1田中85
2鈴木42
3佐藤NULL
4伊藤70
5山田55
6高橋NULL
7中村60
7行読込
学習ポイント
CASE WHEN の評価は上から順で、NULL は全ての比較 WHEN をすり抜ける：CASE WHEN は条件を上から順に評価し最初に TRUE になった WHEN で結果が確定します。NULL との比較（>=・<=・=）は UNKNOWN になりその WHEN を通過します。ELSE は全ての WHEN が TRUE でなかった行の受け皿で、ELSE を省略すると NULL が返ります。NULL は「条件を満たさない」のではなく「条件の評価が不能」なため、意図しない ELSE への落下が起きます。
NULL チェック（IS NULL）を CASE WHEN の最初に書くのが鉄則：WHEN score IS NULL THEN '欠席' を最初の WHEN に置くことで NULL 行を確実に捕捉できます。IS NULL は TRUE/FALSE を確定的に返す（UNKNOWN にならない）ため最初の WHEN で確実に捕捉されます。ELSE の直前に書いても動作しますが「NULL は特別なケース」と明示するため先頭配置が可読性の観点でも優れています。
ELSE を省略すると全条件を満たさない行（NULL を含む）が NULL を返す：CASE WHEN score >= 80 THEN 'A' WHEN score >= 60 THEN 'C' END のように ELSE なしで書くと、score が NULL または条件を満たさない行は NULL が返ります。CASE ... END はデフォルトで NULL を返すことを常に意識し、ELSE を明示的に書いてください。
アンチパターン
IS NULL チェックなしで範囲条件の CASE WHEN を書く：CASE WHEN score >= 60 THEN '合格' ELSE '不合格' END は score が NULL のとき NULL >= 60 → UNKNOWN → WHEN スキップ → ELSE → '不合格' になります。欠席者（NULL）が「不合格」として扱われ集計に混入するバグの典型例です。NULL が特別な意味を持つ列には必ず WHEN col IS NULL THEN '...' を最初の WHEN に追加してください。
CASE + 集計関数の組み合わせで ELSE を省略する：SUM(CASE WHEN category = 'A' THEN amount END) のように ELSE を省略すると、category = 'A' でない行と category が NULL の行で CASE 全体が NULL を返し SUM がそれを除外します。集計対象外の行を 0 にしたい場合は ELSE 0 を必ず書いてください。
実務コラム：CASE WHEN の NULL 防御チェックリスト
分析 SQL では NULL に対する「防御的な書き方」が信頼性の高いレポートを生みます。チェックリスト：① 範囲条件の CASE WHEN を書く前に「この列は NULL になりうるか」を確認する。② なりうる場合は WHEN col IS NULL THEN '...' を最初の WHEN に追加する。③ ELSE を必ず明示する（ELSE NULL は避ける）。④ 期待出力と実際の出力を比較して NULL 行の分類を検証する。特に KPI 計算や dashboard クエリでは NULL の誤分類が数値の信頼性を損なうため、定期的な NULL 件数チェックをモニタリングに組み込む設計が重要です。
3 / 5
問題を選択
QUESTION 9

ORDER BY と NULL のソート順 — DESC で NULL が先頭に来る落とし穴

ORDER BY NULLNULLS FIRSTNULLS LASTソート順のDBMS差異
前提知識 ▼

ORDER BY における NULL のソート順は DBMS によって異なります。特に PostgreSQL では DESC ソートのデフォルトが NULLS FIRST（NULL が先頭）になるため、「最近のものを先に」とすると NULL（未販売）が先頭に出てしまう落とし穴があります。

-- PostgreSQL のデフォルトソート規則
ORDER BY last_sold_at ASC   -- NULLS LAST がデフォルト（NULL は最後）
ORDER BY last_sold_at DESC  -- NULLS FIRST がデフォルト（NULL が先頭！←落とし穴）

-- 明示的に指定する方法（PostgreSQL / Oracle / 標準 SQL）
ORDER BY last_sold_at DESC NULLS LAST   -- NULL を末尾に
ORDER BY last_sold_at ASC  NULLS FIRST  -- NULL を先頭に

-- MySQL / SQL Server では NULLS FIRST/LAST 非対応 → 代替手法
ORDER BY COALESCE(last_sold_at, '1900-01-01'::date) DESC
MySQL / SQL Server の NULL ソート：NULL は最小値として扱われるため ASC で先頭、DESC で末尾になります。PostgreSQL（ASC=末尾, DESC=先頭）と逆の動作のため、マルチ DBMS 環境での移植時に要注意です。BigQuery・Snowflake は PostgreSQL と同じ規則です。
問題 ▼

products テーブルから、最近売れた商品を先に（last_sold_at DESC）、まだ売れていない商品（NULL）は最後に表示してください。出力列は product_id, name, last_sold_at で返してください。

使用テーブル
► products（7行）
product_idnamelast_sold_at
1商品A2024-03-15
2商品B2024-01-20
3商品CNULL
4商品D2024-03-20
5商品ENULL
6商品F2024-02-10
7商品GNULL
期待出力 ▼
product_idnamelast_sold_at
4商品D2024-03-20
1商品A2024-03-15
6商品F2024-02-10
2商品B2024-01-20
3商品CNULL
5商品ENULL
7商品GNULL
模範解答コード ▼ 
SELECT
  product_id,
  name,
  last_sold_at
FROM  products
ORDER BY last_sold_at DESC NULLS LAST;  -- NULL を明示的に末尾へ

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM products                          → 行を読み込む
  2. SELECT                                 → 列を取得
  3. ORDER BY last_sold_at DESC NULLS LAST  → 並び替えて出力
*/
解説（テーブル変化・ポイント） ▼
SELECT product_id, name, last_sold_at FROM products ORDER BY last_sold_at DESC NULLS LAST;
LEGEND ▼
データ取得・読込対象
① FROM products（7行） ▼
FROM productsproducts テーブルの7行を読み込みます。product_id=3,5,7 の last_sold_at が NULL（未販売商品）です。ORDER BY DESC でこれらをどこに配置するかが今回のポイントです。
1 / 4
product_idnamelast_sold_at
1商品A2024-03-15
2商品B2024-01-20
3商品CNULL
4商品D2024-03-20
5商品ENULL
6商品F2024-02-10
7商品GNULL
7行読込
学習ポイント
PostgreSQL の NULL ソート規則：ASC は NULLS LAST、DESC は NULLS FIRST がデフォルト：ASC（昇順）では NULL は最後（NULLS LAST がデフォルト）、DESC（降順）では NULL が先頭（NULLS FIRST がデフォルト）になります。これが「最近売れた商品を先に出したい（DESC）のに、未販売商品（NULL）が先頭に来てしまう」という実務でよく遭遇するバグの根本原因です。
NULLS FIRST / NULLS LAST で NULL の位置を明示的に指定する：ORDER BY col DESC NULLS LAST で NULL を末尾に、ORDER BY col ASC NULLS FIRST で NULL を先頭に固定できます。これは SQL 標準（ISO/IEC 9075）の構文で PostgreSQL・Oracle・BigQuery・Snowflake で使用可能です。MySQL・SQL Server・SQLite は NULLS FIRST/LAST をサポートしていないため代替手法が必要です。
COALESCE を使ったポータブルな NULL ソート：ORDER BY COALESCE(last_sold_at, '1900-01-01'::date) DESC のように NULL を番兵値（sentinel value）に変換して末尾へ押し込む方法は全 DBMS で動作します。ただし番兵値が実データと衝突しないことを確認し、コメントで意図を明記してください。
アンチパターン
ダッシュボードで ORDER BY date_col DESC して NULL（未収集データ）が先頭に来る：週次レポートで「直近の更新日順」に並べたのに、update_date が NULL の行（データ未収集）が先頭に表示されるバグ。NULLS LAST を知らないと原因が分からず、WHERE date_col IS NOT NULL で NULL を除外してしまい（本来表示すべきデータを消す）、正しい解決策を見つけられないことがあります。
MySQL や SQL Server で NULLS LAST を書くと構文エラー：PostgreSQL で書いた ORDER BY col DESC NULLS LAST を MySQL や SQL Server に移植するとエラーになります。移植性が必要な場合は ORDER BY CASE WHEN col IS NULL THEN 1 ELSE 0 END, col DESC（NULL を最後に押し込む）か、COALESCE で番兵値を使ってください。
実務コラム：DBMS 間の NULL ソート挙動の違いと移植性
PostgreSQL（ASC=NULLS LAST, DESC=NULLS FIRST）と MySQL / SQL Server（どちらも NULL が最小値として扱われ ASC で先頭、DESC で末尾）では NULL のソート挙動が逆になります。BigQuery・Snowflake は PostgreSQL と同じ規則（ASC=NULLS LAST, DESC=NULLS FIRST）を採用しています。マルチ DBMS 環境やクラウドデータウェアハウスへの移行時には NULL ソートの違いが集計レポートの順番変化として現れます。NULLS FIRST / NULLS LAST を明示するか、COALESCE で補正するかを統一的に決めておくことがチームの生産性向上につながります。
4 / 5
問題を選択
QUESTION 10

FULL OUTER JOIN と NULL — 両テーブルの「欠損」を一度に検出する

FULL OUTER JOINCOALESCE欠損の双方向検出データ品質チェック
前提知識 ▼

FULL OUTER JOIN は LEFT JOIN と RIGHT JOIN を合体させたもので、両テーブルの全行を保持します。どちらかのテーブルに対応行がない場合、相手側の列が NULL になります。

SELECT
  COALESCE(a.id, b.id)  AS id,  -- どちらかの値を必ず取得
  a.value               AS val_a,
  b.value               AS val_b
FROM  table_a a
FULL OUTER JOIN table_b b ON a.id = b.id

-- a.value IS NULL → table_b にしかない行（a に欠損）
-- b.value IS NULL → table_a にしかない行（b に欠損）
LEFT / RIGHT / FULL OUTER JOIN の比較：LEFT JOIN は「右にない行」を検出します。FULL OUTER JOIN は「どちらかにしかない行」を両方向から一度に検出できます。予実照合・マスタ照合・データ品質チェックで威力を発揮します。
問題 ▼

expected_revenue（予算）と actual_revenue（実績）を FULL OUTER JOIN して、月ごとの予算・実績・ステータス（'正常' / '実績未入力' / '予算未策定'）を出力してください。出力列は month, expected, actual, status、month 昇順で返してください。

使用テーブル
► expected_revenue（5行）
monthexpected
2024-01500000
2024-02600000
2024-03550000
2024-04700000
2024-05650000
► actual_revenue（5行）
monthactual
2024-01480000
2024-02620000
2024-03590000
2024-04710000
2024-06430000
期待出力 ▼
monthexpectedactualstatus
2024-01500000480000正常
2024-02600000620000正常
2024-03550000590000正常
2024-04700000710000正常
2024-05650000NULL実績未入力
2024-06NULL430000予算未策定
模範解答コード ▼ 
SELECT
  COALESCE(e.month, a.month)  AS month,  -- どちらかに存在する月を確実に取得
  e.expected,
  a.actual,
  CASE
    WHEN e.month IS NULL THEN '予算未策定'  -- actual のみ → e.month が NULL
    WHEN a.month IS NULL THEN '実績未入力'  -- expected のみ → a.month が NULL
    ELSE                      '正常'
  END AS status
FROM       expected_revenue e
FULL OUTER JOIN actual_revenue a
  ON e.month = a.month
ORDER BY month;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM expected_revenue e            → 左テーブルを読み込む
  2. FULL OUTER JOIN actual_revenue a   → 結合（両表の全行を保持）
  3. SELECT                             → 列を評価（month, status）
  4. ORDER BY month                     → 並び替えて出力
*/
解説（テーブル変化・ポイント） ▼
SELECT COALESCE(e.month, a.month) AS month, e.expected, a.actual, CASE WHEN e.month IS NULL THEN '予算未策定' WHEN a.month IS NULL THEN '実績未入力' ELSE '正常' END AS status FROM expected_revenue e FULL OUTER JOIN actual_revenue a ON e.month = a.month ORDER BY month;
LEGEND ▼
データ取得・読込対象
① FROM expected_revenue（5行）& actual_revenue（5行） ▼
FROM expected_revenue e / actual_revenue aexpected_revenue の5行を左テーブルとして読み込みます。右テーブル actual_revenue の5行は 2024-01〜04 が一致、2024-05 は expected のみ、2024-06 は actual のみに存在します。FULL OUTER JOIN でこの両方の欠損を検出します。
1 / 5
e.month (expected)e.expecteda.month (actual)a.actual
2024-015000002024-01480000
2024-026000002024-02620000
2024-035500002024-03590000
2024-047000002024-04710000
2024-05650000（なし）（なし）
（なし）（なし）2024-06430000
expected:5行 / actual:5行（2024-05はexpectedのみ、2024-06はactualのみ）
学習ポイント
FULL OUTER JOIN = LEFT JOIN + RIGHT JOIN：左テーブルにのみ存在する行（右が NULL）、右テーブルにのみ存在する行（左が NULL）、両方に存在する行を全て取得します。LEFT JOIN は「右テーブルに存在しない左行」を検出しましたが（Q5 で学習）、FULL OUTER JOIN は「どちらかにしか存在しない行」を両方向から一度に検出できます。データ品質チェック・マスタ照合・予実比較で威力を発揮します。
片方の月が NULL → COALESCE(e.month, a.month) で確実に month を取得する：FULL OUTER JOIN 後 e.month または a.month が NULL になりうるため COALESCE(e.month, a.month) でどちらかに存在する月を確実に取り出します。ORDER BY month（エイリアス参照）も COALESCE の結果でソートされるため正しく機能します。
e.month IS NULL / a.month IS NULL で「欠損の向き」を分類する：e.month IS NULL → 右（actual）にしかない行（予算未策定）、a.month IS NULL → 左（expected）にしかない行（実績未入力）。CASE WHEN で STATUS 列を付けることで両テーブル間のデータ不整合を一目で把握できます。予実管理・マスタ照合・データ品質ダッシュボードで標準的なパターンです。
アンチパターン
INNER JOIN でデータを欠損させる：INNER JOIN を使うと両テーブルに存在しない月（2024-05 と 2024-06）が結果から消えます。「この月のデータがない」という情報自体が重要なのに INNER JOIN ではそれが不可視になります。データ品質チェックには必ず FULL OUTER JOIN を使ってください。
COALESCE(e.month, a.month) を忘れて ORDER BY が NULL になる：FULL OUTER JOIN 後に SELECT e.month だけを書くと、right-only の行（2024-06）で e.month が NULL になります。ORDER BY e.month で NULL が意図しない位置に来る上 month 列自体が NULL として出力されます。COALESCE(e.month, a.month) AS month で month 列を統一し、以降の処理でこのエイリアスを参照してください。
実務コラム：FULL OUTER JOIN の実務活用と dbt での予実照合パターン
月次予算（budget テーブル）と実績（actual テーブル）の照合は、データ分析チームで最も頻繁に依頼される作業のひとつです。FULL OUTER JOIN を使うことで「予算は策定したが実績がない月」と「実績はあるが予算がない月（予算管理漏れ）」の両方を一度に検出できます。dbt では予実ブリッジモデル（mart 層）として FULL OUTER JOIN を実装し status カラムを付けることで、データエンジニアが upstream の欠損を素早く検知できる運用体制が構築できます。また COALESCE + IS NULL の組み合わせはマスタテーブルと実績テーブルの整合性確認（例：商品マスタに存在しない取引の検出）にも同様に活用できます。
5 / 5
問題を選択
次のセット →