Print Friendly, PDF & Email

この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。

前回は、検索結果のソート、グループ化と集約関数を解説しました。

今回は、複数テーブルに対する検索について解説します。

これまでは単一のテーブルに対してのみの操作でした。

しかし、リレーションデータベースは正規化の考え方に則り、複数テーブルに分けてデータを格納することが一般的です。

複数のテーブルにまたがったデータを使って結合できなければせっかくのデータベースの機能を活用することはできません。

そこで複数のテーブルを結合する操作が重要になります。

この操作はリレーショナル・データベースの3種類の表操作のうち結合(join)にあたります。

joinにはさまざまな種類があることが「SQL JOIN Diagram」でグーグル検索すればお分かりになると思います。

そのうち、今回は内部結合と左外部結合の2つを紹介します。

内部結合

内部結合とは、結合条件に指定している値が両方のテーブルに存在するレコードを抽出する結合方法です。

結合条件に指定している値が一方のテーブルにのみ存在するレコードは抽出されません。

【内部結合の例】

新人エンジニア研修

なお、結合の前提として一方のテーブルの主キーが他方のテーブルの外部キーとして組み込まれている必要があります

そうでないと、レコードとレコードを結合する手がかりが無くなってしまうからです。(下記 表名1.列名 = 表名2.列名 の部分)

内部結合の構文

SELECT 列名1,列名2, ... FROM

表名1

JOIN 表名2 ※

ON 表名1.列名 = 表名2.列名;

※標準SQLではINNER JOINと書きます。

SQLの例

SELECT 
    id, name, saleDateTime
FROM
    cars
        JOIN
    sales ON id = car_id;

※この例のように列名だけで表名が特定できる場合は、単に列名だけを書けばよく、 表名1.列名 のように書く必要はありません。

結果の例

新人エンジニア研修
以下省略

なお、2つの表で同じ列名が使われている場合、そのままでは「Column 'カラム名' in field list is ambiguous」というエラーメッセージが表示されます。

その場合は、どの表の列であるかを明示するために「表名.列名」のように指定します。

慣れないうちは列名に「*」を使い、まずはテーブルの結合を優先させ、全てのテーブルが結合された大きなテーブルを得ます。

その後で結合済みのテーブルを見ながらゆっくりと対象の列を選択するのが良いでしょう。

SQLの例

SELECT 
    *
FROM
    cars
        JOIN
    sales ON id = car_id;

結果の例

新人エンジニア研修
2つのテーブルが結合されたテーブル

この方法はテーブルが3つ以上に増えてきた場合にも威力を発揮します。

例題1

cars表とsales表をid(car_id)で内部結合しなさい。

そのうえで、以下のようにid, name, price, saleDateTimeを表示させなさい。

新人エンジニア研修

<解答例>

SELECT 
    id, name, price, saleDateTime
FROM
    cars
        JOIN
    sales ON id = car_id;

例題2

cars表とsales表、customers表を内部結合しなさい。

そのうえで、以下のようにid, name, price, saleDateTimeを表示させなさい。

新人エンジニア研修

<解答例>

SELECT 
    id, name, price, customer_name, saleDateTime
FROM
    cars
        JOIN
    sales ON id = car_id
        JOIN
    customers ON sales.customer_id = customers.customer_id;

なお、ASを使って別名をつけると複雑な条件もシンプルに書くことができます。

sales AS s ON id = car_id JOIN customers AS c ON s.customer_id = c.customer_id;

例題3

どの車が何台売れたのかを知りたい。

cars表とsales表からid, name, price,販売台数を、販売台数の多い順に並べ替えて表示しなさい。

<解答例>

SELECT 
    id, name, price, COUNT(*)
FROM
    cars
        JOIN
    sales ON id = car_id
GROUP BY car_id
ORDER BY COUNT(*) DESC;

例題4

どの車が一番売上に貢献したかを知りたい。

cars表とsales表からid, name, price,販売金額合計を、販売金額合計の多い順に並べ替えて表示しなさい。

なお、販売金額合計にはその名前をつけて表示すること。

<解答例>

    SELECT 
    id, name, SUM(price) as '販売合計金額'
FROM
    cars
        JOIN
    sales ON id = car_id
GROUP BY car_id
ORDER BY SUM(price) DESC;

左外部結合

外部結合とは、 結合条件に指定している値がテーブルのどちらか一方にしかないレコードも抽出することができる結合方法です。

【左外部結合の例】

新人エンジニア研修

ここで左側のテーブルと呼んでいるのは、以下のSQLの構文の表1にあたる先に記述する方のテーブルです。

左外部結合の構文

SELECT 列名1,列名2, ... FROM

表名1(左)

LEFT OUTER JOIN 表名2(右) ※

ON 表名1.列名 = 表名2.列名;

※MySQLの場合、 OUTERは省略可

SQLの例

SELECT 
    id, name, saleDateTime
FROM
    cars
        LEFT JOIN
    sales ON id = car_id;

結果の例

新人エンジニア研修

右外部結合もありますが、左右を入れ替えれば同じことですからここでは解説を割愛します。

まとめ

□内部結合とは、結合条件に指定している値が両方のテーブルに存在するレコードを抽出する結合方法

□外部結合とは、結合条件に指定している値がテーブルのどちらか一方にしかないレコードも抽出することができる結合方法

□結合の前提として一方のテーブルの主キーが他方のテーブルの外部キーとして組み込まれている必要がある

□慣れないうちは*を使ってテーブルの結合を優先させ、すべての列から必要な列を選択すると迷わない

以上、複数テーブルに対する検索について見てきました。

完全外部結合というのもあるのですが、MySQLでは直接指定できないためこの記事では省略しています。

次回は副問合せについて見ていきましょう。

データベース(MySQL)へ戻る