MySQL 入門

MySQL INNER JOIN

リレーショナルデータベース（RDB）におけるデータは、効率性を維持し冗長性を避けるために、通常は複数のテーブルに分散して保存されます（モジュール 2 およびモジュール 6 の第1レッスンでデータベース設計とプライマリキー・外部キーについて議論した通りです）。これらの関連するテーブル間でデータを組み合わせ、意味のある情報を取得するために、JOIN（結合） 操作を使用します。

INNER JOIN（内部結合） は、最も基本的かつ頻繁に使用される JOIN タイプの1つであり、2つ以上のテーブル間の関連するカラムに基づいてデータ行をマージするために使用されます。指定された結合条件に基づき、両方のテーブルに一致するデータが存在する行のみを返します。

1. INNER JOINの理解

2つのテーブルのカラム間に一致がある限り、INNER JOIN キーワードは両方のテーブルからすべての行を選択します。共通のフィールド（またはフィールドのセット）が一致する値を持っている場合、2つのテーブルの行を効果的に組み合わせます。もう一方のテーブルに対応する一致がない行は、結果セット（リザルトセット）から除外されます。

私たちが使用してきた world データベースを例に考えてみましょう。ここには city（都市）テーブルと country（国）テーブルがあります。各都市は特定の国に属しています。city テーブルには CountryCode カラムがあり、これは country テーブルの Code カラム（プライマリキー）を参照する外部キーです。INNER JOIN を使用すると、都市名とその対応する国名など、2つのテーブルからの情報を単一の結果セットにマージできます。

INNER JOIN の基本構文は以下の通りです：

SELECT
    column_list -- 取得するカラムのリスト
FROM
    table1 -- 結合元テーブル1
INNER JOIN
    table2 ON table1.matching_column = table2.matching_column; -- 結合先テーブル2と結合条件
  • SELECT column_list: 結合後のテーブルから取得したいカラムを指定します。
  • FROM table1: データを取得する最初のテーブルを指示します。
  • INNER JOIN table2: table1 と結合する2番目のテーブルを指定します。
  • ON table1.matching_column = table2.matching_column: これが 結合条件（Join Condition） です。2つのテーブル間のリレーションシップを指定します。INNER JOIN は、table1.matching_column の値が table2.matching_column の値と等しい行のみを返します。

テーブル名を表すために エイリアス（Aliases） を使用するのが一般的です。これにより、特に複数のテーブルを扱う場合やテーブル名が長い場合に、クエリを短く、より読みやすくすることができます。

SELECT
    c.Name AS 都市名,
    co.Name AS 国名
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code;

ここでは、c は city テーブルのエイリアス、co は country テーブルのエイリアスです。カラム参照の長さを短縮することで、コードの可読性が大幅に向上します。

2. INNER JOINの具体的な活用例

world データベースを使用して、いくつかの実例を通して INNER JOIN を解説します。

2.1 例1：都市とその所属国の一覧取得

すべての都市とその所属国の一覧を表示するには、共通の CountryCode / Code カラムに基づいて city と country テーブルを結合します。

SELECT
    city.Name AS 都市,
    country.Name AS 国
FROM
    city
INNER JOIN
    country ON city.CountryCode = country.Code
ORDER BY
    country.Name, city.Name;

このクエリでは：

  1. city テーブルの Name（別名：都市）と country テーブルの Name（別名：国）を選択しています。
  2. city と country を結合しています。
  3. ON 句で city テーブルの CountryCode が country テーブルの Code と一致しなければならないことを指定しています。
  4. ORDER BY を使用して、まず国名、次に都市名のアルファベット順でソートしています。

結果セットには、country テーブルに一致する国コードを持つ都市のみが含まれます。何らかの理由で、city テーブルのある都市が country テーブルに存在しない CountryCode を持っている場合、その都市は結果に表示されません。

2.2 例2：3つのテーブルを結合する

INNER JOIN は、3つ以上のテーブルを組み合わせるように拡張できます。複数の INNER JOIN 句をチェーンするだけです。例えば、すべての都市、その所属国、およびその国が属する大陸をリストアップしたいとします。country テーブルにはすでに Continent カラムがあるため、追加の結合なしで取得可能です：

SELECT
    c.Name AS 都市,
    co.Name AS 国,
    co.Continent AS 大陸
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code
ORDER BY
    co.Continent, co.Name, c.Name;

ここで、さらに複雑なシナリオを想定します。各国の公用語に関する情報を保存している countrylanguage というテーブルがあるとします。このテーブルには CountryCode と Language カラムがあります。

都市、その所属国、およびその国の公用語をリストアップするには、city を country に結合し、さらに country を countrylanguage に結合する必要があります。

SELECT
    c.Name AS 都市,
    co.Name AS 国,
    cl.Language AS 公用語
FROM
    city AS c
INNER JOIN
    country AS co ON c.CountryCode = co.Code
INNER JOIN
    countrylanguage AS cl ON co.Code = cl.CountryCode
WHERE
    cl.IsOfficial = 'T' -- 'T' が公用語を表すと仮定
ORDER BY
    co.Name, c.Name, cl.Language;

ここでは：

  1. 最初の INNER JOIN で c.CountryCode = co.Code を使用して city を country に結合しています。
  2. 2番目の INNER JOIN で co.Code = cl.CountryCode を使用して country を countrylanguage に結合しています。
  3. 公用語のみをフィルタリングするために WHERE 句が追加されており、WHERE 句が JOIN 操作とどのように連携するかを示しています。

2.3 例3：GROUP BYを組み合わせた統計処理

INNER JOIN は本質的に、一致しない行をフィルタリングして除外します。これは、ある国が country テーブルに存在するが、city テーブルに対応するエントリがない場合、その国は INNER JOIN の結果には表示されないことを意味します。

この挙動を利用して、一致するデータを持つエンティティをカウントまたは識別できます。例えば、各国にいくつの都市があるかを知りたい場合、INNER JOIN を GROUP BY および COUNT と組み合わせることができます。

SELECT
    co.Name AS 国,
    COUNT(c.ID) AS 都市数
FROM
    country AS co
INNER JOIN
    city AS c ON co.Code = c.CountryCode
GROUP BY
    co.Name
ORDER BY
    都市数 DESC;

このクエリでは：

  1. country と city を結合しています。
  2. country.Name ごとに結果をグループ化しています。
  3. 各国の都市 ID の数をカウントしています。

INNER JOIN により、結果セットには city テーブルに少なくとも1つの都市を持つ国のみが含まれます。都市が0件の国はこの結果セットに表示されません。

3. INNER JOINとWHERE、ORDER BYの併用

通常、結合した結果に対してフィルタリングやソートを行う必要があります。WHERE 句と ORDER BY 句は、結合操作の後に適用されます。

顧客注文システムを例に：'Alice Smith' によって出された注文を取得し、注文日順にソートします。

SELECT
    o.OrderID,
    o.OrderDate,
    o.TotalAmount,
    c.FirstName,
    c.LastName
FROM
    Orders AS o
INNER JOIN
    Customers AS c ON o.CustomerID = c.CustomerID
WHERE
    c.FirstName = 'Alice' AND c.LastName = 'Smith'
ORDER BY
    o.OrderDate DESC;

このクエリは、まず Orders（注文）と Customers（顧客）テーブルを結合します。その後、結合された結果をフィルタリングし、'Alice Smith' による注文のみを表示します。最後に、これらの特定の注文を OrderDate（注文日）の降順で並べ替えます。