SQL Quiz Book

SQL JOIN — 複数テーブル結合とテーブル変化の基礎

基礎JOIN複数テーブル結合Web開発PostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 6

LEFT JOIN + カレンダーマスタ — 売上ゼロの日も欠損させずに時系列データを作る

LEFT JOINマスタ結合COALESCEゼロ補完
前提知識 ▼

売上データだけを GROUP BY すると、「売上が1件もなかった日」は結果から消滅してしまい、グラフを描画した際にX軸の日付が飛んでしまいます。
これを防ぐため、すべての日付が連続して入っている「カレンダーテーブル」を主軸（左側）にして、売上データを LEFT JOIN します。

FROM   calendar AS c
LEFT JOIN sales AS s ON c.cal_date = s.sale_date;
COALESCE の活用： 売上がない日の集計結果は NULL になるため、COALESCE(SUM(amount), 0) を使って 0 に変換するのがセットで使われる定石です。
問題 ▼

10月1日〜10月3日の日付を持つカレンダーテーブル（calendar）と、売上テーブル（sales）があります。

カレンダーテーブルをベースにして売上を LEFT JOIN し、各日付の売上合計（daily_sales）を算出してください。売上がない日は 0 になるようにします。

使用テーブル
▸ calendar
cal_date
10-01
10-02
10-03
▸ sales
sale_dateamount
10-01100
10-01150
10-03200
期待出力 ▼
cal_datedaily_sales
10-01250
10-020
10-03200
模範解答コード ▼ 
SELECT
  c.cal_date,
  COALESCE(SUM(s.amount), 0) AS daily_sales  -- 売上が無い日は 0

FROM calendar AS c
LEFT JOIN sales AS s  -- 売上の無い日も残す
  ON c.cal_date = s.sale_date
GROUP BY
  c.cal_date
ORDER BY
  c.cal_date;

/*
  実行順序:
  1. FROM calendar AS c    → カレンダー
  2. LEFT JOIN sales AS s  → 日付で照合
  3. GROUP BY c.cal_date   → 展開された仮想テーブルをカレンダーの日付ごとにグループ化
  4. SELECT ...            → SUM で集計
  5. ORDER BY c.cal_date   → 日付順に並び替え
  */
解説（テーブル変化・ポイント） ▼
SELECT c.cal_date, COALESCE(SUM(s.amount), 0) AS daily_sales FROM calendar AS c LEFT JOIN sales AS s ON c.cal_date = s.sale_date GROUP BY c.cal_date ORDER BY c.cal_date;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM calendar AS cデータ欠損を防ぐための「軸」となるカレンダーテーブルを読み込みます。
1 / 5
cal_date
'10-01'
'10-02'
'10-03'
全 3行 読込
学習ポイント
JOIN PATTERN
カレンダーマスタとの LEFT JOIN
連続した日付データを軸にして欠損を防ぐ
c = calendar   s = sales
連続データの生成（ゼロ埋め）：Webダッシュボードなどのグラフ描画において、データソースの段階で日付の抜け漏れをなくすことはフロントエンドの処理を劇的にシンプルにします。SQLでこれを実現する最も標準的な手法が「カレンダーテーブルとのLEFT JOIN」です。
カレンダーテーブルの作り方：実務では、10年分程度の日付を持つ物理テーブル calendar_master を作っておく方法と、PostgreSQLの generate_series() 関数などを使ってクエリ内で動的に生成する（CTEを利用する）方法の2通りがよく使われます。
アンチパターン
集計してから JOIN していないか： GROUP BY と LEFT JOIN を組み合わせる場合、行が爆発しないように「先にサブクエリで日付ごとにSUMしてからカレンダーと結合する」方がパフォーマンスが良いケースがあります。データ量が増えてきたら実行計画を確認しましょう。
1 / 5
問題を選択
QUESTION 7

LEFT JOIN連鎖の罠 — テーブル結合順序による意図しないデータ消失を防ぐ

LEFT JOININNER JOIN結合順序データ消失
前提知識 ▼

