SQL Quiz Book

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

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

INNER JOIN — 共通キーで2テーブルを結合し、両方に存在する行だけを取得する

INNER JOINON句基礎積集合
前提知識 ▼

JOIN とは、共通のキー列（例: user_id）をもとに2つのテーブルを横方向に結合する操作です。正規化されたDBでは「ユーザー情報は users テーブル」「注文情報は orders テーブル」と分割して保存するため、関連データを一緒に取得するには JOIN が必要です。

INNER JOIN は最もシンプルなJOINで、ON句で指定したキーが両テーブルに存在する行のみを結果に含めます。片方のテーブルにしかないキーの行は除外されます（積集合）。

SELECT 列名, ...
FROM   左テーブル AS エイリアス
INNER JOIN 右テーブル AS エイリアス
  ON 左テーブル.キー列 = 右テーブル.キー列;
テーブルエイリアス（AS o / AS u）： AS o のように短縮名を付けると o.user_id と書け、「ordersテーブルの user_id」と明示できます。特に同名の列が複数テーブルにある場合はエイリアス修飾が必須です。
問題 ▼

orders（注文）テーブルと users（ユーザー）テーブルを user_id で結合し、order_id・ユーザー名（name）・amount（注文金額）を取得してください。

※ orders の user_id = 4 は users テーブルに存在しません。また users の user_id = 3（山田）は注文がありません。

使用テーブル
▸ orders
order_iduser_idamount
113000
225000
341200
▸ users
user_idname
1田中
2佐藤
3山田
期待出力 ▼
order_idnameamount
1田中3000
2佐藤5000
模範解答コード ▼ 
SELECT
  o.order_id,
  u.name,      -- u. プレフィックスで「usersテーブルのname」と明示する
  o.amount

FROM orders AS o         -- orders を起点（左テーブル）にする
INNER JOIN users AS u   -- users と結合（右テーブル）
  ON o.user_id = u.user_id  -- 照合キー: 両テーブルの user_id が一致する行のみ結合
ORDER BY o.order_id;

/*
  実行順序:
  1. FROM orders AS o        → orders テーブル
  2. INNER JOIN users AS u   → orders の各行に対し ON 条件を満たす users の行を照合・結合
  3. SELECT o.order_id, ...  → 結合された仮想テーブル
  */
解説（テーブル変化・ポイント） ▼
SELECT o.order_id, u.name, o.amount FROM orders AS o INNER JOIN users AS u ON o.user_id = u.user_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM orders AS oorders テーブル（3行）を読み込みます。user_id 列が JOIN のキーになります。この後、各行に対して users テーブルとの照合が行われます。
1 / 3
order_iduser_idamount
113000
225000
341200
全 3行 読込
学習ポイント
JOIN PATTERN
INNER JOIN — 積集合
両テーブルにキーが存在する行のみを返す
o = orders   u = users
INNER JOIN の本質は「積集合」：ON句で指定したキーが両テーブルに存在する行だけが結果に含まれます。本問では orders の user_id=4 は users に存在せず、users の user_id=3（山田）は orders に存在しないため、どちらも除外されます。「データが揃ってい る行だけを使う」という意図の場合に INNER JOIN が適切です。
テーブルエイリアスとドット記法：AS o でエイリアスを付けると o.user_id と書けます。本問のように両テーブルに user_id という同名列がある場合、修飾なしの SELECT user_id は「どちらの user_id か」が曖昧としてエラーになります（ambiguous column）。複数テーブルをJOINする際は常にエイリアス修飾を書く習慣をつけましょう。
アンチパターン
ON句を書き忘れると CROSS JOIN（直積）になる：INNER JOIN users と書いて ON句を省略すると、左テーブルの全行 × 右テーブルの全行のすべての組み合わせが出力されます。本問なら3行 × 3行 = 9行が出力される重大な事故です。実行前に必ず ON句の確認を。
古い結合構文（WHERE句JOIN）は使わない：FROM orders, users WHERE orders.user_id = users.user_id という書き方はSQL-89の古い構文です。JOIN条件とフィルタ条件が同じWHEREに混在して可読性が下がるため、現代のSQL（SQL-92以降）では INNER JOIN ... ON 構文を使うのが標準です。
実務コラム：正規化とJOINの関係
リレーショナルDBでは正規化の原則に従い、ユーザー情報・注文情報・商品情報はそれぞれ別テーブルに分割して保存されます。JOINはその分割されたデータを取得時に再結合するメカニズムです。実務のSQLの大半は何らかのJOINを含んでおり、「JOINを読む・書く」スキルはバックエンド・データ分析の両方で必須です。
1 / 5
問題を選択
QUESTION 2

