第4章 複数テーブルから必要な情報を取り出す

この章のゴール

第3章では、商品、商品分類、容器種別、取引先を別々のテーブルへ作成しました。 分けて保存したデータを業務で利用するときは、外部キーをたどって必要な名称や数量を組み合わせます。

この章を終えると、次のことができるようになります。

• 外部キーと主キーを結合条件にする
• INNER JOINで複数テーブルから必要な列を取得する
• ONの結合条件とWHEREの検索条件を区別する
• SUMとCOUNTで数量と明細行数を集計する
• GROUP BYで取引先別、商品分類別、容器種別に結果をまとめる
• 集計結果から、テーブル設計と業務条件が十分か確認する

60分のうち、約35分を講師と一緒に行う操作、約20分を演習、約5分を振り返りに使います。 SQL構文を網羅せず、第6章のAI検索にも使う結合と集計へ絞ります。

使用するデータとSQLファイル

この章は、第3章で作成したai_database_trainingを引き続き使用します。 次の状態になっていることを確認してください。

• product_categories、package_types、customers、productsがある
• 第3章の共通サンプルデータが登録されている
• 操作練習用商品PRD099は削除済みである

本章では、次のSQLファイルを上から順に使用します。

1. 04-prepare-shipment-data.sql — 結合・集計練習用の出荷明細を作成
2. 04-join-and-aggregate.sql — 結合・集計例を実行

1番のファイルは、第3章の作成用・登録用SQLを実行したデータベースへ1回だけ実行します。

この章でも、講師が指定した研修用MySQLだけを使用してください。

実在する業務データベースへ、教材のSQLを実行してはいけません。

分けて保存し、結合して利用する

商品テーブルには、容器種別名の「缶」ではなく、容器種別IDが保存されています。 名称を商品ごとに重複して保存せず、必要なときに容器種別テーブルから取得するためです。

products.package_type_id
        ＝
package_types.package_type_id

このように、関連する行を共通の値で対応付ける処理を結合と呼びます。 本章では、対応する行が両方のテーブルにある場合だけ結果へ出すINNER JOINを使用します。

図4-1 欲しい列があるテーブルまで、外部キーと主キーを順にたどります。

図4-1では、出荷明細から次の情報を取得できます。

欲しい情報	たどる経路
取引先名	出荷明細 → 取引先
商品名	出荷明細 → 商品
容器種別名	出荷明細 → 商品 → 容器種別
商品分類名	出荷明細 → 商品 → 商品分類
1ケース当たりの本数	出荷明細 → 商品

結合できるのは、テーブル間の関係と、対応するキーが設計されているためです。

結合・集計練習用の出荷明細を追加する

第3章の4テーブルだけでは、出荷予定ケース数を集計できません。 そこで本章では、1行を「一つの出荷予定に含まれる一商品」とする、次の中間テーブルを追加します。

列	意味
shipment_plan_no	出荷予定番号
line_no	出荷予定内の明細番号
shipment_date	出荷予定日
customer_id	取引先への外部キー
product_id	商品への外部キー
shipment_status	出荷予定の状態コード
case_quantity	出荷予定ケース数
updated_at	最終更新日時

テーブル名はshipment_schedule_rowsです。 shipment_plan_noとline_noの組み合わせを主キーにします。

この表では、同じ出荷予定番号の日付、取引先、状態、更新日時が明細ごとに繰り返されます。 結合と集計を学ぶための中間教材であり、完成した設計ではありません。 第5章で問題を確認し、出荷予定と出荷予定明細へ分割します。

04-prepare-shipment-data.sqlを開き、ファイル全体を1回実行してください。 最後の確認結果が7になれば、7明細が登録されています。

SELECT COUNT(*) AS shipment_row_count
FROM shipment_schedule_rows;

INNER JOINで商品と容器種別を結合する

最初に、商品と容器種別を結合します。

SELECT
    p.product_code,
    p.product_name,
    pt.package_type_name,
    p.units_per_case
FROM products AS p
INNER JOIN package_types AS pt
    ON p.package_type_id = pt.package_type_id
ORDER BY p.product_code;

テーブルへ別名を付ける

products AS pは、productsへpという短い別名を付けています。 列名の前に別名を書くと、その列がどのテーブルに属するかを明示できます。

p.product_name
│ └─ 列名
└── テーブルの別名

複数テーブルに同名の列がある場合も、別名を付ければ区別できます。 本教材では、結合するSQLでテーブルの別名を省略しません。

ONで行同士を対応付ける

ONの後ろには、どの値が一致する行を組み合わせるかを書きます。

ON p.package_type_id = pt.package_type_id

