ここではテーブルの結合について学習します。SQLでは複数のテーブルを結合して結果を出力できるようになっています。テーブルの結合方法にはいくつかの種類がありますが、一般的によく使われるのは内部結合と外部結合の2つです。ここでは内部結合と外部結合の1つのやり方である左外部結合について取り上げます。

(補足)テーブルの確認

ここでは categories テーブルと courses テーブルの2つのテーブルを結合する様子を確認します。以下のとおりSQLを実行してレコードが出力されることを確認してください。

MariaDB [eldb]> select * from categories;
+----+-------------+
| id | title       |
+----+-------------+
|  1 | Programming |
|  2 | Design      |
|  3 | Marketing   |
+----+-------------+
3 rows in set (0.05 sec)

MariaDB [eldb]> select * from courses;
+----+-----------------+---------------+-------------+
| id | title           | learning_time | category_id |
+----+-----------------+---------------+-------------+
|  1 | PHP Basic       |            30 |           1 |
|  2 | PHP Database    |            50 |           1 |
|  3 | Python Basic    |            40 |           1 |
|  4 | Web Design      |            50 |           2 |
|  5 | Japan's History |           100 |        NULL |
+----+-----------------+---------------+-------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

内部結合( inner join

テーブルを結合する方法を確認していきましょう。まずはじめに内部結合を取り上げます。内部結合は inner join を使います。また結合する列を指定するために on 句を合わせて指定します。

テーブル名A inner join テーブル名B on テーブルAの列名 = テーブルBの列名

内部結合では、2つのテーブルに共通するレコードのみが結果として出力されます。次のSQLでは courses テーブルと categories テーブルの2つを内部結合しています。また結合時の条件( on 句)として、courses テーブルの category_id 列と categories テーブルの id 列を指定しています。これら2つの列の値の等しいものが結合されます。

MariaDB [eldb]> select * from courses co 
    ->          inner join categories ca on co.category_id = ca.id;         
+----+--------------+---------------+-------------+----+-------------+
| id | title        | learning_time | category_id | id | title       |
+----+--------------+---------------+-------------+----+-------------+
|  1 | PHP Basic    |            30 |           1 |  1 | Programming |
|  2 | PHP Database |            50 |           1 |  1 | Programming |
|  3 | Python Basic |            40 |           1 |  1 | Programming |
|  4 | Web Design   |            50 |           2 |  2 | Design      |
+----+--------------+---------------+-------------+----+-------------+
4 rows in set (0.02 sec)

MariaDB [eldb]> 

上記のSQLでは courses テーブルに co という別名(エイリアス)を付けています。同様に categories テーブルには ca という別名を付けています。SQLにおいてテーブルを結合すると、テーブル名を記述する機会が増えるので短い別名を付けるとコードが記述しやすくなります。また列名にアクセスするときには co.category_id のように テーブル名.列名 のようにアクセスできます。2つのテーブルに重複する列名が存在するときには テーブル名.列名 と指定する必要があります。

出力内容についても確認しておきましょう。内部結合によって結果が4行出力されている点に注意してください。 courses テーブルの ID=5 のレコードは category_idNULL であるため category テーブルのレコードと結合できません。そのため結果に出力されなくなります。

左外部結合( left outer join

続いてテーブルを結合する方法を確認していきましょう。次は外部結合を取り上げます。外部結合は、詳細に分けると左外部結合( left outer join )、右外部結合( right outer join )、完全外部結合( full outer join )の3種類が存在します。左外部結合の仕組みを理解すれば、右外部結合や完全外部結合を理解することは容易です。またデータベースプロダクトによっては右外部結合や完全外部結合をサポートしていないものも存在します。MySQLの場合は左外部結合と右外部結合をサポートしています。開発現場では一般的に左外部結合を使うことが多いです。ここでは左外部結合を中心に学習を進めます。

左外部結合の構文は内部結合とよく似ています。

テーブル名A left outer join テーブル名B on テーブルAの列名 = テーブルBの列名

inner join の部分を left outer join とすれば左外部結合になります。また left join のように outer というキーワードを省略しても動作します。

右外部結合は right outer join、 完全外部結合は full outer join と記述します。これらも短く right joinfull join と記述できます。

次のSQLでは courses テーブルと categories テーブルの2つを左外部結合しています。また結合時の条件( on 句)として、courses テーブルの category_id 列と categories テーブルの id 列を指定しています。左外部結合の場合も、これら2つの列の値の等しいものが結合されますが、1つ目のテーブル(この場合 courses テーブル)のレコードは結合先が存在しないものもすべて出力されます。

MariaDB [eldb]> select * from courses co 
    ->          left outer join categories ca on co.category_id = ca.id;
+----+-----------------+---------------+-------------+------+-------------+
| id | title           | learning_time | category_id | id   | title       |
+----+-----------------+---------------+-------------+------+-------------+
|  1 | PHP Basic       |            30 |           1 |    1 | Programming |
|  2 | PHP Database    |            50 |           1 |    1 | Programming |
|  3 | Python Basic    |            40 |           1 |    1 | Programming |
|  4 | Web Design      |            50 |           2 |    2 | Design      |
|  5 | Japan's History |           100 |        NULL | NULL | NULL        |
+----+-----------------+---------------+-------------+------+-------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

SQLの実行結果に courses テーブルの id5 のレコードが出力されている点に注意してください。左外部結合を行ったため、courses テーブルのレコードは結合先が存在しないものであっても結果に出力されます。このとき結合先のテーブルから取得する項目は null となります。

(参考) courses テーブルと categories テーブルを入れ替えた場合

さきほどの左外部結合のSQLについて、テーブル名の記述順序を入れ替えてみましょう。次のSQLは categories テーブルを基準に courses テーブルを左外部結合しています。

MariaDB [eldb]> select * from categories ca 
    ->          left outer join courses co on ca.id = co.category_id;
+----+-------------+------+--------------+---------------+-------------+
| id | title       | id   | title        | learning_time | category_id |
+----+-------------+------+--------------+---------------+-------------+
|  1 | Programming |    1 | PHP Basic    |            30 |           1 |
|  1 | Programming |    2 | PHP Database |            50 |           1 |
|  1 | Programming |    3 | Python Basic |            40 |           1 |
|  2 | Design      |    4 | Web Design   |            50 |           2 |
|  3 | Marketing   | NULL | NULL         |          NULL |        NULL |
+----+-------------+------+--------------+---------------+-------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

SQLの実行結果に categories テーブルの id3 のレコードが出力されている点に注意してください。左外部結合を行ったため、categories テーブルのレコードは結合先が存在しないものであっても結果に出力されます。このとき結合先のテーブルから取得する項目は null となります。

左外部結合時のテーブル名を入れ替えると右外部結合の結果と一致します。ここでは右外部結合 right outer join の結果も確認してみましょう。

MariaDB [eldb]> select * from courses co 
    ->          right outer join categories ca on co.category_id = ca.id;
+------+--------------+---------------+-------------+----+-------------+
| id   | title        | learning_time | category_id | id | title       |
+------+--------------+---------------+-------------+----+-------------+
|    1 | PHP Basic    |            30 |           1 |  1 | Programming |
|    2 | PHP Database |            50 |           1 |  1 | Programming |
|    3 | Python Basic |            40 |           1 |  1 | Programming |
|    4 | Web Design   |            50 |           2 |  2 | Design      |
| NULL | NULL         |          NULL |        NULL |  3 | Marketing   |
+------+--------------+---------------+-------------+----+-------------+
5 rows in set (0.00 sec)

MariaDB [eldb]> 

実行結果は列の表示順序は異なりますが、1つ前のSQLと同じ内容が出力されているのがわかります。このように左外部結合を使っても右外部結合を使ってもテーブルの指定順序を工夫すれば同じ結果にアクセスできます。