SQLの内部結合（INNER JOIN）を使ってみよう！

2023.04.27 株式会社システムインテグレータ

今回は初心者訓練所#08として、前回紹介した「テーブル結合」の中でも一番利用される「内部結合」について実践的な問題を通して説明していきます。問題は2問用意してあり、それぞれ「内部結合」を使用した解答例について解説を行います。「内部結合」をしっかり覚えて、SQLの武器を身に付け、ビックデータに立向かいましょう。

（１問目）受注商品の一覧表示

1問目は、ER図にある、商品マスタ、倉庫マスタ、受注データ、受注明細データの4つのテーブルを利用した内部結合になります。

【問題】

2023年3月に取引があった受注の商品一覧を表示しなさい。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)
・WH_CODE → W_CD
・WH_NAME → W_NM
・ITEM_CODE → I_CD
・ITEM_NAME → I_NM
・ORDER_NO → OD_NO
・QUANTITY → OD_QT

表示順
１．倉庫コードの昇順
２．商品コードの昇順
３．受注数量の降順

【使用するテーブルのER図(半角:物理名/全角:論理名)】

※ER図(Entity Relationship Diagram)とは、テーブル(Entity)とテーブル同士の関連(Relationship)を図に表したもので、データベース設計における代表的な設計図のことです。詳細の説明は、ここでは省かせていただきます。

sql08-01

【準備】

使用するテーブルを作成し、データを挿入します。テーブルが多くて大変ですが、頑張りましょう。

（１）商品マスタのCREATE文とINSERT文

CREATE TABLE ITEM_MST (ITEM_CODE TEXT NOT NULL,ITEM_NAME TEXT,PRIMARY KEY(ITEM_CODE));

INSERT INTO ITEM_MST (ITEM_CODE,ITEM_NAME) VALUES
('100','BANANA'), ('110','APPLE'), ('120','ORANGE')
, ('130','GRAPE'), ('140','PEAR'), ('150','PERSIMMON')
, ('160','STRAWBERRY'), ('170','MELON'), ('180','WATERMELON');

sql08-02

（２）倉庫マスタのCREATE文とINSERT文

CREATE TABLE WH_MST (WH_CODE TEXT NOT NULL,WH_NAME TEXT,PRIMARY KEY (WH_CODE));

INSERT INTO WH_MST (WH_CODE,WH_NAME) VALUES
('100','WH_NO1'), ('200','WH_NO2'),('300','WH_NO3');

sql08-03

（３）受注テーブルのCREATE文とINSERT文

CREATE TABLE ORDER_TBL (ORDER_NO TEXT NOT NULL,ORDER_DATE TEXT NOT NULL,WH_CODE TEXT NOT NULL,ORDER_AMNT INTEGER NOT NULL DEFAULT '0',PRIMARY KEY(ORDER_NO));

INSERT INTO ORDER_TBL
(ORDER_NO,ORDER_DATE,WH_CODE,ORDER_AMNT) VALUES
('OR010','2023-02-28','100','495'), ('OR100','2023-03-01','100','1640')
, ('OR101','2023-03-05','200','2680'), ('OR102','2023-03-10','200','720')
, ('OR103','2023-03-20','300','1780'), ('OR104','2023-03-31','200','3160')
, ('OR200','2023-04-01','100','3560');

sql08-04

（４）受注明細テーブルのCREATE文とINSERT文

CREATE TABLE ORDER_DETAILS (ORDER_NO TEXT,OD_ROW_NO INTEGER,ITEM_CODE TEXT NOT NULL,UNITPRICE INTEGER NOT NULL DEFAULT '0',QUANTITY INTEGER NOT NULL DEFAULT '1',PRIMARY KEY(ORDER_NO,OD_ROW_NO));

INSERT INTO ORDER_DETAILS
(ORDER_NO,OD_ROW_NO,ITEM_CODE,UNITPRICE,QUANTITY) VALUES
('OR010','1','150','99','5'), ('OR100','1','100','100','2')
, ('OR100','2','110','120','4'), ('OR100','3','120','140','3')
, ('OR100','4','130','540','1'), ('OR101','1','110','120','3')
, ('OR101','2','120','140','5'), ('OR101','3','130','540','3')
, ('OR102','1','110','120','6'), ('OR103','1','120','140','5')
, ('OR103','2','130','540','2'), ('OR104','1','100','100','1')
, ('OR104','2','110','120','5'), ('OR104','3','120','140','6')
, ('OR104','4','130','540','3'), ('OR200','1','160','890','4');

sql08-05

※表示されるデータの出力フォーマットを調整します。（ログイン後に１回実施すれば大丈夫です）
.headers on　（ヘッダ情報を出力）
.mode column　（カラム毎に左揃え出力）

sql08-06

【解答例】

SELECT
T1.WH_CODE AS W_CD
, T3.WH_NAME AS W_NM
, T2.ITEM_CODE AS I_CD
, T4.ITEM_NAME AS I_NM
, T1.ORDER_NO AS OD_NO
, T2.QUANTITY AS OD_QT
FROM
ORDER_TBL AS T1 -- ①
INNER JOIN ORDER_DETAILS AS T2 -- ②
ON T1.ORDER_NO = T2.ORDER_NO
INNER JOIN WH_MST AS T3 -- ③
ON T1.WH_CODE = T3.WH_CODE
INNER JOIN ITEM_MST AS T4 -- ④
ON T2.ITEM_CODE = T4.ITEM_CODE
WHERE
T1.ORDER_DATE BETWEEN '2023-03-01' AND '2023-03-31' -- ⑤
ORDER BY
T1.WH_CODE ASC
, T2.ITEM_CODE ASC
, T2.QUANTITY DESC; -- ⑥