左辺は商品が持つ外部キー、右辺は容器種別の主キーです。 商品PRD001の容器種別IDが1なら、容器種別ID1の「缶」が同じ結果行へ加わります。

複数のテーブルを順に結合する

次は、2026年11月20日の確定済み明細へ、取引先名と商品名を加えます。

SELECT
    s.shipment_plan_no,
    s.line_no,
    c.customer_name,
    p.product_name,
    s.case_quantity
FROM shipment_schedule_rows AS s
INNER JOIN customers AS c
    ON s.customer_id = c.customer_id
INNER JOIN products AS p
    ON s.product_id = p.product_id
WHERE s.shipment_date = '2026-11-20'
  AND s.shipment_status = 'CONFIRMED'
ORDER BY s.shipment_plan_no, s.line_no;

結果は4行です。

出荷予定番号	明細	取引先	商品	ケース数
SP20261120-001	1	取引先A	みなとソーダ	12
SP20261120-001	2	取引先A	みなと天然水	20
SP20261120-004	1	取引先B	しおかぜ茶	10
SP20261120-004	2	取引先B	みなとソーダ	7

ONとWHEREは役割が異なります。

句	このSQLでの役割
ON	どの取引先・商品を同じ結果行へ結び付けるか
WHERE	日付と状態が業務上の対象に合う行だけを残す

結合が正しくても、日付や状態を指定しなければ業務上の答えは変わります。 AIから検索するときも、対象日と含める状態を明確にする必要があります。

SUMでケース数を合計する

「2026年11月20日の確定済み缶飲料は何ケースか」を求めます。

SELECT
    SUM(s.case_quantity) AS total_cases
FROM shipment_schedule_rows AS s
INNER JOIN products AS p
    ON s.product_id = p.product_id
INNER JOIN package_types AS pt
    ON p.package_type_id = pt.package_type_id
WHERE s.shipment_date = '2026-11-20'
  AND s.shipment_status = 'CONFIRMED'
  AND pt.package_type_code = 'CAN';

結果は29ケースです。

みなとソーダ  12ケース
しおかぜ茶    10ケース
みなとソーダ   7ケース
----------------------
合計          29ケース

SUMは、対象行にある数値を合計します。 この問いでは、日付、状態、容器種別の三条件をすべて満たす行だけが対象です。

GROUP BYで分類ごとにまとめる

合計を一つだけ出すのではなく、取引先ごとに分けて表示するときはGROUP BYを使います。

SELECT
    c.customer_code,
    c.customer_name,
    SUM(s.case_quantity) AS total_cases
FROM shipment_schedule_rows AS s
INNER JOIN customers AS c
    ON s.customer_id = c.customer_id
WHERE s.shipment_date = '2026-11-20'
  AND s.shipment_status = 'CONFIRMED'
GROUP BY
    c.customer_id,
    c.customer_code,
    c.customer_name
ORDER BY c.customer_code;

図4-2 GROUP BYで同じ取引先の行をまとめ、SUMでケース数を合計します。

結果は次のとおりです。

取引先コード	取引先名	合計ケース数
CUS001	取引先A	32
CUS002	取引先B	17

本教材では、SELECTに書いた集計対象以外の列をGROUP BYにも明示します。 どの単位で結果を一行へまとめたかを読み取りやすくするためです。

COUNTとSUMは数えているものが違う

容器種別ごとに、明細行数とケース数を並べて確認します。

SELECT
    pt.package_type_code,
    pt.package_type_name,
    COUNT(*) AS detail_row_count,
    SUM(s.case_quantity) AS total_cases
FROM shipment_schedule_rows AS s
INNER JOIN products AS p
    ON s.product_id = p.product_id
INNER JOIN package_types AS pt
    ON p.package_type_id = pt.package_type_id
WHERE s.shipment_date = '2026-11-20'
  AND s.shipment_status = 'CONFIRMED'
GROUP BY
    pt.package_type_id,
    pt.package_type_code,
    pt.package_type_name
ORDER BY pt.package_type_code;

容器コード	容器種別	明細行数	合計ケース数
CAN	缶	3	29
PET	ペットボトル	1	20

• COUNT(*)は、条件に合った明細行の数を数える
• SUM(case_quantity)は、各明細のケース数を合計する

一つの出荷予定に複数商品があるため、明細行数を「出荷予定件数」と呼んではいけません。 何を一件として数えるかは、SQLを書く前に業務の言葉で決めます。

ケース数を本数へ換算する

商品ごとのunits_per_caseを結合すれば、ケース数を本数へ換算できます。

SUM(s.case_quantity * p.units_per_case) AS total_units

