SQL JOIN — サブクエリと結合パターンの応用

QUESTION 6

JOIN + GROUP BY 集計 — INNER JOINで結合した後にCOUNT・SUMで顧客別売上を集計する

NRO上

前提知識 ▼

JOIN と GROUP BY は「結合→グループ化→集計」という流れで組み合わせます。JOIN は行を横に広げ、GROUP BY は行を縦にまとめます。この2つを組み合わせることで、複数テーブルにまたがった集計クエリが書けます。

SELECT
  c.name,
  COUNT(o.order_id) AS order_count,  -- 注文件数を集計
  SUM(o.amount)     AS total_amount  -- 合計金額を集計
FROM       customers AS c
INNER JOIN orders    AS o ON c.customer_id = o.customer_id
GROUP BY   c.customer_id, c.name
ORDER BY   total_amount DESC;

GROUP BY に SELECT の非集計列を全て列挙：SELECT c.name のように集計関数なしで使う列は全て GROUP BY に含めます。GROUP BY c.customer_id だけでは c.name を SELECT できません（PostgreSQL・標準SQL）。

問題 ▼

customers テーブルと orders テーブルを INNER JOIN して、各顧客の注文件数（order_count）と合計金額（total_amount）を取得してください。注文のない顧客（鈴木）は除外し、合計金額の降順で並べてください。

使用テーブル

▸ customers

customer_id	name	city
1	田中	東京
2	佐藤	大阪
3	山田	東京
4	鈴木	名古屋

▸ orders

order_id	customer_id	amount
1	1	5000
2	1	3000
3	2	7000
4	3	2000

期待出力 ▼

name	order_count	total_amount
田中	2	8000
佐藤	1	7000
山田	1	2000

模範解答コード ▼

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,  -- 各顧客の注文件数
  SUM(o.amount)      AS total_amount  -- 各顧客の合計金額

FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id  -- 注文のない鈴木はINNER JOINで除外

GROUP BY c.customer_id, c.name  -- SELECT の非集計列を全て列挙

ORDER BY total_amount DESC;  -- 集計後の別名で降順ソート

/*
  実行順序:
  1. FROM customers AS c     → customers を読み込む
  2. INNER JOIN orders AS o  → ON で結合（未注文は除外）
  3. GROUP BY customer_id    → 顧客でグループ化
  4. SELECT COUNT/SUM        → 各グループで集計
  5. ORDER BY total_amount DESC   → 降順でソート
  */

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

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)      AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_amount DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM customers AS ccustomers テーブル（4行）を左テーブルとして読み込みます。鈴木（customer_id=4）は orders にマッチする行がないため、次の INNER JOIN で除外されます。

1 / 4

customer_id	name	city
1	田中	東京
2	佐藤	大阪
3	山田	東京
4	鈴木	名古屋

全 4行読込

学習ポイント

INNER JOIN + GROUP BY

結合で行を横に広げ、GROUP BYで縦にまとめ、集計関数で値を求める。

A: customers(c) / B: orders(o)

「JOIN → GROUP BY → 集計」の思考順序：SQLを書く前に「どのテーブルを結合するか（JOIN）→ 何の単位でまとめるか（GROUP BY）→ 何を計算するか（COUNT/SUM）」の順番で考えます。本問では customers ⋈ orders → 顧客別 → 注文数と合計金額、という流れです。この思考順序はほぼ全ての集計クエリに通用します。

COUNT(*) vs COUNT(列名)：COUNT(*) は NULL を含む全行を数えますが、COUNT(o.order_id) のように列名を指定するとその列が NULL でない行のみを数えます。INNER JOIN の場合は除外済みなので差はありませんが、LEFT JOIN と組み合わせるときは COUNT(列名) の方が安全です。注文ゼロ件ユーザーを 0 で表示したい場合は LEFT JOIN + COUNT(o.order_id) + COALESCE(SUM(o.amount), 0) のパターンを使います。

アンチパターン

GROUP BY に SELECT の非集計列を漏らすとエラー：SELECT c.name, c.city, COUNT(...) と書いて GROUP BY c.customer_id, c.name にすると、c.city が GROUP BY に含まれていないためエラーになります（PostgreSQL）。SELECT で集計関数なしに使う列は全て GROUP BY に含めてください。

