MySQLでテーブル結合

2015.12.24

はじめに

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句で元となるテーブルをRIGHTLEFTで指定できます。
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を使う様です。