この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
SQLのテーブル結合の理解を深める為に調査した結果を記事にしました。 想定した状況に必要な情報を複数のテーブルから導き出すという設定です。
想定した状況1
目的
「店舗ごとの在庫の金額と数量を調べる必要が出た。」
テーブル
テーブル1:shop
店舗の商品ごとの在庫です。 id = 行番号、shop_name = 店舗名、item_id = 商品ID、quantity = 数量
+----+-------------+---------+----------+
| id | shop_name | item_id | quantity |
+----+-------------+---------+----------+
| 1 | Music Store | 1 | 1000 |
| 2 | Music Store | 2 | 200 |
| 3 | Music Store | 4 | 100 |
| 4 | Book Store | 3 | 1000 |
| 5 | Book Store | 4 | 500 |
+----+-------------+---------+----------+
テーブル2:item
商品情報です。 item_id = 商品ID、item = 商品名、price = 単価、category = 商品の種別
+---------+-----------+-------+----------+
| item_id | item_name | price | category |
+---------+-----------+-------+----------+
| 1 | cd | 1500 | music |
| 2 | record | 2000 | music |
| 3 | novel | 800 | book |
| 4 | magazine | 500 | book |
| 5 | dvd | 4000 | movie |
+---------+-----------+-------+----------+
ポイント
2つのテーブル”shop"と"item"は、"item_id"で結びついています。
方法1:INNER JOIN(内部結合)
構文
SELECT
テーブル.表示するフィールド
FROM
テーブル1 INNER JOIN テーブル2
ON
テーブル.比較するフィールド
実行するSQL文
SELECT
shop.shop_name,
shop.item_id,
item.item_name,
item.price,
shop.quantity
FROM
shop INNER JOIN item
ON
shop.item_id = item.item_id;
実行結果
+-------------+---------+-----------+-------+----------+
| shop_name | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store | 1 | cd | 1500 | 1000 |
| Music Store | 2 | record | 2000 | 200 |
| Book Store | 3 | novel | 800 | 1000 |
| Music Store | 4 | magazine | 500 | 100 |
| Book Store | 4 | magazine | 500 | 500 |
+-------------+---------+-----------+-------+----------+
解説
SQL文の意味は、 「 shopテーブルのitem_idに一致する項目を、itemテーブルのitem_idと比較して抜き出す。 表示するのはSELECTで指定したフィールドのみ。」 となります。
方法2:OUTER JOIN(外部結合)
OUTER JOINの場合は、FROM句で元となるテーブルをRIGHTとLEFTで指定できます。 OUTER JOINを中心として左か右という意味になっています。
RIGHTの場合
構文
SELECT
テーブル.表示するフィールド
FROM
照合するテーブル RIGHT OUTER JOIN 元となるテーブル
ON
テーブル.比較するフィールド
実行するSQL文
SELECT
shop.shop_name,
shop.item_id,
item.item_name,
item.price,
shop.quantity
FROM
shop RIGHT OUTER JOIN item
ON
shop.item_id = item.item_id;
実行結果
+-------------+---------+-----------+-------+----------+
| shop_name | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store | 1 | cd | 1500 | 1000 |
| Music Store | 2 | record | 2000 | 200 |
| Music Store | 4 | magazine | 500 | 100 |
| Book Store | 3 | novel | 800 | 1000 |
| Book Store | 4 | magazine | 500 | 500 |
| NULL | NULL | dvd | 4000 | NULL |
+-------------+---------+-----------+-------+----------+
解説
INNER JOINの時より1行増えています。 これは、元となったテーブルがitemなので、比較対象のitem側のitem_idの項目が全て表示されるためです。 shop_nameとitem_idとquantityの値がNULLなのは、item.item_idの"dvd"に該当する項目がshopには無いためです。
LEFTの場合
構文
SELECT
テーブル.表示するフィールド
FROM
元となるテーブル LEFT OUTER JOIN 照合するテーブル
ON
テーブル.比較するフィールド
SQL文
SELECT
shop.shop_name,
shop.item_id,
item.item_name,
item.price,
shop.quantity
FROM
shop LEFT OUTER JOIN item
ON
shop.item_id = item.item_id;
実行結果
+-------------+---------+-----------+-------+----------+
| shop_name | item_id | item_name | price | quantity |
+-------------+---------+-----------+-------+----------+
| Music Store | 1 | cd | 1500 | 1000 |
| Music Store | 2 | record | 2000 | 200 |
| Book Store | 3 | novel | 800 | 1000 |
| Music Store | 4 | magazine | 500 | 100 |
| Book Store | 4 | magazine | 500 | 500 |
+-------------+---------+-----------+-------+----------+
解説
RIGHTの場合と違い、shopを元にしているので、itemの該当しない項目は表示されず、NULLは有りません。
想定した状況2
目的
「状況1結果に、追加テーブルcategoryの情報も必要になった。 ただし今度は店名'Music Store'のみが欲しい。」
追加テーブル
テーブル3:category
テーブル2のcategoryと結びついています。
+----------+--------+
| category | data |
+----------+--------+
| music | ongaku |
| book | hon |
+----------+--------+
INNER JOINで結合
実行するSQL文
一部、書き方を変えてSQL文を短縮しています。 ・INNER JOINはJOINで判別してくれます。もしOUTER JOINにしたい場合はLEFTかRIGHTを付けます。 ・ASでテーブル名の略称を作っています。shop=S、item=I、category=C。
SELECT
S.shop_name,
S.item_id,
I.item_name,
I.price,
S.quantity,
I.category,
C.data
FROM
shop AS S
JOIN item AS I
ON S.item_id = I.item_id
JOIN category AS C
ON I.category = C.category
WHERE
shop_name='Music Store';
実行結果
+-------------+---------+-----------+-------+----------+----------+--------+
| shop_name | item_id | item_name | price | quantity | category | data |
+-------------+---------+-----------+-------+----------+----------+--------+
| Music Store | 1 | cd | 1500 | 1000 | music | ongaku |
| Music Store | 2 | record | 2000 | 200 | music | ongaku |
| Music Store | 4 | magazine | 500 | 100 | book | hon |
+-------------+---------+-----------+-------+----------+----------+--------+
解説
処理の流れですが、FROM句でshopとitemを結合し、その結果をcategoryと結合しています。 そしてWHERE句で、'Music Store'を指定して出力結果を更に絞っています。
まとめ
SELECTが行の選択と連結なら、JOINは列の選択と連結だと分かりました。 ちなみに、OUTER JOINを使う時は一般的にLEFTを使う様です。