7.複数テーブルにまたがったデータも分析する - セイコンサルティンググループ

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

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

1. テーブル結合の必要性

これまでは単一のテーブルに対してのみの操作でした。しかし、リレーショナルデータベースは正規化の考え方に則り、複数テーブルに分けてデータを格納することが一般的です。複数のテーブルにまたがったデータを使って結合できなければせっかくのデータベースの機能を活用することはできません。そこで複数のテーブルを結合する操作が重要になります。この操作はリレーショナル・データベースの3種類の表操作のうち結合【join】にあたります。

joinにはさまざまな種類があることが「SQL JOIN Diagram」でグーグル検索すればお分かりになると思います。そのうち、今回は内部結合と左外部結合の2つを紹介します。

2. 内部結合

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

【内部結合の例】

なお、結合の前提として一方のテーブルの主キーが他方のテーブルの外部キーとして組み込まれている必要があります。そうでないと、レコードとレコードを結合する手がかりが無くなってしまうからです。（下記表名1.列名 = 表名2.列名の部分）

内部結合の構文

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

表名1

JOIN 表名2 ※

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

※標準SQLではINNER JOINと書きます。INNERが省略できるのはMySQL特有の文法です。

※もしも、フィールド名だけで表名が特定できる場合は、単にフィールド名だけを書けばよく、表名1.列名のように書く必要はありません。

ちなみに英語の【join】は結合するという意味があります。また、英語の【on】は基本的には何かに接しているという意味の接続詞ですが、日本語同様、「～の上で」のような条件を示す際にもよく使われます。例えば、【on the condition that】には「～という条件で」という意味があります。

以下の7_1.sqlはcars表とsales表を結合して、car_id,name,saleDatetTimeを抽出するSQL文です。

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

結果は下記図7.2のようになります。

なお、表名を付けずにフィールド名だけで実行しようとすると「Column 'カラム名' in field list is ambiguous」というエラーメッセージが表示されますので試しておいてください。【 ambiguous 】＝曖昧だとの指摘ですね。どの表の列であるかを明示するために「表名.フィールド名」のように指定することを忘れないようにしましょう。

慣れないうちは以下7_2.sqlのようにフィールド名に「*」を使い、まずはテーブルの結合を優先させ、全てのテーブルが結合された大きなテーブルを得ます。その後で結合済みのテーブルを見ながらゆっくりと対象の列を選択するのが良いでしょう。（下図7.3参照）

SELECT 
    *
FROM
    cars
        JOIN
    sales ON cars.car_id = sales.car_id;

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

例題1

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

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

例題2

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

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

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

SELECT 
    ca.car_id, ca.name, price, cs.name, saleDateTime
FROM
    cars AS ca
        JOIN
    sales AS s ON ca.car_id = s.car_id
        JOIN
    customers AS cs ON s.customer_id = cs.customer_id;

例題3

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

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

例題4

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

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

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

3. 左外部結合

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

【左外部結合の例】

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

左外部結合の構文

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

表名1（左）

LEFT OUTER JOIN 表名2（右） ※

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

※MySQLの場合、 OUTERは省略可

以下の7_7.sqlはcars表とsales表を左外部結合してcar_id, name, saleDateTimeを表示します。cars表を“左”に置きますので1台も売れていない車も表示されます。

※この演習をする際にはsales表からsale_idが5以上のデータを削除してから試してください。

SELECT 
    c.car_id, name, saleDateTime
FROM
    cars AS c
        LEFT JOIN
    sales AS s ON c.car_id = s.car_id;

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

＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞

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

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

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

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

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

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

興味のある方はインターネットで調べてみてください。

次回はSQLの中にSQLを書く副問合せについて見ていきましょう。

IT企業向け新人研修おすすめ資料　無料公開中 (saycon.co.jp)

新人エンジニア研修向けデータベース解説の目次に戻る

研修のカリキュラムを見てみる