SQLの内部結合(INNER JOIN)を使ってみよう!

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

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

(1問目)受注商品の一覧表示

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

表示順
1.    倉庫コードの昇順
2.    商品コードの昇順
3.    受注数量の降順

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

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

sql08-01

【準備】

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

(1)商品マスタの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

(2)倉庫マスタの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

(3)受注テーブルの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

(4)受注明細テーブルの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

※表示されるデータの出力フォーマットを調整します。(ログイン後に1回実施すれば大丈夫です)
.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文も読み難くなるので、各項目の先頭にテーブルの別名を付けて、どのテーブルの項目か分かり易いように記述しています。

(2問目)受注商品の集計

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

【問題】

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

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

表示順
1.受注金額の降順
2.商品コードの昇順

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

sql08-09

【準備】

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

(1)受注テーブルの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

(2)受注明細テーブルの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月5日の受注データを絞り込んでいます。
⑦    商品コードと商品名をGROUP BY句でグルーピングしています。(表示項目に商品名が含まれているため、ここでも商品名を指定しています。表示項目に商品名が指定されていない場合は、商品名は不要です。)
⑧    単価と数量を掛ける計算式を、AS句で変換した「OD_AMT」をそのままソート項目に使用しています。

【結果】

sql08-12

sql08-13

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

おすすめの学習コンテンツ

本記事を読み終わって、さらに学びたい方、次は問題にチャレンジしてみたい方には、以下の無料コンテンツをご用意しておりますので、ぜひ活用してみてください!

SQLiteについて
・本記事で使用したデータベース「SQLite」について解説する資料です。
 SQLiteとは?他のデータベースとの違いも解説

SQLの教科書
・一般的な解説から、実際にSQLを動かす操作イメージまで幅広く解説する資料です。
 「SQLの教科書」ビックデータ時代の必須科目

SQL無料トライアルテスト
・TOPSIC-SQLの無料トライアルテストをご受験していただけます。
 TOPSIC-SQL無料トライアルテスト

SQLコンテスト
・簡単な会員登録をするだけでSQLのオンラインコンテストに無料で参加できます。
    過去に開催されたコンテストの問題にもチャレンジできます。
 TOPSIC SQL CONTEST

SQL基礎解説YouTube
・「TOPSIC-SQL」を元にしたオンラインウェビナーです。
 SQL-BOOT CAMP #1 
 ビッグデータ時代の必須科目SQLを学ぶ「実戦で通用する武器を身につけよう!」

まとめ

本記事では、初心者訓練所の第8弾として、テーブル結合の中でもよく利用される「内部結合」について説明しました。実践形式の問題に対しての解答を通して、「内部結合」の使い方のイメージがついたのではないでしょうか。それでは、次の初心者訓練所でまたお会いしましょう!


(株)システムインテグレータ インキュベーション部 元鬼軍曹 久保 司


RELATED POST関連記事


RECENT POST「SQL‐BOOTCAMP」の最新記事


SQL‐BOOTCAMP

SQLの日付・時間関数(DATE、TIME、DATETIME、JULIANDAY、STRFTIME)を使ってみよう!

SQL‐BOOTCAMP

SQLのウィンドウ関数(LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE)を使ってみよう!

SQL‐BOOTCAMP

SQLのウィンドウ関数(PERCENT_RANK、CUME_DIST、NTILE)を使ってみよう!

SQL‐BOOTCAMP

SQLのウィンドウ関数(ROW_NUMBER、RANK、DENSE_RANK)を使ってみよう!

SQLの内部結合(INNER JOIN)を使ってみよう!

TOPSIC TOPへ