WEBサービス創造記

WEBサービスを作ったり保守したりしてる人のメモブログです。

SQLテーブル結合まとめ

      2013/05/10

結合処理の概要

“結合”とはリレーショナルデータベースにおける関係演算(テーブル操作)のひとつで、複数のテーブルを繋いでひとつのテーブルにする操作のことです。

また、結合以外の関係演算には特定の行を抽出する”選択”と指定した列を抽出する”射影”があります。

結合の種類

結合にはいくつか種類があり、同じデータを持つテーブルに対して同じ条件で結合処理を行っても結果が異なることがあります。

代表的な結合の種類としては以下のものがあります。

内部結合
(INNER JOIN句)
結合条件が一致したデータのみを取得する … FROM 左表 INNER JOIN 右表 [結合条件]
※INNERを省略して、”JOIN”と書いても同じ結果となる
外部結合
(OUTER JOIN句)
内部結合と違い、結合条件に一致したデータに加えて”どちらかの”テーブルにしか存在しないデータも取得する
この”どちらのテーブルを基準に取得するか”によって構文が2つに別れる
左外部結合
(LEFT JOIN)
… FROM 左表 LEFT OUTER JOIN 右表 [結合条件]
※OUTERを省略して、”LEFT JOIN”と書いても同じ結果となる
右外部結合
(RIGHT JOIN)
… FROM 左表 RIGHT OUTER JOIN 右表 [結合条件]
※OUTERを省略して、”RIGHT JOIN”と書いても同じ結果となる

結合を行うSQL

実際にSQL文を発行してこれらどのような結果になるかを確かめたいと思います。
そのために、以下のようなテーブルを作成してみました。

このテーブル構造は、ユーザは会員登録を行うことでブログを作成できるレンタルブログASPのようなものを想定しています。
ここではわかりやすく、ユーザはひとつだけブログを管理することができ、ブログにはひとつだけカテゴリを設定することができるとします。

実データは以下の通り入っています。

mysql> SELECT * FROM users;
+----+---------------+
| id | name          |
+----+---------------+
| 36 | 川手 賢也     |
| 37 | 茂泉 優衣     |
| 38 | 舞崎 夫美     |
| 39 | 星永 昌恵     |
| 40 | 横尾 三洲     |
+----+---------------+

mysql> SELECT * FROM blog_categories;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 趣味         |
|  2 | ビジネス     |
|  3 | スポーツ     |
+----+--------------+

mysql> SELECT * FROM blogs;
+----+---------------------------+---------------------------------------------------------------------------------+---------+------------------+
| id | name                      | description                                                                     | user_id | blog_category_id |
+----+---------------------------+---------------------------------------------------------------------------------+---------+------------------+
| 11 | 川手 賢也のブログ         | 川手 賢也のブログです。趣味について書いています。                               |      36 |                1 |
| 12 | 舞崎 夫美のブログ         | 舞崎 夫美のブログです。スポーツについて書いています。                           |      38 |                3 |
| 13 | 星永 昌恵のブログ         | 星永 昌恵のブログです。ビジネスについて書いています。                           |      39 |             NULL |
| 14 | 横尾 三洲のブログ         | 横尾 三洲のブログです。ビジネスについて書いています。                           |      40 |                2 |
+----+---------------------------+---------------------------------------------------------------------------------+---------+------------------+

ユーザID37はブログを持っていません。また、ブログID13にはブログカテゴリーが設定されていません。

内部結合(INNER JOIN句)

上表では内部結合では”結合条件が一致したデータのみを取得する”とのことでした。ここではUserテーブルとBlogテーブルを内部結合させてみます。

mysql> select `users`.id, `users`.name, `blogs`.name, `blogs`.description from users inner join blogs on `users`.id=`blogs`.user_id;
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| id | name          | name                      | description                                                                     |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| 36 | 川手 賢也     | 川手 賢也のブログ         | 川手 賢也のブログです。趣味について書いています。                               |
| 38 | 舞崎 夫美     | 舞崎 夫美のブログ         | 舞崎 夫美のブログです。スポーツについて書いています。                           |
| 39 | 星永 昌恵     | 星永 昌恵のブログ         | 星永 昌恵のブログです。ビジネスについて書いています。                           |
| 40 | 横尾 三洲     | 横尾 三洲のブログ         | 横尾 三洲のブログです。ビジネスについて書いています。                           |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+

なお、赤字の部分が結合処理を行っている部分で、このように両テーブルから結合に利用する任意のカラムを指定し、その値によって結合するかどうかを判断しています。
ユーザID37は管理するブログを持ってませんので取得されませんでした。