LEFT JOIN — 左テーブルの全行を保持し、右テーブルにマッチしない行はNULLで補完する

LEFT JOINNULL補完マスタ結合外部結合
前提知識 ▼

LEFT JOIN（左外部結合）は INNER JOIN と違い、左テーブル（FROM 側）の全行を必ず結果に含めます。右テーブルに ON 条件でマッチする行がない場合、右テーブルの全列が NULL で補完されて出力されます。

SELECT 列名, ...
FROM   左テーブル AS u   -- 全行が必ず出力される
LEFT JOIN 右テーブル AS o
  ON u.key = o.key;       -- マッチしない場合、o.* は NULL
INNER JOIN vs LEFT JOIN の選択： 「注文をしたユーザーだけを取得したい」→ INNER JOIN。「全ユーザーを取得し、注文がなければNULLで表示したい」→ LEFT JOIN。マスタテーブル（users）に対して明細テーブル（orders）をJOINするケースではLEFT JOINが好まれます。
問題 ▼

users テーブルの全ユーザーと、各ユーザーの注文情報（order_id・amount）を取得してください。注文が1件もないユーザーは order_id と amount を NULL で表示します。

※ 山田（user_id=3）は注文がありません。また、田中（user_id=1）は注文が2件あります。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
▸ orders
order_iduser_idamount
113000
211800
325000
期待出力 ▼
user_idnameorder_idamount
1田中13000
1田中21800
2佐藤35000
3山田NULLNULL
模範解答コード ▼ 
SELECT
  u.user_id,
  u.name,
  o.order_id,  -- 山田（user_id=3）はordersにマッチしないためこれらの列はNULLになる
  o.amount

FROM users AS u            -- 全ユーザー（左テーブル）を保持
LEFT JOIN orders AS o    -- ordersはマッチする行のみ結合、なければNULL補完
  ON u.user_id = o.user_id
ORDER BY u.user_id, o.order_id NULLS LAST;

/*
  実行順序:
  1. FROM users AS u        → 左テーブルを読み込む
  2. LEFT JOIN orders AS o  → ON で照合（未マッチはNULL）
  3. SELECT ...             → 列を射影して出力
  */
解説（テーブル変化・ポイント） ▼
SELECT u.user_id, u.name, o.order_id, o.amount FROM users AS u LEFT JOIN orders AS o ON u.user_id = o.user_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。LEFT JOIN では、このテーブルの全行が結果に必ず含まれます。
1 / 3
user_idname
1田中
2佐藤
3山田
全 3行 読込（全行が保持される）
学習ポイント
JOIN PATTERN
LEFT JOIN — 左外部結合
左テーブルの全行を保持、右はNULL補完
u = users（左）  o = orders（右）
LEFT JOIN の「左テーブル保持」：FROM に書いたテーブル（左テーブル）の全行が必ず結果に含まれます。右テーブル（JOIN 側）にマッチする行がない場合、右テーブルの全列が NULL で補完されます。田中（user_id=1）は orders に2件あるため2行に展開され、山田（user_id=3）は NULL補完で1行になります。
NULL はデータが「存在しない」ことを表す：山田の行で order_id = NULL は「0件」や「空文字」ではなく、orders テーブルに対応する行が存在しないことを示します。NULL に対して IS NULL / IS NOT NULL で条件をかけることができ、これが次問（Q3）の差集合パターンへつながります。
アンチパターン
LEFT JOIN 後に WHERE 句で右テーブルの列を絞ると INNER JOIN と同じ結果になる：LEFT JOIN orders ON ... WHERE o.amount > 0 のように RIGHT テーブルの列に条件をつけると、NULL はどの比較でも FALSE になるため NULL 行（山田）が除外されます。RIGHT テーブルへの条件は ON 句に書くか、意図が INNER JOIN なら INNER JOIN に変えましょう。
実務コラム：LEFT JOINが活きる要件
「会員一覧に最新の注文日を表示したい」「全商品のうち今月売れた件数を表示し、売れていない商品は0を表示したい」という要件には LEFT JOIN が典型的な解法です。RIGHT JOIN（右外部結合）も存在しますが、FROM と JOIN の順序を入れ替えれば LEFT JOIN に書き換えられるため、可読性のために常に LEFT JOIN に統一するプロジェクトがほとんどです。
2 / 5
問題を選択
QUESTION 3