ベースとなるテーブルに複数のテーブルを繋げていく際、一度 LEFT JOIN を使って「NULL」を許容した行に対し、その後うっかり INNER JOIN を繋げてしまうと、せっかく残した行が消滅（データ消失）してしまいます。

-- ✗ 誤った連鎖 (orders が無いユーザーが消える)
FROM users LEFT JOIN orders ON ... INNER JOIN items ON ...

-- ✓ 正しい連鎖 (すべて LEFT JOIN で繋ぐ)
FROM users LEFT JOIN orders ON ... LEFT JOIN items ON ...
なぜ消えるのか？： LEFT JOIN orders によって作られた orders.item_id = NULL の行が、次の INNER JOIN items の ON句評価において「itemsに一致しない」とみなされ、除外されてしまうからです。
問題 ▼

ユーザー（users）、注文（orders）、商品マスタ（items）の3つのテーブルがあります。

注文履歴がないユーザーも含めて、全員の名前と注文した商品名を出力する正しいSQLを記述してください。注文がない場合、商品名は NULL となります。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3鈴木
▸ orders
user_iditem_id
110
220
▸ items
item_iditem_name
10PC
20マウス
期待出力 ▼
nameitem_name
田中PC
佐藤マウス
鈴木NULL
模範解答コード ▼ 
SELECT
  u.name,
  i.item_name

FROM users AS u
LEFT JOIN orders AS o        -- ユーザーを全て残しつつ orders と結合 (鈴木は NULL になる)
  ON u.user_id = o.user_id
LEFT JOIN items AS i        -- アイテムも LEFT JOIN（INNER だと鈴木が消える）
  ON o.item_id = i.item_id
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users AS u
  2. LEFT JOIN orders AS o
  3. LEFT JOIN items AS i        → 仮想テーブルと items を結合
  4. SELECT u.name, i.item_name  → 指定した列を射影
  */
解説（テーブル変化・ポイント） ▼
SELECT u.name, i.item_name FROM users AS u LEFT JOIN orders AS o ON u.user_id = o.user_id LEFT JOIN items AS i ON o.item_id = i.item_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uすべてのユーザー（3行）をベーステーブルとして読み込みます。
1 / 4
user_idname
1田中
2佐藤
3鈴木
全 3行 読込
学習ポイント
JOIN PATTERN
LEFT JOIN の連鎖
ベーステーブルから伸びる枝はすべてLEFTで繋ぐ
u = users   o = orders   i = items
結合順序による行の消失：「すべてのユーザーを出したい」と思って最初に LEFT JOIN を使っても、その後の結合で INNER JOIN を使ってしまうと、せっかく補完されたNULL行がフィルタリングされて消えてしまいます。実務での「データ件数が合わない」バグの代表格です。
明確な設計意図を持つ：「主軸（絶対に消したくないテーブル）」を決めたら、そこからぶら下がる関連テーブルは原則すべて LEFT JOIN で繋いでいくのがセーフティな記述です。逆に、フィルタリングを目的とする結合は明示的に INNER JOIN を使います。
アンチパターン
LEFT JOIN 先のテーブルで WHERE 絞り込み： 連鎖的にLEFT JOINしたテーブルの列に対して、うっかり WHERE i.item_name = 'PC' のように WHERE 句で条件を指定してしまうと、NULL の行（PCを買っていないユーザー）が丸ごと除外されてしまい、実質的に INNER JOIN と同じ挙動になってしまいます（暗黙のINNER JOIN）。条件は ON i.item_name = 'PC' に書く必要があります。
SELECT u.name, i.item_name FROM users AS u LEFT JOIN orders AS o ON u.user_id = o.user_id LEFT JOIN items AS i ON o.item_id = i.item_id WHERE i.item_name = 'PC';
LEGEND ▼
データ取得・読込対象
① LEFT JOIN 完了時 ▼
すべてのテーブルを LEFT JOIN した直後LEFT JOIN によって、注文がない鈴木の行は NULL で補完された状態で残っています。
1 / 2
nameitem_name
田中'PC'
佐藤'マウス'
鈴木NULL
3行
2 / 5
問題を選択
QUESTION 8

