【SQL】外部結合でテーブルを結合させる。OUTER JOIN句について詳しく解説。

大きなシステムになればなるほど、ビッグデータの中から効率よく情報を取得しないとサーバーの負荷が顕著になるため、データべースを扱う上で外部結合(複数テーブルを結合する事)は避けては通れない道でもあります。

この外部結合にはいくつかありますが、最も基本的でありよく使われるものは「LEFT OUTER JOIN(左外部結合)」句です。

そこで今回はこのLEFT OUTER JOIN句について解説してみたいと思います。

最初は若干慣れないかもしれませんが、そんなに難しく考える必要もありませんので、最後までじっくりと目を通してみてください。

外部結合とは

まず初めに、外部結合についての基本的知識について解説をしてみたいと思います。

外部結合とは2つのデータベーステーブルのカラム同士を紐づけ、結合されたテーブルの情報を取得するというものです。

SQL句を少しでも知っている方はこの他にも「INNER JOIN(内部結合)」というSQL句をご存じだと思いますが、この二つには根本的な違いがあります。

次はこの二つの句について、もう少し掘り下げて説明をしていきます。

内部結合と外部結合の違い

データベースに存在する２つのテーブルを結合させ、テーブル間のデータをひとまとめにした情報を取得するという意味ではどちらも同じです。

ただし、INNER JOIN(内部結合)は一致しないデータは取得しません。

逆にOUTER JOIN(外部結合)は一致していない場合もデータが存在する限り全て取得します。

文章だけだと非常に解り難いと思いますので、以下ではここまでしてきた説明を解りやすく図にしてみました。

上の図は、2つの異なるデータベーステーブルを外部結合しています。

結合条件は左側テーブルの「Group ID」と右側テーブルの「ID」の値が同一のデータです。

この図では、「IDが3」のイワシには4というGroup IDが存在していません。

にも拘わらず、下側のテーブルではID3のレコードが存在しています。

これが外部結合と内部結合の大きな違いです。

以下は、外部結合についてのより詳しい説明です。

LEFT OUTER JOIN句の使い方

LEFT OUTER JOIN句は、SELECT文と共に使用することで2つのテーブルを結合させ、結合されたテーブルからデータを取得することが出来ます。

記述は次の通りです。

SELECT テーブル名.カラム名, ... FROM テーブル名1
  LEFT OUTER JOIN テーブル名2
    ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

SELECT文で取得するデータは結合テーブルから取得することになります。

よって、取得するカラムがどちらのテーブルのどのカラムなのかを明確にする必要があるため、「テーブル名.カラム名」となるように指定します。

また左外部結合の特徴である「左側テーブルにしかないデータも取得する」という部分ですが、この左側テーブルはFROMの後に記述するテーブルを指します。

ONの後の記述は結合条件であり、左右のテーブルのどのカラムを対象とするかを「テーブル名.カラム名」の形式で指定します。

実際に使ってみる

それでは早速サンプルテーブルでLEFT OUTER JOIN句を使ってみましょう。

今回はfood(テーブルA)とtype(テーブルB)の二つのサンプルテーブルを使用します。

foodは上の図でいう左側テーブル、typeは右側テーブルで、それぞれに同じ内容を予め格納しています。

MariaDB [sample]> SELECT * FROM food;
+----+------------+----------+
| id | name       | group_id |
+----+------------+----------+
|  1 | Cabbage    |        1 |
|  2 | Strawberry |        2 |
|  3 | Sardines   |        4 |
|  4 | Lettuce    |        1 |
|  5 | Beef       |        3 |
|  6 | Orange     |        2 |
+----+------------+----------+
6 rows in set (0.000 sec)
 
MariaDB [sample]> SELECT * FROM type;
+----+------------+
| id | group_name |
+----+------------+
|  1 | Vegetables |
|  2 | Fruit      |
|  3 | Meat       |
+----+------------+
3 rows in set (0.000 sec)

先ほど記述の説明をした通りにSQLで実行してみます。

まずは基本通りに外部結合させて全てのデータを取得します。

MariaDB [sample]> SELECT * FROM food
    -> LEFT OUTER JOIN type
    -> ON food.group_id = type.id;
+----+------------+----------+------+------------+
| id | name       | group_id | id   | group_name |
+----+------------+----------+------+------------+
|  1 | Cabbage    |        1 |    1 | Vegetables |
|  2 | Strawberry |        2 |    2 | Fruit      |
|  3 | Sardines   |        4 | NULL | NULL       |
|  4 | Lettuce    |        1 |    1 | Vegetables |
|  5 | Beef       |        3 |    3 | Meat       |
|  6 | Orange     |        2 |    2 | Fruit      |
+----+------------+----------+------+------------+
6 rows in set (0.001 sec)

期待通りに結合テーブルから全てのデータが取得できました。

id3のレコードは右側テーブルのidとgroup_idが「null」となっていますが、これは左外部結合の特徴が「左側テーブルにしかないデータも取得する」となっているため、整合性を保つために該当なし部分に関してはnullで処理されるためです。

確認のため、今度は参照させるテーブルを反対にしてみましょう。

MariaDB [sample]> SELECT * FROM type
    -> LEFT OUTER JOIN food
    -> ON type.id = food.group_id;
+----+------------+------+------------+----------+
| id | group_name | id   | name       | group_id |
+----+------------+------+------------+----------+
|  1 | Vegetables |    1 | Cabbage    |        1 |
|  2 | Fruit      |    2 | Strawberry |        2 |
|  1 | Vegetables |    4 | Lettuce    |        1 |
|  3 | Meat       |    5 | Beef       |        3 |
|  2 | Fruit      |    6 | Orange     |        2 |
+----+------------+------+------------+----------+
5 rows in set (0.001 sec)

先ほどと一緒でSQL句は変わらず、左外部結合であるLEFT OUTER JOINを使用しテーブルだけを逆にしたため、イワシのレコードが消えていることが確認できます。

これは左側テーブルとして指定したtypeテーブルに魚類のレコードが存在しないため、結合時にイワシのレコードのみが弾かれたためだといえます。

このようにテーブルの順番で結果が全く異なってくるのが外部結合です。

カラム指定で取得する

基本的な取得方法は一緒ですが、カラムを指定する場合には記述を減らすことが出来る場合があります。

それは「左右どちらかのテーブルにしか存在しないカラムを指定する場合」です。

先ほどのテーブルを使って実際に見てみましょう。

MariaDB [sample]> SELECT name, group_name FROM food
    -> LEFT OUTER JOIN type
    -> ON food.group_id = type.id;
+------------+------------+
| name       | group_name |
+------------+------------+
| Cabbage    | Vegetables |
| Strawberry | Fruit      |
| Sardines   | NULL       |
| Lettuce    | Vegetables |
| Beef       | Meat       |
| Orange     | Fruit      |
+------------+------------+
6 rows in set (0.001 sec)

“name”、”group_name”ともにどちらかのテーブルにしか存在しないカラム名のため、テーブル名を省略しても問題なく実行されました。

なお、どちらのテーブルにも存在するカラム名を指定してしまうと以下のように”field list is ambiguous”というエラーが返ってきます。

MariaDB [sample]> SELECT id FROM food
    -> LEFT OUTER JOIN type
    -> ON food.group_id = type.id;
ERROR 1052 (23000): Column 'id' in field list is ambiguous

まとめ

いかがでしたか？

今回はSQLのLEFT OUTER JOIN句について解説してみました。

テーブル結合はサーバーを扱う場合には必ず使用する命令句といっても過言ではないため、しっかりと使いこなせるまでこの記事を読んで練習を繰り返しましょう。