LEFT JOIN + IS NULL — 一度も注文していないユーザーを「差集合」で抽出する

LEFT JOINIS NULL差集合アンチJOIN
前提知識 ▼

LEFT JOIN の特性（右テーブルにマッチしない行はNULL補完）を応用すると、「左テーブルにあって右テーブルにないレコード」、つまり差集合を取得できます。

FROM   左テーブル AS u
LEFT JOIN 右テーブル AS o ON u.key = o.key
WHERE  o.key IS NULL  -- NULLになった行 = 右テーブルにマッチしなかった行だけ残す
NOT IN との違い（重要）： WHERE u.user_id NOT IN (SELECT user_id FROM orders) でも同じ結果になりますが、サブクエリが返すリストに NULL が1つでも含まれていると全件が空になるバグがあります（NULL との IN は全て UNKNOWN）。LEFT JOIN + IS NULL はこのバグがなく、大規模テーブルでも結合インデックスを使いやすいため実務で推奨されます。
問題 ▼

users テーブルのうち、orders テーブルに一件も注文が存在しないユーザー（user_id と name）を取得してください。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
4鈴木
▸ orders
order_iduser_id
11
22
31
期待出力 ▼
user_idname
3山田
4鈴木
模範解答コード ▼ 
SELECT
  u.user_id,
  u.name

FROM users AS u
LEFT JOIN orders AS o       -- まず全ユーザーと orders を LEFT JOIN する
  ON u.user_id = o.user_id  -- orders にマッチしないユーザーは o.* が全て NULL になる
WHERE o.order_id IS NULL   -- NULL の行だけを残す = 「一度も注文していない」ユーザー
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users AS u           → users を読み込む
  2. LEFT JOIN orders AS o     → ON で照合・NULL 補完
  3. WHERE o.order_id IS NULL  → 未マッチ行だけ通過
  4. SELECT u.user_id, u.name  → 2列を射影
  */
解説（テーブル変化・ポイント） ▼
SELECT u.user_id, u.name FROM users AS u LEFT JOIN orders AS o ON u.user_id = o.user_id WHERE o.order_id IS NULL;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（4行）を左テーブルとして読み込みます。
1 / 4
user_idname
1田中
2佐藤
3山田
4鈴木
全 4行 読込
学習ポイント
JOIN PATTERN
LEFT JOIN + IS NULL — 差集合（アンチJOIN）
左専有行のみ取得（右テーブルに存在しない行）
u = users（左）  o = orders（右）
「アンチJOIN」パターン — LEFT JOIN + IS NULL：LEFT JOIN の結果、右テーブルの列が NULL になっている行だけを WHERE でフィルタすることで、左テーブルにあって右テーブルにないレコード（差集合）を取得できます。「未購入ユーザー」「未回答の設問」「未払いの請求」など"〜していない"という否定条件の抽出で頻繁に使われます。
NOT IN との違い：WHERE u.user_id NOT IN (SELECT user_id FROM orders) でも同じ結果になりますが、サブクエリが返すリストに NULL が1つでも含まれると全件が空になるバグがあります（NULL との IN 比較は全て UNKNOWN となりフィルタを通過できない）。LEFT JOIN + IS NULL はこのバグがなく、インデックスも活用しやすいため実務で推奨されるパターンです。
アンチパターン
JOIN キー以外の列で IS NULL 判定する：WHERE o.amount IS NULL のように JOIN キー以外の列で判定すると、「orders テーブルに amount = NULL で保存されている行」も誤ってマッチします。差集合パターンでは必ずJOIN キーの列（本問では o.order_id や o.user_id）で IS NULL 判定するのが正確です。
3 / 5
問題を選択
QUESTION 4

3テーブルJOIN — order_items / orders / users を連鎖JOINで結合して注文明細を取得する

INNER JOIN多段結合仮想テーブルECサイト定番
前提知識 ▼

3つ以上のテーブルをJOINするには、JOINを連鎖させます。2回目のJOINは1回目のJOIN結果（仮想テーブル）に対して適用されます。

FROM   order_items AS oi
INNER JOIN orders  AS o  ON oi.order_id  = o.order_id  -- ① 1回目
INNER JOIN users   AS u  ON o.user_id   = u.user_id;  -- ② ①の仮想テーブルに追加JOIN
エイリアスの命名慣習： テーブル名の頭文字（order_items → oi、orders → o、users → u）をエイリアスにするのが広く採用されている慣習です。複数JOINを含む長いSQLでは、エイリアスがないとどの列がどのテーブル由来か即座に判断できなくなります。
問題 ▼