最新レコード抽出 (Top-1 JOIN) — サブクエリの集計結果を利用して詳細行を引く

INNER JOINサブクエリ複合キーTop-N抽出
前提知識 ▼

あるグループ（例：ユーザー）ごとに「最新の日付」や「最高スコア」を持つ特定の一行（Top-1）の全カラムを取得したい場面は頻出します。
GROUP BY だけでは集計キー以外の列を取得できないため、サブクエリで最新日付を算出し、それを元のテーブルに複合キーで INNER JOIN してレコード全体を引き当てます。

FROM logs AS a
INNER JOIN (
  SELECT id, MAX(date) AS max_d FROM logs GROUP BY id
) AS b
  ON a.id = b.id AND a.date = b.max_d;
問題 ▼

ユーザーのログイン履歴テーブル（login_history）があります。同一ユーザーが複数回ログインしています。

各ユーザーの「一番最新のログインレコード」だけを抽出し、user_id、最新日付（login_date）、およびその時の端末（device）を取得してください。

使用テーブル
▸ login_history
user_idlogin_datedevice
110-01PC
110-05スマホ
210-02PC
210-03PC
期待出力 ▼
user_idlogin_datedevice
110-05スマホ
210-03PC
模範解答コード ▼ 
SELECT
  h.user_id,
  h.login_date,
  h.device

FROM login_history AS h
INNER JOIN (
  -- サブクエリ: ユーザーごとに最新の日付を計算
  SELECT user_id, MAX(login_date) AS max_date
  FROM login_history
  GROUP BY user_id
) AS latest
  ON  h.user_id = latest.user_id               -- ユーザーIDと日付の両方が一致する行(=最新行)だけを結合・抽出
  AND h.login_date = latest.max_date
ORDER BY h.user_id;

/*
  実行順序:
  1. [サブクエリ] FROM login_history       → テーブル
  2. [サブクエリ] GROUP BY user_id         → ユーザーごとに MAX 日付を算出
  3. FROM login_history AS h          → メインのテーブル
  4. INNER JOIN ... AS latest ON ...  → メインの行とサブクエリ結果を複合キー照合
  5. SELECT h.user_id, ...            → 必要な列を射影
  */
解説（テーブル変化・ポイント） ▼
SELECT h.user_id, h.login_date, h.device FROM login_history AS h INNER JOIN ( SELECT user_id, MAX(login_date) AS max_date FROM login_history GROUP BY user_id ) AS latest ON h.user_id = latest.user_id AND h.login_date = latest.max_date;
LEGEND ▼
データ取得・読込対象
① サブクエリ FROM ▼
FROM login_historyまずサブクエリ内で login_history テーブル（4行）を読み込みます。
1 / 6
user_idlogin_datedevice
1'10-01''PC'
1'10-05''スマホ'
2'10-02''PC'
2'10-03''PC'
全 4行 読込
学習ポイント
JOIN PATTERN
Top-1 JOIN（自己結合的サブクエリ）
集計したキーを使って元のテーブルから詳細行を抜く
h = history   sub = MAX日付
GROUP BY の限界を超える：SELECT user_id, MAX(date), device ... GROUP BY user_id と書くと、device が GROUP BY に含まれていないため SQLエラーになります。これを突破するためには「集計用」と「詳細取得用」のフェーズを明確に分け、JOINでつなぎ合わせるという発想が必要です。
実務での頻出シーン：「各ブログカテゴリの最新記事」「各ユーザーの最後の注文」など、システム開発において「グループごとの代表1行（の全データ）」を取得するケースは無限に登場します。この JOIN パターンは基礎中の基礎として必ず習得してください。
実務コラム
モダンなSQL環境（PostgreSQL, MySQL 8.0以降等）では、この Top-N 抽出タスクは「ウィンドウ関数（ROW_NUMBER()）」を使って解くのが現代のベストプラクティスです。
WITH ranked AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date DESC) as rn FROM login_history) SELECT * FROM ranked WHERE rn = 1;
このように書くと JOIN すら不要になり、さらに「Top-3まで欲しい」といった拡張も容易になります。しかし、JOINの仕組みを理解しておくことはベースとして非常に重要です。
3 / 5
問題を選択
QUESTION 9

