この記事では、弊社の新人エンジニア研修の参考に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;結果の例
この方法はテーブルが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では直接指定できないためこの記事では省略しています。
次回は副問合せについて見ていきましょう。