ECサイトの注文明細（order_items）テーブルから、注文したユーザー名（user_name）・商品名（product_name）・数量（qty）を取得してください。

order_items → orders → users の順に INNER JOIN して結合します。

使用テーブル
▸ order_items
item_idorder_idproduct_nameqty
11ノートPC2
21マウス1
32ノートPC3
▸ orders
order_iduser_id
11
22
▸ users
user_idname
1田中
2佐藤
期待出力 ▼
user_nameproduct_nameqty
田中ノートPC2
田中マウス1
佐藤ノートPC3
模範解答コード ▼ 
SELECT
  u.name        AS user_name,  -- users テーブルの name 列
  oi.product_name,             -- order_items テーブルの商品名
  oi.qty                       -- order_items テーブルの数量

FROM order_items AS oi         -- 注文明細を起点
INNER JOIN orders AS o         -- ① 注文明細と注文を order_id で結合
  ON oi.order_id = o.order_id
INNER JOIN users  AS u         -- ② ①の仮想テーブルに user_id でユーザーを結合
  ON o.user_id   = u.user_id
ORDER BY oi.item_id;

/*
  実行順序:
  1. FROM order_items AS oi      → 注文明細3行を読み込む
  2. INNER JOIN orders AS o      → oi.order_id と o.order_id を照合・結合（3行保持）
     中間テーブル: order_items + orders の情報が合流し、user_id 列が加わる
  3. INNER JOIN users AS u       → o.user_id と u.user_id を照合・結合（3行保持）
     中間テーブル: さらに u.name が加わる（全テーブルの情報が揃う）
  4. SELECT u.name, oi.product_name, oi.qty  → 必要な3列を射影して出力
*/
解説（テーブル変化・ポイント） ▼
SELECT u.name AS user_name, oi.product_name, oi.qty FROM order_items AS oi INNER JOIN orders AS o ON oi.order_id = o.order_id INNER JOIN users AS u ON o.user_id = u.user_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM order_items AS oi注文明細テーブル（3行）を起点として読み込みます。order_id が次のJOINのキーになります。
1 / 4
item_idorder_idproduct_nameqty
11ノートPC2
21マウス1
32ノートPC3
全 3行 読込
学習ポイント
JOIN PATTERN
3テーブル連鎖 INNER JOIN
JOINを連鎖して3テーブルを結合（全マッチ行のみ）
oi = order_items   o = orders   u = users
JOINの連鎖 — 仮想テーブルへの追加JOIN：2回目の INNER JOIN users は、order_items と orders を結合した仮想テーブルに対してさらに JOIN を実行します。3つでも4つでも「前のJOIN結果に次のテーブルをJOINする」という積み上げ方式は変わりません。最終的な仮想テーブルには全テーブルのカラムが揃い、SELECT 句でその中から必要な列だけを取り出します。
エイリアスの命名規則：テーブル名の頭文字（order_items → oi、orders → o、users → u）をエイリアスに使うのが広く採用されている慣習です。複数JOINを含む長いSQLでは、エイリアスがないとどの列がどのテーブル由来かが即座にわからなくなります。SELECT name よりも SELECT u.name の方が、コードレビュー時に一目でわかります。
アンチパターン
ON句の書き忘れによるデータ爆発：3テーブルJOINで1つのON句を書き忘れると、その2テーブルが CROSS JOIN（直積）になり行数が掛け算で爆発します。本問なら3行 × 2行 × 2行 = 12行になります。実行計画（EXPLAIN）で確認する習慣や、JOIN を1つずつ積み上げながら SELECT COUNT(*) で行数チェックするデバッグ法が有効です。
実務コラム：多段JOINとAPI設計
ECサイトのバックエンドでは「注文一覧画面に顧客名・商品名・金額を表示する」APIは必ず複数テーブルのJOINを含みます。order_items → orders → users → products と4テーブルをJOINするクエリは実務の定番パターンです。JOINしたどのテーブルの列も エイリアス.列名 でアクセスでき、最終的なSELECT句で必要な列だけを選んで出力します。
4 / 5
問題を選択
QUESTION 5

JOIN + GROUP BY — JOINで仮想テーブルを作り、ユーザー別の注文件数・合計金額を集計する

INNER JOINGROUP BYCOUNT / SUM集計API定番
前提知識 ▼

