テーブル結合について (Part.3) － 内部結合を極めよう

はじめに

今回は、「テーブル結合」のPart.3として、まず前回(Part.2)で出題した問題の答え合わせを行い、続けて実践的な問題を解きながら「内部結合」について深掘りしていきます。内部結合は、よく使われるテーブル結合なので、しっかり理解していきましょう。

訓練に使用するデータベースは、これまでに引き続き、いつもの「SQLite」を使用していきます。

それでは、今回もハッスルして訓練に行ってみましょう!

「自然結合」の問題(前回)の答え合わせ

【解答】
自然結合の基本的な構文は下記のようになります。

SELECT 項目X, … FROM テーブルY
NATURAL INNER JOIN テーブルZ;

それでは、前回で登場したテーブルで同じ名前の項目がない、サイズテーブル(tbl_size)と部署テーブル(tbl_dept)を使用して確認してみましょう。データも前回で作成したデータを利用します。今回が初めての方は前回の内容を参照して、それぞれのテーブルを作成しデータを挿入してください。

始めにサイズテーブルに登録されている内容を確認してみましょう。

.headers on
.mode column

SELECT *  FROM tbl_size;

*「.headers」「.mode」は表示内容の設定コマンドなので、ログイン後に1回実行すればOKです。

次に、部署テーブルの内容を確認してみましょう。

SELECT *  FROM tbl_dept;

それぞれのデータを確認できたので、実際に2つのテーブルを自然結合で結合してみます。

SELECT S.* ,  D.* FROM tbl_size AS S
NATURAL INNER JOIN tbl_dept AS D;

結果は、上図のように全てのデータの組み合わせが表示されます。気付いた方もいらっしゃると思いますが、交差結合(CROSS JOIN)した場合と同じ結果となります。

このように、異なるテーブル結合を使用しても同じ結果になる場合もあるので、色々と試してみてください。また、それぞれのテーブルの全ての項目を表示するために、「S.*」「D.*」のように項目名を省略してアスタリスクを使用しています。

「内部結合」の深掘り

ここからは、実際に「内部結合」を利用した問題を解きながら、内部結合を深掘りしていきます。

(1)受注商品の一覧

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

【使用するテーブルのER図(半角:物理名/全角:論理名)】
※ER図(Entity Relationship Diagram)とは、テーブル(Entity)とテーブル同士の関連(Relationship)を図に表したもので、データベース設計における代表的な設計図のことです(ここでは、詳細の説明は省略します)。

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

【商品マスタCREATEコマンド】

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

【商品マスタINSERTコマンド】

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');

【倉庫マスタCREATEコマンド】

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

【倉庫マスタINSERTコマンド】

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

【受注テーブルCREATEコマンド】

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コマンド】

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

【受注明細テーブルCREATEコマンド】

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コマンド】

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');

【解答例】

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 '2021-10-01' AND '2021-10-31' -- ⑤
ORDER BY
    T1.WH_CODE ASC
    , T2.ITEM_CODE ASC
    , T2.QUANTITY DESC; -- ⑥

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

【結果】
受注日が2021年10月に含まれるデータが表示されていることが確認できます。4テーブルを使用しているので、各テーブルはAS句で別名を付けて簡素化しています。

テーブルが多いとSQL文も読み難くなるので、各項目の先頭にテーブルの別名を付けて、どのテーブルの項目か分かり易いように記述しています。