期間重複チェック — 不等価JOINを活用してダブルブッキングを検知する

INNER JOIN自己結合不等価結合期間重複
前提知識 ▼

会議室の予約システムなどにおいて、「異なる2つの予約の時間が被っているか（重複しているか）」を検知するには、自己結合（SELF JOIN）と不等価結合を組み合わせます。

2つの期間（Start〜End）が重複する条件は論理的に以下の一文で表せます。
A.start < B.end AND A.end > B.start
これを ON 句に記述して JOIN すれば、重複しているペアだけが抽出されます。

SELECT a.id_col, b.id_col
FROM   table_name a
JOIN   table_name b               -- 同じテーブルを別名で2度使う（自己結合）
  ON   a.group_col = b.group_col    -- 同じグループ内で比較する
  AND  a.id_col < b.id_col          -- 同じペアを2回出さない
  AND  a.start_col < b.end_col      -- 不等価条件で期間の関係を表す
  AND  a.end_col   > b.start_col;
問題 ▼

会議室の予約テーブル（reservations）があります。

同じ部屋（room_name）で予約時間が被ってしまっている（ダブルブッキングしている）予約のペアを探し、両者の予約ID（res_a, res_b）と部屋名を出力してください。

※自分自身との比較や、(A,B)と(B,A)のような逆順ペアの重複出力を防ぐため、r1.res_id < r2.res_id という条件も加えます。

使用テーブル
▸ reservations
res_idroom_namestart_tend_t
1会議室A10:0012:00
2会議室A11:0013:00
3会議室A13:0014:00
4会議室B10:0012:00
期待出力 ▼
res_ares_broom_name
12会議室A
模範解答コード ▼ 
SELECT
  r1.res_id AS res_a,
  r2.res_id AS res_b,
  r1.room_name

FROM reservations AS r1
INNER JOIN reservations AS r2      -- 自分自身のテーブルと結合(SELF JOIN)
  ON  r1.room_name = r2.room_name
  AND r1.res_id < r2.res_id        -- 同一ペアや逆順ペアを排除
  AND r1.start_t < r2.end_t        -- 期間が被っているかどうかの判定ロジック
  AND r1.end_t   > r2.start_t;

/*
  実行順序:
  1. FROM reservations AS r1        → ベースとして4行を読み込む
  2. INNER JOIN reservations AS r2  → 総当たりで組み合わせを生成し、ON句で評価する
  3. SELECT r1.res_id, ...          → 通過した1ペアだけを射影して出力
  */
解説（テーブル変化・ポイント） ▼
SELECT r1.res_id AS res_a, r2.res_id AS res_b, r1.room_name FROM reservations AS r1 INNER JOIN reservations AS r2 ON r1.room_name = r2.room_name AND r1.res_id < r2.res_id AND r1.start_t < r2.end_t AND r1.end_t > r2.start_t;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM reservations AS r1予約テーブル（4行）を左側のベースとして読み込みます。
1 / 5
res_idroom_namestart_tend_t
1'会議室A''10:00''12:00'
2'会議室A''11:00''13:00'
3'会議室A''13:00''14:00'
4'会議室B''10:00''12:00'
全 4行 読込
学習ポイント
JOIN PATTERN
自己結合＋期間の不等価結合
同じテーブル同士を不等号条件でクロスマッチさせる
r1 = res (左)   r2 = res (右)
期間重複の論理：「Aの終了がBの開始より後」かつ「Aの開始がBの終了より前」であれば、必ず二つの期間は重なっています。これはデータベースだけでなく、JavaScriptなどのプログラミング一般で使える汎用的な衝突判定（AABB等）のアルゴリズムです。
r1.res_id < r2.res_id の意図：自己結合を行うと、全く同じ行同士（r1の1とr2の1）も評価されてしまい、当然時間は被っているので出力されてしまいます。また、(1と2)という結果と(2と1)という結果が二重に出るのも防ぐため、IDの大小関係をつけることで「異なるペアの片道切符」だけを評価対象に絞り込んでいます。
4 / 5
問題を選択
QUESTION 10

