この記事では、弊社の新人エンジニア研修の参考に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と書きます。
※もしも、列名だけで表名が特定できる場合は、単に列名だけを書けばよく、 表名1.列名 のように書く必要はありません。
以下の7_1.sqlはcars表とsales表を結合して、car_id,name,saleDatetTimeを抽出するSQL文です。
結果は下記図7.2のようになります。
なお、表名を付けずに列名だけで実行しようとすると「Column 'カラム名' in field list is ambiguous」というエラーメッセージが表示されますので試しておいてください。
【 ambiguous 】＝曖昧だとの指摘ですね。
どの表の列であるかを明示するために「表名.列名」のように指定することを忘れないようにしましょう。
慣れないうちは以下7_2.sqlのように列名に「*」を使い、まずはテーブルの結合を優先させ、全てのテーブルが結合された大きなテーブルを得ます。
その後で結合済みのテーブルを見ながらゆっくりと対象の列を選択するのが良いでしょう。（下図7.3参照）
この方法はテーブルが3つ以上に増えてきた場合にも威力を発揮します。
例題1
cars表とsales表をicar_idで内部結合しなさい。
そのうえで、以下のようにid, name, price, saleDateTimeを表示させなさい。
＜解答例＞
例題2
cars表とsales表、customers表を内部結合しなさい。
そのうえで、以下のようにcars.car_id, cars.name, price, customers.name, saleDateTimeを表示させなさい。
＜解答例＞
なお、ASを使って別名をつけると複雑な条件もシンプルに書くことができます。
sales AS s ON id = car_id JOIN customers AS c ON s.customer_id = c.customer_id;
例題3
どの車が何台売れたのかを知りたい。
cars表とsales表からcar_id, name, price,販売台数を、販売台数の多い順に並べ替えて表示しなさい。
＜解答例＞
例題4
どの車が一番売上に貢献したかを知りたい。
cars表とsales表からid, name, price,販売金額合計を、販売金額合計の多い順に並べ替えて表示しなさい。
なお、販売金額合計にはその名前をつけて表示すること。
＜解答例＞
なお、ここで以下のように記述してもうまくいきませんので確認ください。
ORDER BY '販売合計金額' DESC;
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_id5以上のデータを削除してから試してください。
右外部結合もありますが、左右を入れ替えれば同じことですからここでは解説を割愛します。
＜まとめ：隣の人に正しく説明できたらチェックを付けましょう＞
□内部結合とは、結合条件に指定している値が両方のテーブルに存在するレコードを抽出する結合方法
□外部結合とは、結合条件に指定している値がテーブルのどちらか一方にしかないレコードも抽出することができる結合方法
□結合の前提として一方のテーブルの主キーが他方のテーブルの外部キーとして組み込まれている必要がある
□慣れないうちは*を使ってテーブルの結合を優先させ、すべての列から必要な列を選択すると迷わない
以上、複数テーブルに対する検索について見てきました。
完全外部結合というのもあるのですが、MySQLでは直接指定できないためこの記事では省略しています。
興味のある方はインターネットで調べてみてください。
次回はSQLの中にSQLを書く副問合せについて見ていきましょう。