JOIN と GROUP BY を組み合わせると、複数テーブルのデータを横断した集計クエリが書けます。SQL は FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY の順で評価されます。つまり JOIN で仮想テーブルを作ってから GROUP BY で集計するため、異なるテーブルの列を自由に集計キーや集計対象にできます。

FROM   users AS u
INNER JOIN orders AS o ON u.user_id = o.user_id
-- JOINした仮想テーブルを GROUP BY で集計する
GROUP BY u.user_id, u.name
GROUP BY には SELECT の非集計列を全列書く： SELECT 句に集計関数（COUNT/SUM）を含む場合、集計関数を使っていない列はすべて GROUP BY に列挙する必要があります。書き忘れると PostgreSQL や MySQL（strict mode）ではエラーになります。
問題 ▼

users テーブルと orders テーブルを結合し、ユーザーごとの注文件数（order_count）と合計金額（total_amount）を集計してください。出力は合計金額の多い順（降順）で並べてください。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
▸ orders
order_iduser_idamount
113000
211500
325000
432000
53800
期待出力 ▼
nameorder_counttotal_amount
佐藤15000
田中24500
山田22800
模範解答コード ▼ 
SELECT
  u.name,
  COUNT(o.order_id)  AS order_count,  -- NULL以外の値を数える（o.order_id は各注文に必ず存在する）
  SUM(o.amount)      AS total_amount  -- 注文金額の合計

FROM users AS u
INNER JOIN orders AS o  -- 注文のないユーザーはここで除外される（INNER JOINのため）
  ON u.user_id = o.user_id
GROUP BY u.user_id, u.name  -- SELECT の非集計列（u.name）を全部 GROUP BY に書く
ORDER BY total_amount DESC; -- 集計後の列名で並べ替える

/*
  実行順序:
  1. FROM users AS u           → users の3行を読み込む
  2. INNER JOIN orders AS o    → orders と結合（5行の仮想テーブル）
  3. GROUP BY u.user_id        → ユーザーごとに3グループに分類
  4. COUNT / SUM               → 各グループ内で注文件数・合計金額を集計
  5. SELECT u.name, ...        → 集計結果の列を射影
  6. ORDER BY total_amount DESC → 合計金額の降順でソート
*/
解説（テーブル変化・ポイント） ▼
SELECT u.name, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_amount FROM users AS u INNER JOIN orders AS o ON u.user_id = o.user_id GROUP BY u.user_id, u.name ORDER BY total_amount DESC;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。
1 / 4
user_idname
1田中
2佐藤
3山田
全 3行 読込
学習ポイント
JOIN PATTERN
INNER JOIN → GROUP BY — 積集合 + 集計
JOIN後の仮想テーブルをグループ集計する
u = users（左）  o = orders（右）
JOIN → GROUP BY の実行順序：SQL は FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY の順に評価されます。JOIN で仮想テーブルを作ってから GROUP BY で集計するため、異なるテーブルの列を自由に集計キーや集計対象にできます。本問では u.name（users の列）でグループ化しながら o.amount（orders の列）を集計しています。
GROUP BY には SELECT の非集計列を全列書く：SELECT u.name, COUNT(...) と書いた場合、u.name は集計関数を使っていない「非集計列」です。PostgreSQL・MySQL（strict mode）では SELECT 句の非集計列はすべて GROUP BY 句に含めなければエラーになります。本問では u.user_id でグループ化すれば u.name も一意に決まりますが、明示的に両方書くのが安全です。
アンチパターン
INNER JOIN を使うと注文0件ユーザーが消える：本問は INNER JOIN のため、orders に注文が1件もないユーザーは結果から除外されます。「注文数0件のユーザーも0として一覧に含めたい」場合は LEFT JOIN を使い、COUNT(o.order_id)（NULL をカウントしない）とすることで、注文ゼロのユーザーは order_count = 0 として出力されます。
実務コラム：集計APIとJOIN＋GROUP BY
ダッシュボードやレポートで「ユーザーごとの購入金額TOP10」「部門ごとの売上集計」などを表示するAPIは、ほぼ必ず JOIN + GROUP BY + ORDER BY LIMIT の組み合わせです。このパターンを習得すると、フロントエンドから「ユーザー別の集計データをJSONで返してほしい」という要件をそのままSQLに落とし込めるようになります。Q6〜Q10では、JOINとCASE式の組み合わせ・HAVING句・ウィンドウ関数など、さらに実務的なパターンを扱います。
5 / 5
問題を選択
次のセット →