INNER JOIN で注文ゼロ件の顧客が消える：「全顧客の注文数を表示したい」のに INNER JOIN を使うと、注文のない顧客が結果から消えます。注文ゼロ件を 0 で表示したい場合は LEFT JOIN に変え、COUNT(o.order_id) と COALESCE(SUM(o.amount), 0) を使います。要件確認が重要です。

SELECT
  c.name,
  COUNT(o.order_id)       AS order_count,
  COALESCE(SUM(o.amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

LEGEND ▼

データ取得・結合対象

① LEFT JOIN ▼

LEFT JOIN orders AS o注文が0件の鈴木（customer_id=4）を残すため、LEFT JOINを使用します。鈴木に対応する orders の列は全て NULL になります。

1 / 2

▸ customers（c）

customer_id	name
1	田中
2	佐藤
3	山田
4	鈴木

⋈

▸ orders（o）

order_id	amount
1	5000
2	3000
3	7000
4	2000
NULL	NULL

マッチ: 4行 + 鈴木（NULL）1行 = 5行

実務コラム

この「JOIN + GROUP BY 集計」パターンはダッシュボードAPIの定番です。例：① 月別売上レポート（GROUP BY DATE_TRUNC('month', ordered_at)）、② カテゴリ別商品数（GROUP BY category_id）、③ ユーザーごとのアクティビティ集計（ログテーブルをJOINしてアクション種別ごとにCOUNT）。クエリの基本形として体に染み込ませておくと実装速度が大きく上がります。

QUESTION 7

FULL OUTER JOIN — 左右どちらにしか存在しない行も全て取得し退職・在籍・入社を判定する

UOA分

前提知識 ▼

FULL OUTER JOIN（完全外部結合）は、左右のテーブルのどちらかにしか存在しない行も含めて全行を結合します。一致しない側の列は NULL で補完されます。

SELECT ...
FROM   table_a AS a
FULL OUTER JOIN table_b AS b ON a.id = b.id;
-- a.id IS NULL → B にしか存在しない行
-- b.id IS NULL → A にしか存在しない行
-- どちらも非NULL  → 両方に存在する行

COALESCE で id を統合する：FULL JOIN では左右どちらかの id が NULL になりうるため、COALESCE(a.id, b.id) で「どちらか非NULL の id」を取得するのが定番イディオムです。

MySQL は FULL OUTER JOIN 非対応：PostgreSQL・SQL Server・Oracle は標準サポート。MySQL では LEFT JOIN UNION RIGHT JOIN で代替します。

問題 ▼

2023年と2024年の従業員テーブルを FULL OUTER JOIN して、全従業員の在籍状況（退職・在籍継続・入社）を判定してください。片方の emp_id が NULL かどうかで CASE WHEN を使って分類します。

使用テーブル

▸ employees_2023（前年）

emp_id	name
1	田中
2	佐藤
3	山田

▸ employees_2024（今年）

emp_id	name
2	佐藤
3	山田
4	鈴木

期待出力 ▼

emp_id	name_2023	name_2024	status
1	田中	NULL	退職
2	佐藤	佐藤	在籍継続
3	山田	山田	在籍継続
4	NULL	鈴木	入社

模範解答コード ▼

SELECT
  COALESCE(a.emp_id, b.emp_id) AS emp_id,    -- どちらかが NULL でも ID を取得
  a.name                         AS name_2023, -- 左テーブル（前年）の名前
  b.name                         AS name_2024, -- 右テーブル（今年）の名前
  CASE
    WHEN a.emp_id IS NULL THEN '入社'      -- 前年にいない → 新入社員
    WHEN b.emp_id IS NULL THEN '退職'      -- 今年にいない → 退職
    ELSE                       '在籍継続'   -- 両方に存在 → 在籍継続
  END AS status

FROM employees_2023 AS a
FULL OUTER JOIN employees_2024 AS b
  ON a.emp_id = b.emp_id

ORDER BY COALESCE(a.emp_id, b.emp_id); -- NULL を COALESCE で統合してソート

/*
  実行順序:
  1. FROM employees_2023 AS a               → 前年従業員3行を左テーブルとして読み込む
  2. FULL OUTER JOIN employees_2024 AS b
  3. SELECT + COALESCE + CASE WHEN          → emp_id統合と在籍状況を判定
  4. ORDER BY COALESCE(a.emp_id, b.emp_id)  → emp_id 昇順でソート
  */

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

SELECT
  COALESCE(a.emp_id, b.emp_id) AS emp_id,
  a.name AS name_2023,
  b.name AS name_2024,
  CASE
    WHEN a.emp_id IS NULL THEN '入社'
    WHEN b.emp_id IS NULL THEN '退職'
    ELSE '在籍継続'
  END AS status
FROM employees_2023 AS a
FULL OUTER JOIN employees_2024 AS b
  ON a.emp_id = b.emp_id
ORDER BY COALESCE(a.emp_id, b.emp_id);

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM employees_2023 AS a前年の従業員テーブル（3行）を左テーブルとして読み込みます。FULL OUTER JOIN のため、左右どちらにしか存在しない行も最終結果に含まれます。

1 / 4

emp_id	name
1	田中
2	佐藤
3	山田

全 3行読込（左テーブル）

学習ポイント

FULL OUTER JOIN

左右どちらにしか存在しない行も含め、両テーブルの全行を結合する。

A: employees_2023(a) / B: employees_2024(b)

FULL OUTER JOIN = LEFT JOIN ∪ RIGHT JOIN：結果行数は「A ∩ B（共通）+ A のみ + B のみ」の合計です。本問では共通2行 + 田中（Aのみ）1行 + 鈴木（Bのみ）1行 = 4行となります。「2つのテーブルの差分を全方向で検出したい」ときが典型的な使い所で、データ移行の整合性チェックやバージョン間の差分比較に活躍します。

IS NULL 判定で結合種別を3分類する：FULL OUTER JOIN 後の CASE WHEN で a.emp_id IS NULL と b.emp_id IS NULL を組み合わせることで、「Aのみ存在／Bのみ存在／両方存在」の3種類に分類できます。この IS NULL を使ったパターン分類は FULL JOIN の最重要イディオムです。

アンチパターン

MySQL で FULL OUTER JOIN を書くとエラー：MySQL は FULL OUTER JOIN 非対応です。SELECT ... FROM a LEFT JOIN b ON a.id=b.id UNION ALL SELECT ... FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL で代替します。PostgreSQLでは標準サポートされています。

COALESCE なしで ORDER BY すると NULL の位置が実装依存になる：ORDER BY a.emp_id とすると、a.emp_id が NULL の行（鈴木）の位置がDB実装依存になります（PostgreSQLはNULLS LASTがデフォルト）。FULL JOIN 後のソートは COALESCE(a.id, b.id) で統合してから行うのがベストプラクティスです。

実務コラム

FULL OUTER JOIN の実務例：① データ移行後の整合性チェック（新旧テーブルを FULL JOIN して id が片方にしかない行を検出）、② マスタデータの同期漏れ検出（本番DBと開発DBのマスタ差分を FULL JOIN + CASE WHEN で可視化）、③ A/Bテストのバリアント網羅確認（A側とB側の設定テーブルを FULL JOIN して未設定項目を検出）。「2つのデータセットの差分を全方向で見たい」シーンで使います。

QUESTION 8

多対多 JOIN（M:N）— 中間テーブルを2回 INNER JOIN してユーザーとタグを結びつける

N間:グ

前提知識 ▼

Webアプリでは「1人のユーザーが複数のタグを持ち、1つのタグも複数のユーザーに付く」という多対多（M:N）関係が頻繁に登場します。RDBではこの関係を中間テーブル（ジャンクションテーブル）で表現します。

SELECT u.name, t.tag_name
FROM       users     AS u
INNER JOIN user_tags AS ut ON u.user_id = ut.user_id  -- ① users → 中間テーブル
INNER JOIN tags      AS t  ON ut.tag_id = t.tag_id;   -- ② 中間テーブル → tags

中間テーブルは「橋渡し役」：user_tags テーブルは user_id と tag_id だけを持ち、users と tags を結びつける橋の役割を果たします。2つの外部キーの組み合わせが主キーになるのが典型的な設計です。

問題 ▼

users・user_tags（中間テーブル）・tags の3テーブルを2回 INNER JOIN して、各ユーザーが持つタグ名の一覧を取得してください。user_id → tag_id の順に並べてください。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ user_tags（中間テーブル）

user_id	tag_id
1	1
1	2
2	2
2	3
3	1

▸ tags

tag_id	tag_name
1	Frontend
2	Backend
3	DevOps

期待出力 ▼

name	tag_name
田中	Frontend
田中	Backend
佐藤	Backend
佐藤	DevOps
山田	Frontend

模範解答コード ▼

SELECT
  u.name,     -- users テーブルのユーザー名
  t.tag_name  -- tags テーブルのタグ名

FROM users AS u
INNER JOIN user_tags AS ut   -- 第1 JOIN: users → user_tags（中間テーブル経由の第1段階）
  ON u.user_id = ut.user_id
INNER JOIN tags AS t         -- 第2 JOIN: user_tags → tags（中間テーブルから実テーブルへ）
  ON ut.tag_id = t.tag_id

ORDER BY u.user_id, t.tag_id; -- ユーザー順・タグ順でソート

/*
  実行順序:
  1. FROM users AS u               → users を読み込む
  2. INNER JOIN user_tags AS ut    → user_id でマッチング
  3. INNER JOIN tags AS t          → tag_id でマッチング
  4. SELECT u.name, t.tag_name     → 2列を射影
  5. ORDER BY u.user_id, t.tag_id  → ユーザー・タグ順にソート
  */

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

SELECT
  u.name,
  t.tag_name
FROM users AS u
INNER JOIN user_tags AS ut
  ON u.user_id = ut.user_id
INNER JOIN tags AS t
  ON ut.tag_id = t.tag_id
ORDER BY u.user_id, t.tag_id;

LEGEND ▼

データ取得・読込対象

① FROM users ▼

FROM users AS uusers テーブル（3行）を起点として読み込みます。3テーブル JOIN の起点は、最も「主体」となるエンティティを選ぶと読みやすくなります。

1 / 4

user_id	name
1	田中
2	佐藤
3	山田

全 3行読込

学習ポイント

INNER JOIN × 2（M:N）

中間テーブルを橋渡しに、2回 JOIN で多対多関係を解消する。

A: users → user_tags（中間） → tags :B

M:N関係を「2回のJOIN」で解消する：users と tags は直接 JOIN できません（外部キーがない）。中間テーブル user_tags を橋渡しにして「users → user_tags → tags」と2段階で JOIN することで M:N 関係を解消します。この3テーブルパターンはタグ・権限・カテゴリなどあらゆる M:N 設計で共通して使います。

中間テーブルには追加情報を持たせることもある：本問の user_tags は user_id と tag_id だけですが、created_at（タグを付けた日時）や weight（タグの重み）など、関係に付随する属性を中間テーブルに追加することがあります。これを「属性付き中間テーブル」といい、ORDER×商品の数量・単価を持つ order_items が代表例です。

アンチパターン

配列型で多対多を1カラムに詰め込む設計：users.tag_ids = '{1,2,3}' のように配列やカンマ区切り文字列で多対多を1カラムに詰め込む設計はアンチパターンです。JOIN でタグ名を取得できず、検索・集計・インデックスが全て困難になります。必ず中間テーブルで正規化してください。

中間テーブルに重複行が入る設計：中間テーブルの (user_id, tag_id) ペアに UNIQUE 制約を張らないと、同じ組み合わせが重複して入ります。JOIN 結果の行数が倍増して集計が狂います。中間テーブルには必ず (user_id, tag_id) の複合 PRIMARY KEY または UNIQUE 制約を設定してください。

SELECT u.name, ut.tag_id
FROM users AS u
INNER JOIN user_tags_dup AS ut
  ON u.user_id = ut.user_id;

LEGEND ▼

データ取得・結合対象

① 危険な重複データのJOIN ▼

INNER JOIN user_tags_dup中間テーブルに「(1, 1)」という全く同じペアが2行登録されてしまっていると、田中（user_id=1）の行が意図せず増殖してしまいます。

1 / 1

▸ users（u）

user_id	name
1	田中

⋈

▸ user_tags_dup（重複あり）

user_id	tag_id
1	1
1	1

同じ組み合わせが複数マッチしてしまう

実務コラム

M:N 中間テーブルパターンはWebアプリの至る所に登場します。例：① 記事とタグ（articles ↔ article_tags ↔ tags）、② ユーザーと権限/ロール（users ↔ user_roles ↔ roles）、③ 注文と商品（orders ↔ order_items ↔ products・数量など属性付き）、④ コースと受講生（courses ↔ enrollments ↔ students）。このパターンを見た瞬間に「3テーブル2回JOIN」と判断できるようにすると実装速度が大幅に上がります。

QUESTION 9

CTE（WITH句）+ JOIN — 商品別売上集計をCTEで仮想テーブル化してからJOINで詳細を付与する

INRエ

前提知識 ▼

CTE（Common Table Expression、WITH句）は、複雑なサブクエリに名前を付けて「仮想テーブル」として再利用できる機能です。クエリを複数のステップに分解して書けるため、可読性と保守性が大幅に向上します。

WITH cte_name AS (
  -- サブクエリ（集計・フィルタ等）
  SELECT col1, SUM(col2) AS total
  FROM some_table
  GROUP BY col1
)
SELECT m.name, c.total
FROM       master_table AS m
INNER JOIN cte_name      AS c ON m.id = c.col1;

CTEは「一時的な名前付きサブクエリ」：FROM句のインラインサブクエリをCTEで書き直すと読みやすくなります。同じCTEを複数回参照でき、複雑な集計クエリの整理に有効です（PostgreSQL・MySQL 8+・SQL Server等で対応）。

問題 ▼

order_items テーブルを CTE で商品別に集計し（合計数量・注文件数）、products テーブルと INNER JOIN して商品名・合計数量・注文件数・合計売上金額（price × total_qty）を取得してください。合計売上金額の降順で並べてください。

使用テーブル

▸ products

product_id	name	price
1	ノートPC	80000
2	マウス	3000
3	キーボード	8000

▸ order_items

item_id	product_id	quantity
1	1	2
2	2	5
3	2	3
4	3	1
5	1	1

期待出力 ▼

name	total_qty	order_count	total_revenue
ノートPC	3	2	240000
マウス	8	2	24000
キーボード	1	1	8000

模範解答コード ▼

-- CTE: order_items を商品別に集計して仮想テーブルを作る
WITH sales_summary AS (
  SELECT
    product_id,
    SUM(quantity)  AS total_qty,   -- 合計販売数量
    COUNT(*)       AS order_count  -- 注文件数（行数）
  FROM order_items
  GROUP BY product_id              -- 商品別に集計
)

-- 本体クエリ: CTEと products を JOIN して詳細を付与
SELECT
  p.name,
  ss.total_qty,
  ss.order_count,
  p.price * ss.total_qty AS total_revenue  -- 単価 × 合計数 = 合計売上

FROM products AS p
INNER JOIN sales_summary AS ss     -- CTEを通常テーブルと同じようにJOIN
  ON p.product_id = ss.product_id

ORDER BY total_revenue DESC;  -- 合計売上の降順で出力

/*
  実行順序:
  1. WITH sales_summary AS (...)        → order_items 5行を product_id でGROUP BY
  2. FROM products AS p                 → products の3行を読み込む
  3. INNER JOIN sales_summary AS ss     → ON product_id でマッチング（全3行マッチ）
  4. SELECT ... p.price * ss.total_qty  → 各行で合計売上を計算
  5. ORDER BY total_revenue DESC        → 降順ソート
  */

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

WITH sales_summary AS (
  SELECT
    product_id,
    SUM(quantity) AS total_qty,
    COUNT(*) AS order_count
  FROM order_items
  GROUP BY product_id
)
SELECT
  p.name, ss.total_qty,
  ss.order_count,
  p.price * ss.total_qty AS total_revenue
FROM products AS p
INNER JOIN sales_summary AS ss
  ON p.product_id = ss.product_id
ORDER BY total_revenue DESC;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① WITH CTE: GROUP BY ▼

WITH sales_summary AS (SELECT ... FROM order_items GROUP BY product_id)order_items（5行）を product_id でグループ化します。同じ色の行が同一商品グループです。この各グループに対して SUM と COUNT が適用されます。

1 / 4

item_id	product_id	quantity
1	1	2
5	1	1
2	2	5
3	2	3
4	3	1

5行 → GROUP BY → 3グループ

学習ポイント

WITH（CTE）+ INNER JOIN

CTEで集計を仮想テーブル化し、本体クエリでJOINして詳細情報を付与する。

A: products / CTE: sales_summary

CTE vs サブクエリ — どちらを使うか：CTEとインラインサブクエリは同じ結果を返しますが、クエリが複数のステップに分かれる場合はCTEの方が可読性が高くなります。特に「集計してからJOINする」パターンでは、サブクエリをFROMの中に書くと1つのクエリがネストして深くなります。CTEは「まず集計する→次にJOINする」と段階的に記述できるため意図が明確になります。

複数のCTEを連鎖させる：WITH cte1 AS (...), cte2 AS (...) SELECT ... のようにカンマで区切って複数のCTEを定義し、後のCTEで前のCTEを参照することができます。複雑なレポートクエリを「ステップ1→ステップ2→ステップ3」と段階的に書けるのがCTEの最大の強みです。

アンチパターン

単純な処理にCTEを使うと冗長になる：WITH a AS (SELECT * FROM table) のような自明なCTEは冗長です。CTEを使うべきは「集計・フィルタなど前処理が必要」「同じサブクエリを複数回参照する」場合に限ります。1段階の単純なJOINにCTEは不要です。

PostgreSQL v12以前のCTEは最適化バリア：PostgreSQL 12以前ではCTEが「最適化バリア」として扱われ、本体クエリのWHERE条件がCTE内部に押し込まれませんでした（実行計画が非効率になりうる）。パフォーマンスが重要な場合は EXPLAIN ANALYZE で実行計画を確認してください。

実務コラム

CTEはバックエンドのレポートAPIで特に活躍します。例：① 月別KPIダッシュボード（CTEで月別集計→JOINで目標値テーブルと結合→達成率を計算）、② ユーザーランキング（CTEでポイント集計→JOINでユーザー情報付与→RANK()で順位付け）、③ 在庫アラート（CTEで商品別残数集計→JOINで閾値テーブルと比較）。複数ステップの処理を読みやすく書きたい場面で積極的に活用してください。

QUESTION 10

ROW_NUMBER() + サブクエリ JOIN — ウィンドウ関数でグループ内に連番を付け最新行1件だけ取得する

OAブ新

前提知識 ▼

ウィンドウ関数 ROW_NUMBER() はグループ（PARTITION）ごとに行を並べて連番を振ります。この連番を使ってグループ内の特定行（最新1件・最大値など）のみをフィルタする手法は、実務で最も頻繁に登場する上級パターンの一つです。

ROW_NUMBER() OVER (
  PARTITION BY 列名    -- グループを定義（GROUP BY と異なり行を保持）
  ORDER BY     列名 DESC -- グループ内での並び順を定義
) AS rn              -- 最新・最大の行が rn=1 になる

ウィンドウ関数は GROUP BY と異なり行を消さない：GROUP BY は複数行を1行に集約しますが、ROW_NUMBER() は各行に連番を付けるだけで行数を変えません。PARTITION BY でグループを定義しても全行が保持され、グループ内での順位が rn として各行に付与されます。

WHERE でウィンドウ関数は直接使えない：WHERE ROW_NUMBER() OVER (...) = 1 と書くとエラーになります。ウィンドウ関数はサブクエリ（またはCTE）の中で計算し、外側のWHEREやJOIN ONでrnをフィルタするのが正しいパターンです。

問題 ▼

orders テーブルから各ユーザーの最新注文（ordered_at が最も新しい注文）を1件だけ取得して、users テーブルと INNER JOIN してユーザー名を付与してください。ウィンドウ関数でユーザー内の注文に新しい順の番号を付け、先頭行だけを抽出します。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ orders

order_id	user_id	amount	ordered_at
1	1	5000	2024-01-10
2	1	8000	2024-02-15
3	2	3000	2024-01-20
4	2	6000	2024-03-01
5	3	4000	2024-02-05

期待出力 ▼

name	order_id	amount	ordered_at
佐藤	4	6000	2024-03-01
田中	2	8000	2024-02-15
山田	5	4000	2024-02-05

模範解答コード ▼

SELECT
  u.name,
  o.order_id,
  o.amount,
  o.ordered_at

FROM users AS u
INNER JOIN (

  -- サブクエリ: orders の各行に user_id 内での ordered_at 降順の連番を付与
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id        -- user_id ごとにグループを作る
      ORDER BY     ordered_at DESC -- 新しい注文ほど小さい rn が付く
    ) AS rn                        -- 最新行 = rn = 1
  FROM orders

) AS o
  ON  u.user_id = o.user_id
  AND o.rn      = 1          -- ON句で rn=1（最新1件）のみを結合

ORDER BY o.ordered_at DESC;  -- 注文日の降順でソート

/*
  実行順序:
  1. サブクエリ ROW_NUMBER()                   → ユーザーごとに最新順で番号付け
  2. FROM users AS u                      → users を読み込む
  3. INNER JOIN (subquery) AS o           → user_id 一致 AND rn=1 でマッチング
  4. SELECT u.name, o.order_id, o.amount  → 列を射影
  5. ORDER BY ordered_at DESC             → 最新注文日の降順でソート
  */

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

SELECT
  u.name, o.order_id,
  o.amount, o.ordered_at
FROM users AS u
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY ordered_at DESC
  ) AS rn
  FROM orders
) AS o
  ON  u.user_id = o.user_id
  AND o.rn = 1
