3分間データベース講座 第3回: 複数のデータを組み合わせる～結合（JOIN）と集約関数～

登場人物：

• チャット先生（通称:先生）： オブジェクト指向マスター。最近はデータベースの世界にも詳しい。

• ボット助手（通称:ボット君）： プログラミングを始めたばかりの若手。データベースはまだ未知の世界。

ボット君： 先生！前回、主キーと外部キーでテーブルを繋ぐ仕組みは理解できました。でも、実際にその繋がったテーブルからデータを取り出すにはどうすればいいんですか？「山田さんが注文した商品の名前」みたいな情報を知りたいんですけど、ユーザー情報と商品情報は別のテーブルにあるんですよね？

先生： 素晴らしい！まさにそこが今日の核心だ。データベースの真の力は、複数のテーブルに散らばったデータを組み合わせて、意味のある情報を引き出すことにある。そのための鍵が「結合（JOIN）」と「集約関数」だ。

1. 複数のテーブルを繋げる～結合（JOIN）

先生： 複数のテーブルを関連づけるには、JOINという機能を使う。これは、異なるテーブルにあるデータを、指定した共通の列を基準にして「横に並べる」操作だ。

先生： 例として、users（ユーザー）テーブル、products（商品）テーブル、orders（注文）テーブルを見てみよう。

users テーブル

+----+--------------+
| id | name         |
+----+--------------+
| 1  | 山田 太郎    |
| 2  | 佐藤 花子    |
| 3  | 鈴木 次郎    |
+----+--------------+

products テーブル

+----+--------------+--------+
| id | name         | price  |
+----+--------------+--------+
| 501| Tシャツ      | 3000   |
| 502| スニーカー   | 8000   |
| 503| キャップ     | 2000   |
+----+--------------+--------+

orders テーブル

+----+-----------+---------------+----------+
| id | user_id   | product_id    | quantity |
+----+-----------+---------------+----------+
| 101| 1         | 501           | 2        |
| 102| 2         | 502           | 1        |
| 103| 1         | 501           | 1        |
| 104| 3         | NULL          | 1        |
+----+-----------+---------------+----------+

先生： JOINにはいくつか種類があるんだが、最も基本的な「INNER JOIN」と、データが欠けていても結合できる「LEFT JOIN」を学んでいこう。

INNER JOIN：一致するデータだけを結合

先生： INNER JOINは、2つのテーブルの共通する列に一致する値があるデータだけを組み合わせて表示する。

-- ordersテーブルとproductsテーブルをINNER JOINで結合する
SELECT
    o.id AS order_id,
    o.user_id,
    p.name AS product_name, -- productsテーブルの商品名
    o.quantity
FROM
    orders AS o -- ordersテーブルに'o'という別名をつける
INNER JOIN
    products AS p ON o.product_id = p.id; -- productsテーブルを'p'という別名で結合

このクエリを実行すると、product_idがNULLの注文104は表示されない。

実行結果

+------------+---------+--------------+----------+
| order_id   | user_id | product_name | quantity |
+------------+---------+--------------+----------+
| 101        | 1       | Tシャツ       | 2        |
| 102        | 2       | スニーカー　   | 1        |
| 103        | 1       | Tシャツ       | 1        |
+------------+---------+--------------+----------+

LEFT JOIN：左側のテーブルのデータをすべて残して結合

先生： LEFT JOINは、FROM句で先に指定したテーブル（左側のテーブル）のデータをすべて残し、右側のテーブルから一致するデータを結合する。一致するデータがなければNULLが表示される。

-- ordersテーブルのデータをすべて残してproductsテーブルと結合する
SELECT
    o.id AS order_id,
    o.user_id,
    p.name AS product_name,
    o.quantity
FROM
    orders AS o
LEFT JOIN
    products AS p ON o.product_id = p.id;

このクエリを実行すると、product_idがNULLの注文104も表示され、product_nameはNULLとなる。

実行結果

+------------+---------+--------------+----------+
| order_id   | user_id | product_name | quantity |
+------------+---------+--------------+----------+
| 101        | 1       | Tシャツ      | 2        |
| 102        | 2       | スニーカー　   | 1        |
| 103        | 1       | Tシャツ      | 1        |
| 104        | 3       | NULL         | 1        |
+------------+---------+--------------+----------+

2. データを集計する～集約関数とGROUP BY

ボット君： 今度は、注文された商品ごとの合計数量を知りたいです。個別の注文ではなく、全体の集計です。

先生： そのような「データを集約（まとめる）」ときに使うのが「集約関数」だ。

代表的な集約関数には、次のようなものがある。

• COUNT(): データの数を数える

• SUM(): 合計値を計算する

• AVG(): 平均値を計算する

• MAX(): 最大値を求める

• MIN(): 最小値を求める

先生： さらに、集計する単位を指定したい場合は、「GROUP BY」を使う。これは、指定した列の値が同じ行を一つのグループとして扱い、そのグループごとに集約関数を適用する機能だ。

例えば、「商品ごとの合計数量」を知りたい場合は、JOINしたテーブルをproduct_nameでGROUP BYしてからquantityをSUM()で合計すればいい。

-- 商品ごとの注文数量の合計を算出する
SELECT
    p.name AS product_name, -- 商品名
    SUM(o.quantity) AS total_quantity -- 注文数量の合計
FROM
    orders AS o
INNER JOIN
    products AS p ON o.product_id = p.id
GROUP BY
    p.name; -- 商品名ごとにグループ化する

このクエリの実行結果は、以下のようなイメージになる。

実行結果

+--------------+------------------+
| product_name | total_quantity   |
+--------------+------------------+
| Tシャツ      | 3                |
| スニーカー    | 1                |
+--------------+------------------+

WHEREとHAVING：集計前後の絞り込み

先生： GROUP BY句でデータを集計した後、さらに条件で絞り込みたい場合がある。その場合はWHERE句ではなく、「HAVING」句を使う。

• WHERE: 集計前のデータを絞り込む

• HAVING: 集計後のデータを絞り込む

-- 合計数量が2件以上の商品だけを表示する
SELECT
    p.name AS product_name,
    SUM(o.quantity) AS total_quantity
FROM
    orders AS o
INNER JOIN
    products AS p ON o.product_id = p.id
GROUP BY
    p.name
HAVING
    SUM(o.quantity) >= 2; -- 集計結果の合計数量が2以上のものに絞り込む

まとめと次回の予告

先生： 今日のまとめだ。

• 結合（JOIN）: 複数のテーブルを繋ぎ、データを組み合わせるための機能。**INNER JOINやLEFT JOIN**などがある。

• 集約関数: SUMやCOUNTなど、データの集計を行うための機能。

• GROUP BY: 集計を行う際、どの列を基準にグループ化するかを指定する。

• WHEREとHAVING: 集計前の絞り込みにはWHERE、集計後の絞り込みにはHAVINGを使う。

これらの機能を組み合わせることで、データベースからビジネスに役立つ情報を簡単に取り出せるようになる。

先生： 次回は、データベースのデータ品質を守るための「正規化」と「ER図」について学んでいこう。

次回：第4回: データベース設計の基礎～正規化とER図の考え方～

#データベース #SQL #結合 #JOIN #集約関数 #GROUPBY #HAVING #プログラミング学習