【説明】

① 受注日を絞り込むために、受注テーブルをメインテーブルとしています。
② 受注数量を取得するために、受注明細テーブルを受注番号で内部結合しています。
③ 倉庫名を取得するために、倉庫マスタを倉庫コードで内部結合しています。
④ 商品名を取得するために、商品マスタを受注明細データの商品コードで内部結合しています。
⑤ BETWEEN句で2023年3月の受注データを絞り込んでいます。
⑥ 受注数量は降順で表示するため、DESCを指定しています。

【結果】

sql08-07

sql08-08

受注日が2023年3月に含まれるデータが表示されていることが確認できます。4テーブルを使用しているので、各テーブルはAS句で別名を付けて簡素化しています。
テーブルが多いとSQL文も読み難くなるので、各項目の先頭にテーブルの別名を付けて、どのテーブルの項目か分かり易いように記述しています。

（２問目）受注商品の集計

２問目は、集計関数、GROUP BY句を使用した内部結合です。

【問題】

2023年4月5日に受注した商品の受注数量と受注金額を商品毎に集計しなさい。受注金額は、単価(UNITPRICE)と受注数量(QUANTITY)を掛けて求めること。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)
・ITEM_CODE → I_CD
・ITEM_NAME → I_NM
・QUANTITY → OD_QT
・(UNITPRICE * QUANTITY) → OD_AMT

表示順
１．受注金額の降順
２．商品コードの昇順

【使用するテーブルのER図(半角:物理名/全角:論理名)】

sql08-09

【準備】

商品マスタのデータは１問目で挿入したデータを使用します。受注テーブルと受注明細テーブルにデータを追加で挿入します。

（１）受注テーブルのINSERT文

INSERT INTO ORDER_TBL
(ORDER_NO,ORDER_DATE,WH_CODE,ORDER_AMNT) VALUES
('OR410','2023-04-04','100','495'), ('OR500','2023-04-05','100','1640')
, ('OR501','2023-04-05','200','2680'), ('OR502','2023-04-05','200','720')
, ('OR503','2023-04-05','300','1780'), ('OR504','2023-04-05','200','3160')
, ('OR600','2023-04-05','100','3240'), ('OR510','2023-04-05','300','3180')
, ('OR530','2023-04-05','300','888');

sql08-10

（２）受注明細テーブルのINSERT文

INSERT INTO ORDER_DETAILS
(ORDER_NO,OD_ROW_NO,ITEM_CODE,UNITPRICE,QUANTITY) VALUES
('OR410','1','150','99','5'), ('OR500','1','100','100','2')
, ('OR500','2','110','120','4'), ('OR500','3','120','140','3')
, ('OR500','4','130','540','1'), ('OR501','1','110','120','3')
, ('OR501','2','120','140','5'), ('OR501','3','130','540','3')
, ('OR502','1','110','120','6'), ('OR503','1','120','140','5')
, ('OR503','2','130','540','2'), ('OR504','1','100','100','1')
, ('OR504','2','110','120','5'), ('OR504','3','120','140','6')
, ('OR504','4','130','540','3'), ('OR600','1','160','810','4')
, ('OR510','1','100','100','7'), ('OR510','2','110','120','9')
, ('OR510','3','120','140','10'), ('OR530','1','190','888','1');

sql08-11

【解答例】

SELECT
T2.ITEM_CODE AS I_CD
, T3.ITEM_NAME AS I_NM
, SUM(T2.QUANTITY) AS OD_QT -- ④
, SUM(T2.UNITPRICE * T2.QUANTITY) AS OD_AMT -- ⑤
FROM
ORDER_TBL AS T1 -- ①
INNER JOIN ORDER_DETAILS AS T2 -- ②
ON T1.ORDER_NO = T2.ORDER_NO
INNER JOIN ITEM_MST AS T3 -- ③
ON T2.ITEM_CODE = T3.ITEM_CODE
WHERE -- ⑥
T1.ORDER_DATE = '2023-04-05'
GROUP BY -- ⑦
T2.ITEM_CODE
, T3.ITEM_NAME
ORDER BY -- ⑧
OD_AMT DESC
, T2.ITEM_CODE ASC;

【説明】

① 受注日を絞り込むために、受注テーブルをメインテーブルとしています。
② 受注数量を取得するために、受注明細テーブルを受注番号で内部結合しています。
③ 商品名を取得するために、商品マスタを受注明細データの商品コードで内部結合しています。
④ SUM関数を使用して、商品コード、商品名毎に受注数量を集計しています。
⑤ SUM関数を使用して、商品コード、商品名毎に単価と数量を掛けた値を集計しています。
⑥ 受注日が2023年4月５日の受注データを絞り込んでいます。
⑦ 商品コードと商品名をGROUP BY句でグルーピングしています。（表示項目に商品名が含まれているため、ここでも商品名を指定しています。表示項目に商品名が指定されていない場合は、商品名は不要です。）
⑧ 単価と数量を掛ける計算式を、AS句で変換した「OD_AMT」をそのままソート項目に使用しています。

【結果】

sql08-12

sql08-13

受注日が2023年4月5日のデータが、受注金額の降順、受注金額が同じ場合は商品コードの昇順で表示されていることが確認できます。よく利用される、データ集計と内部結合の組合せの例を紹介しました。今まで紹介してきたSQL文に比べ、より実践的なSQL文となりますが、もう皆さんにとっては簡単だったかも知れません。