本章の確定済みデータでは、容器種別ごとの結果は次のとおりです。

容器種別	合計ケース数	合計本数
缶	29	696
ペットボトル	20	480

同じ29という数値でも、ケースと本では意味が異なります。 列の別名、画面表示、AIの回答では単位を省略しないようにします。

演習3：結合・集計して業務上の問いへ答える

04-join-and-aggregate.sqlを参考に、次の問いへ答えるSQLを作成してください。 検索日はすべて2026-11-20、予定状態はCONFIRMEDだけとします。

課題1：結合結果を確認する

商品コード、商品名、商品分類名、容器種別名、1ケース当たりの本数を、商品コード順に表示してください。

確認すること：

• 4商品が表示される
• IDではなく分類名と容器種別名が表示される
• 商品ごとの入数が第3章のデータと一致する

課題2：取引先別に集計する

取引先コード、取引先名、合計ケース数を表示してください。

取引先コード	自分の結果
CUS001
CUS002

課題3：商品分類別に集計する

商品分類コード、商品分類名、合計ケース数を表示してください。 0件の分類を無理に表示する必要はありません。

課題4：AIへ渡す検索条件を説明する

「明日の缶飲料の出荷予定は何ケースですか」という質問を、SQLで検索できる条件へ言い換えてください。

条件	指定する値
基準日	2026年11月19日
対象日
予定状態
容器種別
数量の単位

演習結果を確認する

課題2

• CUS001 取引先A：32ケース
• CUS002 取引先B：17ケース

課題3

• CARBONATED 炭酸飲料：19ケース
• TEA 茶系飲料：10ケース
• WATER 水：20ケース

課題4

• 対象日：2026年11月20日
• 予定状態：確定済みを表すCONFIRMED
• 容器種別：缶を表すCAN
• 数量の単位：ケース

したがって、この教材データでの答えは29ケースです。

集計結果から設計を確認する

SQLが動くだけでは、業務で使えるデータベースとはいえません。 今回の検索を通して、設計に次の情報が必要だと分かります。

• 商品と容器種別を結ぶ外部キー
• 出荷明細と商品を結ぶ外部キー
• 「明日」を具体化できる出荷予定日
• 仮登録や取消を除外できる予定状態
• ケース数と、1ケース当たりの本数
• コードの意味を人やAIへ伝えるデータ辞書

一方、shipment_schedule_rowsには、出荷予定日、取引先、状態、更新日時が明細ごとに重複しています。 同じ出荷予定の一行だけを更新すると、同じ予定番号の中で値が矛盾する可能性があります。 この問題を第5章の正規化で解消します。

よくあるエラーと確認場所

状況	主な原因	確認すること
列名が曖昧というエラー	複数テーブルに同名列がある	p.product_idのように別名を付ける
テーブルや列が見つからない	別名、列名、使用DBが違う	USE、DESCRIBE、SQLの綴り
同じ行が予想以上に増える	ONの条件が不足している	外部キーと主キーを結んでいるか
合計が大きすぎる	日付、状態、容器の条件が不足している	WHEREを一条件ずつ確認する
集計エラーになる	非集計列とGROUP BYが対応していない	表示する分類列をGROUP BYへ書く
0行になる	条件値や状態コードが存在しない	集計前の明細表示で対象行を確認する

集計結果が予想と違うときは、いきなりSUMだけを修正しません。 まず集計前のSELECTで対象行を表示し、結合結果とWHERE条件を確認します。

この章で作成・確認したもの

• 結合・集計練習用の出荷明細7行
• 商品、取引先、商品分類、容器種別をたどる結合SQL
• 取引先別、商品分類別、容器種別の集計SQL
• 2026年11月20日の確定済み缶飲料が29ケースであること
• ケース数を本数へ換算するための計算
• AIへ質問するときに必要な日付、状態、容器、単位の条件
• 中間テーブルに残る情報重複の問題

この章のまとめ

• INNER JOINは、ONで対応する行を結び付けます。
• 外部キーと主キーをたどると、分けて保存した名称や数量を一緒に表示できます。
• WHEREは、日付、状態、容器種別など業務上の対象を絞ります。
• SUMは数量を合計し、COUNT(*)は結果の行数を数えます。
• GROUP BYは、取引先や分類など、同じ値の行を一つの集計結果へまとめます。
• 件数、数量、単位、含める状態を決めなければ、正しいSQLでも業務上の答えは定まりません。
• 結合と集計は、設計した関係とデータの意味が機能しているか確かめる手段です。

次章では、中間テーブルに残る重複と更新時の問題を確認し、第1正規形から第3正規形まで整理します。