外部結合

外部結合は”内部結合と違い、結合条件に一致したデータに加えて”どちらかの”テーブルにしか存在しないデータも取得する”のでした。
従って、外部結合の場合にはデータ取得の基準となる左表・右表という概念がポイントになります。

左表はFROMの後に書かれたテーブルです。LEFT OUTER JOINではこのテーブルが基準になります。
右表はJOINの後に書かれたテーブルです。RIGHT OUTER JOINではこのテーブルが基準になります。

内部結合のときと同様に、UserテーブルとBlogテーブルを結合させてみましょう。
まずは左表(ここではUser)を基準に結合するLEFT OUTER JOIN を実行してみます。

mysql> select `users`.id, `users`.name, `blogs`.name, `blogs`.description from users left outer join blogs on `users`.id=`blogs`.user_id;
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| id | name          | name                      | description                                                                     |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| 36 | 川手 賢也     | 川手 賢也のブログ         | 川手 賢也のブログです。趣味について書いています。                               |
| 37 | 茂泉 優衣     | NULL                      | NULL                                                                            |
| 38 | 舞崎 夫美     | 舞崎 夫美のブログ         | 舞崎 夫美のブログです。スポーツについて書いています。                           |
| 39 | 星永 昌恵     | 星永 昌恵のブログ         | 星永 昌恵のブログです。ビジネスについて書いています。                           |
| 40 | 横尾 三洲     | 横尾 三洲のブログ         | 横尾 三洲のブログです。ビジネスについて書いています。                           |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+

既出の内部結合のSQL実行結果と照らし合わせるとわかりやすいと思います。LEFT OUTER JOINでは管理するブログを持っていないユーザID37のレコードも取得されています。

以下は右表(ここではBlog)を基準に結合するRIGHT OUTER JOIN を実行した結果です。

mysql> select `users`.id, `users`.name, `blogs`.name, `blogs`.description from users right outer join blogs on `users`.id=`blogs`.user_id;
+------+---------------+---------------------------+---------------------------------------------------------------------------------+
| id   | name          | name                      | description                                                                     |
+------+---------------+---------------------------+---------------------------------------------------------------------------------+
|   36 | 川手 賢也     | 川手 賢也のブログ         | 川手 賢也のブログです。趣味について書いています。                               |
|   38 | 舞崎 夫美     | 舞崎 夫美のブログ         | 舞崎 夫美のブログです。スポーツについて書いています。                           |
|   39 | 星永 昌恵     | 星永 昌恵のブログ         | 星永 昌恵のブログです。ビジネスについて書いています。                           |
|   40 | 横尾 三洲     | 横尾 三洲のブログ         | 横尾 三洲のブログです。ビジネスについて書いています。                           |
+------+---------------+---------------------------+---------------------------------------------------------------------------------+

こんどは取得の基準がBlogテーブルになるので、ユーザID37のレコードは結果に含まれません。

複雑な結合

既出のSQLは2つのテーブルを結合させるのもでしたが、もっと多くのテーブルを結合することができます。
そのような場合は、左から順に結合処理が行われてその最終的な結果が出力されます。

mysql> select
    ->   `users`.id, `users`.name, `blogs`.name, `blogs`.description
    -> from users
    ->   inner join blogs on `users`.id=`blogs`.user_id
    ->   inner join blog_categories on `blogs`.blog_category_id=`blog_categories`.id;
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| id | name          | name                      | description                                                                     |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+
| 36 | 川手 賢也     | 川手 賢也のブログ         | 川手 賢也のブログです。趣味について書いています。                               |
| 38 | 舞崎 夫美     | 舞崎 夫美のブログ         | 舞崎 夫美のブログです。スポーツについて書いています。                           |
| 40 | 横尾 三洲     | 横尾 三洲のブログ         | 横尾 三洲のブログです。ビジネスについて書いています。                           |
+----+---------------+---------------------------+---------------------------------------------------------------------------------+

上記では既出の内部結合を行うSQLを実行した後に、さらにBlogCategoryテーブルを内部結合しています。
最初の内部結合で取得されたブログの中から更にカテゴリが設定されていないブログが結合時に省かれています。

なお、MySQLでは最大で61個のテーブルを結合させることができるそうです。

ひとつの結合で参照できるテーブルの最大数は 61 です。これはビューの定義で参照できるテーブルの数と同じです。

MySQL :: MySQL 5.1 リファレンスマニュアル :: D.6.1 結合の制限

参考資料

 - MySQL , ,