ORDER BY o.ordered_at DESC;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① ROW_NUMBER() OVER PARTITION BY ▼

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC)orders の全行（5行）を user_id でグループ化（同じ色＝同じユーザー）し、各グループ内で ordered_at 降順に連番（rn）を付与します。最新注文が rn=1 になります。GROUP BY と異なり行数は変わりません。

1 / 4

order_id	user_id	amount	ordered_at	rn
2	1	8000	2024-02-15	1
1	1	5000	2024-01-10	2
4	2	6000	2024-03-01	1
3	2	3000	2024-01-20	2
5	3	4000	2024-02-05	1

5行のまま保持（GROUP BYと異なり行は消えない） / rn=1 が各グループの最新行

学習ポイント

ROW_NUMBER() + INNER JOIN

ウィンドウ関数でグループ内に連番を付け、rn=1 でフィルタしてから JOIN する。

A: users / sub: orders（ROW_NUMBER付き）

ROW_NUMBER vs RANK vs DENSE_RANK：ROW_NUMBER() は常に重複なし連番（同値でも別番号）を付けます。RANK() は同値に同じ番号を付けて次の番号をスキップ（1,1,3）、DENSE_RANK() は同値に同じ番号を付けてスキップなし（1,1,2）。「重複なしで必ず1件だけ取りたい」場合は ROW_NUMBER が最適です。同点1位を複数取りたい場合は RANK を使います。