前日比較 (ズラし結合) — 自己結合で1つ前のデータを横付けして推移を計算する

LEFT JOIN自己結合ズラし結合前日比
前提知識 ▼

時系列データにおいて、「昨日の売上」「前回のスコア」など、行をまたぐ比較（前のレコードとの差分など）を行う場合、SQLでは同じテーブルを2つ並べ、結合条件で日付やIDを -1 して「ズラして」結合するというテクニックを使います。

FROM daily_kpi AS t1
-- t2 が「t1の1つ前のデータ」になるように結合する
LEFT JOIN daily_kpi AS t2
  ON t1.day_id - 1 = t2.day_id;
問題 ▼

日々のアクセス数を記録したテーブル（daily_kpi）があります。わかりやすくするため、日付ではなく連番の day_id で管理されています。

このテーブルを自己結合して、今日（today_users）と昨日（yesterday_users）のアクセス数を横に並べて比較し、さらにその差分（diff）を計算して出力してください。
1日目など「前日が存在しない日」の yesterday_users と diff は NULL になります。

使用テーブル
▸ daily_kpi
day_idusers
1100
2120
390
期待出力 ▼
day_idtoday_usersyesterday_usersdiff
1100NULLNULL
212010020
390120-30
模範解答コード ▼ 
SELECT
  t1.day_id,
  t1.users AS today_users,
  t2.users AS yesterday_users,
  t1.users - t2.users AS diff

FROM daily_kpi AS t1
LEFT JOIN daily_kpi AS t2        -- 前日のデータが見つからなくても当日の行は残すため LEFT JOIN を使用
  ON t1.day_id - 1 = t2.day_id  -- t1(今日) から見て 1 を引いたIDが、t2(昨日) のIDと一致するように結合
ORDER BY t1.day_id;

/*
  実行順序:
  1. FROM daily_kpi AS t1       → t1（今日軸）として3行を読み込む
  2. LEFT JOIN daily_kpi AS t2  → t1の各行に対し、day_idを1つ減らした値でt2を照合
  3. SELECT ...                 → マッチした行を横に並べ、引き算(diff)を計算して出力
  */
解説（テーブル変化・ポイント） ▼
SELECT t1.day_id, t1.users AS today_users, t2.users AS yesterday_users, t1.users - t2.users AS diff FROM daily_kpi AS t1 LEFT JOIN daily_kpi AS t2 ON t1.day_id - 1 = t2.day_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM daily_kpi AS t1今日の軸となる daily_kpi テーブル（3行）を読み込みます。
1 / 4
day_idusers
1100
2120
390
全 3行 読込
学習ポイント
JOIN PATTERN
計算式によるズラし結合 (LEFT SELF JOIN)
条件を-1してレコードを意図的にズラして結合する
t1 = kpi(今日)   t2 = kpi(昨日)
「横」に並べるための JOIN：SQL は基本的に行（縦）の処理を得意としていますが、「昨日のデータ」のように別の行のデータを同じ行に並べて比較（引き算など）したい場合は、JOINを使って強制的に「横」に連結させる必要があります。この発想は集計SQLを書く上で強力な武器になります。
実際の日付型での記述：問題では整数(day_id)を使いましたが、PostgreSQL等で実際の DATE 型を使う場合は ON t1.date - INTERVAL '1 day' = t2.date のように日付関数を使ってマイナスします。
実務コラム
Top-1抽出と同じく、この「前の行の値を取得する」タスクも、モダンなSQLではウィンドウ関数（LAG()）を用いて解くのが現代の標準です。
SELECT date, users, LAG(users) OVER(ORDER BY date) AS yesterday FROM daily_kpi;
このように書けば自己結合は不要になります。ただし、歯抜けデータがある（10/2のレコード自体が存在しない）場合に LAG() だと10/1の値を引いてしまうため、正確な「前日」を引くためにはやはり 「カレンダーとのJOIN」との合わせ技が必要になります。
5 / 5
問題を選択
次のセット →