SELECT
  order_id, ordered_at,
  ROW_NUMBER() OVER (ORDER BY ordered_at DESC) AS rn,
  RANK()       OVER (ORDER BY ordered_at DESC) AS rnk
FROM orders_dup;

LEGEND ▼

データ取得・読込対象

① FROM orders_dup ▼

FROM orders_dup ORDER BY ordered_at DESC同一日時（2024-02-15）の注文が2件含まれる orders_dup を ordered_at の降順で読み込みます。この「同値の2行」が ROW_NUMBER() と RANK() で挙動が分かれるポイントになります。

1 / 2

order_id	ordered_at
6	2024-02-15
2	2024-02-15
5	2024-02-05
1	2024-01-10

全 4行読込（2024-02-15 が重複）

CTEで書き直すとより読みやすい：本問のインラインサブクエリはCTEで書き直せます。

WITH ranked_orders AS (SELECT *, ROW_NUMBER()... FROM orders) SELECT ... FROM users INNER JOIN ranked_orders AS o ON ... AND o.rn = 1

。どちらも同じ結果ですが、CTEの方がサブクエリの意図が名前で明示されて読みやすいです。

アンチパターン

WHERE で ROW_NUMBER() を直接使うとエラー：WHERE ROW_NUMBER() OVER (...) = 1 はSQLの評価順序上エラーになります（ウィンドウ関数はSELECT句で評価されるため、WHERE句では未評価）。必ずサブクエリかCTEで先にrnを計算し、外側でフィルタしてください。

MAX(created_at) + JOIN よりROW_NUMBERの方が堅牢：WHERE ordered_at = (SELECT MAX(ordered_at) FROM orders WHERE user_id = u.user_id) でも同じ結果が得られますが、同一日時の注文が複数ある場合は複数行が返ります。ROW_NUMBER() + rn=1 は「必ず1件だけ」を保証するため、より堅牢です。

実務コラム

「各グループの最新/最大1件を取得する」パターンはWebAPIの最頻出シナリオの一つです。例：① 各ユーザーの最終ログイン情報（PARTITION BY user_id ORDER BY logged_at DESC）、② 各商品の最新価格履歴（PARTITION BY product_id ORDER BY changed_at DESC）、③ 各デバイスの直近のセンサー値（PARTITION BY device_id ORDER BY measured_at DESC）。このパターンをマスターすると、「最新行1件」を取る多くの実務クエリを高速・安全に実装できます。