MySQL入門 JOINの使い方を解説！内部 外部 複数テーブルの結合まで

皆さんMySQL使っていますか？MySQLといえば関係（リレーショナル）データベース。テーブル同士を関係させてなんぼなデータベースシステムです。

そのために使うのが今回紹介するSQLコマンドのJOINです。この記事ではJOINを使った内部結合、外部結合を紹介し、それらを応用して3つ以上のテーブルをJOINする方法もお伝えします。さっそくいってみましょう！

データベース・テーブルの準備

今回は実際にテーブルを使って操作を学んだほうがわかりやすいため、最初にテーブルを準備します。

以下、新規にデータベース「join_test_db」を作成し、その中に3つのテーブル、「monthly_sales」、「employee_name_entry_year」、「employee_address」を作成します。

イメージとしては小規模な営業所における、1つ目が月間の売上、2つ目が社員の名前と入社年に関するデータ、3つ目が住所のデータといった感じです。これのために必要なSQL文は次のようになります。

CREATE DATABASE join_test_db;
USE join_test_db;

CREATE TABLE monthly_sales(id VARCHAR(5),sales INT,client_id INT);
CREATE TABLE employee_name_entry_year(id VARCHAR(5),name VARCHAR(6),entry_year YEAR);
CREATE TABLE employee_address(id VARCHAR(5),prefecture VARCHAR(3));

INSERT INTO monthly_sales (id,sales,client_id) VALUES
('A103','125','1'),
('A102','50','2'),
('A101','64','4'),
('A104','37','6'),
('A102','285','3'),
('A106','300','6'),
('A103','24','1'),
('A101','350','2'),
('B107','14','1');

INSERT INTO employee_name_entry_year (id,name,entry_year) VALUES
('A101','川越','2008'),
('A102','大宮','2008'),
('A103','浦和','2008'),
('A104','戸田','2010'),
('A105','赤羽','2012'),
('A106','大井','2014');

INSERT INTO employee_address (id,prefecture) VALUES
('A101','千葉'),
('A102','群馬'),
('A103','栃木'),
('A104','茨城'),
('A105','東京'),
('A106','神奈川');

同様のテーブルができたかSELECTで確認してみてください。

内部結合・JOINの基礎

それではJOINの内容に入っていきましょう。まずはJOINを使った内部結合を学びます。

冒頭でもお伝えしましたが、JOINを使った結合には内部結合・外部結合という種類があります。（内部結合の応用として自己結合もありますが、今回は扱いません。）

そもそもとして、MySQLにおける結合はどんな意味かというと、「複数のテーブルを特定のキーを使って結びつけること」をいいます。これだけだと少々わかりにくいので、実際にテーブルを使って処理を見ていきましょう。

1章で作ったテーブルを使っていきますので、まだの方はサクッと用意しておいてください。

はじめてのJOIN

はじめてのJOINということで、「monthly_sales」と「employee_name_entry_yearを」結合します。SQL文はこちら。

SELECT  *
    FROM monthly_sales
JOIN employee_name_entry_year
    ON monthly_sales.id = employee_name_entry_year.id;

うまくいくと次のようなテーブルが表示されます。

+------+-------+-----------+------+--------+------------+
| id   | sales | client_id | id   | name   | entry_year |
+------+-------+-----------+------+--------+------------+
| A103 |   125 |         1 | A103 | 浦和   |       2008 |
| A102 |    50 |         2 | A102 | 大宮   |       2008 |
| A101 |    64 |         4 | A101 | 川越   |       2008 |
| A104 |    37 |         6 | A104 | 戸田   |       2010 |
| A102 |   285 |         3 | A102 | 大宮   |       2008 |
| A106 |   300 |         6 | A106 | 大井   |       2014 |
| A103 |    24 |         1 | A103 | 浦和   |       2008 |
| A101 |   350 |         2 | A101 | 川越   |       2008 |
+------+-------+-----------+------+--------+------------+

これはなにが起きているのかというと、「monthly_sales」テーブルの全カラムの後ろに「employee_name_entry_year」テーブルのデータをidを基準に結合させた、という処理をしています。

言葉にすると若干ややこしいですが、テーブルをみてもらえば、同じidのところには対応する情報が表示されているのがわかるかと思います。これがJOINの内部結合です。SQL文の書き方は次のようになります。

JOINとONの使い方が特徴的ですね。JOINでは2つ以上のテーブルを扱う都合上、カラム名がどのテーブル由来のものかを明示するために「テーブル名.カラム名」という指定の仕方をします。

ここで合わせ技として、テーブル名にエイリアスを付ける方法も紹介します。

エイリアスをテーブル名につける

エイリアスとは別名をつけることができる機能です。エイリアスを使うことでテーブル名やカラム名に別名をつけることが可能になります。エイリアスが活躍するのはテーブル名などが長く複雑なとき。

エイリアス有効に活用すると見た目がスッキリしてわかりやすく、扱いも楽になり便利です。例としてemployee_name_entry_yearテーブルを「社員の名前と入社年」、nameを「名前」、entry_yearを「入社年」に置き換えてみましょう。

SELECT  name AS 名前, entry_year AS 入社年 
FROM employee_name_entry_year AS 社員の名前と入社年;

このようなテーブルが表示されれば成功です。

+--------+-----------+
| 名前   | 入社年    |
+--------+-----------+
| 川越   |      2008 |
| 大宮   |      2008 |
| 浦和   |      2008 |
| 戸田   |      2010 |
| 赤羽   |      2012 |
| 大井   |      2014 |
+--------+-----------+

テーブル名は表示されていないので、この段階ではテーブル名にエイリアスを使う意味は特に見いだせませんよね。ですが、JOINでは事情が変わってきます。テーブル名やカラム名はひと目で何を表すのかがわかることが望ましいです。

ですが、内容をちゃんと書こうとするとどんどんと長大なものになってしまうというジレンマもあります。

長過ぎるテーブル名はSQL文を書くときに正直面倒です。そのため、テーブル名にエイリアスを付けてしまうことで扱いを楽にし、表示もスッキリさせることができます。先ほどのSQL文にエイリアスを設定すると次のようになります。

SELECT  *
    FROM monthly_sales  AS x
JOIN employee_name_entry_year AS y
    ON x.id = y.id;

先ほどよりかなり見通しが良くなりましたね！

USINGでさらにスッキリ

今回の例では社員のIDはすべてのテーブルで「id」というカラム名で統一しています。こういった場合、USINGをつかうことでONの部分を簡単に表記することができます。

SELECT  *
    FROM monthly_sales
JOIN employee_name_entry_year
    USING(id);

こちらもだいぶ見やすいですね。IDのようにカラム名が統一して扱われやすいものではUSINGをつかうことでスッキリと記述することができます。

一方で、テーブルによってカラム名が微妙に違うような状況も考えられます。じつはONにはカラム名が異なっても中身が同じであれば問題ない、という仕様があります。なので、エイリアスを使って簡潔に表現すると良いでしょう。

複数のテーブルをJOINする

ここでは3つ以上のテーブルをJOINする方法をみていきます。といっても、いままでやってきたことがわかればさして難しいものではありません。SQL文の書き方は次のようになります。

では実際にすべてのテーブルを1つにまとめてみましょう。

SELECT x.id, x.sales AS 売上, y.name AS 名前, y.entry_year AS 入社年, z.prefecture AS 出身県
    FROM monthly_sales AS x
JOIN employee_name_entry_year AS y
    USING(id)
JOIN employee_address AS z
    USING(id);

次のようなテーブルは得られましたか？

+------+--------+--------+-----------+-----------+
| id   | 売上   | 名前   | 入社年    | 出身県    |
+------+--------+--------+-----------+-----------+
| A103 |    125 | 浦和   |      2008 | 栃木      |
| A102 |     50 | 大宮   |      2008 | 群馬      |
| A101 |     64 | 川越   |      2008 | 千葉      |
| A104 |     37 | 戸田   |      2010 | 茨城      |
| A102 |    285 | 大宮   |      2008 | 群馬      |
| A106 |    300 | 大井   |      2014 | 神奈川    |
| A103 |     24 | 浦和   |      2008 | 栃木      |
| A101 |    350 | 川越   |      2008 | 千葉      |
+------+--------+--------+-----------+-----------+

外部結合・左と右の違いを理解する

この章では外部結合について学んでいきましょう。

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

まずは内部結合と外部結合の違いについてです。じつはテーブルの要素でいままで意図的に触れてこなかった部分があります。もう気づいていたとしたら、かなり観察力のあるタイプですね。

なにを隠していたのか、次のコマンドをいれてみればわかるはずです。

#月次の売上データ
SELECT  * FROM monthly_sales;

#社員データ
SELECT  * FROM employee_name_entry_year;

#2つのデータを内部結合したテーブル
SELECT  *
    FROM monthly_sales
JOIN employee_name_entry_year
    USING(id);

実行結果

+------+-------+-----------+
| id   | sales | client_id |
+------+-------+-----------+
| A103 |   125 |         1 |
| A102 |    50 |         2 |
| A101 |    64 |         4 |
| A104 |    37 |         6 |
| A102 |   285 |         3 |
| A106 |   300 |         6 |
| A103 |    24 |         1 |
| A101 |   350 |         2 |
| B107 |    14 |         1 |
+------+-------+-----------+

+------+--------+------------+
| id   | name   | entry_year |
+------+--------+------------+
| A101 | 川越   |       2008 |
| A102 | 大宮   |       2008 |
| A103 | 浦和   |       2008 |
| A104 | 戸田   |       2010 |
| A105 | 赤羽   |       2012 |
| A106 | 大井   |       2014 |
+------+--------+------------+

+------+-------+-----------+--------+------------+
| id   | sales | client_id | name   | entry_year |
+------+-------+-----------+--------+------------+
| A103 |   125 |         1 | 浦和   |       2008 |
| A102 |    50 |         2 | 大宮   |       2008 |
| A101 |    64 |         4 | 川越   |       2008 |
| A104 |    37 |         6 | 戸田   |       2010 |
| A102 |   285 |         3 | 大宮   |       2008 |
| A106 |   300 |         6 | 大井   |       2014 |
| A103 |    24 |         1 | 浦和   |       2008 |
| A101 |   350 |         2 | 川越   |       2008 |
+------+-------+-----------+--------+------------+

月次の売上テーブルと内部結合で作ったテーブルを比較してみると、売上テーブルの一番下にいるB107さんが内部結合の表には現れていないのです。これは売上テーブルに結合させた社員の名前と入社年テーブルにB107さんが載っていないためです。

イメージとしてB107さんは外部からの出向で来ている人でしょうか。また、同様に内部結合させたテーブルにはA105の赤羽さんも載っていません。こちらはさっきとは逆に社員のデータには載っているけど、売上テーブルに載っていないためです。

イメージとしては営業ではなく事務の方なのでしょう。このように、内部結合では結合させるテーブルの両方にレコードが存在しないと、結合後のテーブルに表示されないのです。ですが、それでは問題が生じる場面もあることでしょう。

社員の名前をとりあえず全員載せたかったり、売上がない人でも同じテーブルにまとめたかったり、などなど。そんなときに使えるのが外部結合なのです。次の節では外部結合の具体的な使い方を見ていきます。

左外部結合

この節では左外部結合について説明します。外部結合には2種類あり、左外部結合と右外部結合といいます。何が左・右なのかというと、全データを表示する範囲を左側にするか、右側にするかということです。

今ひとつ意味がつかみにくいので実際のSQL文をみて、動かしながら考えてみましょう。売上データのIDと社員データの名前を結合するSQL文は次のようになります。

SELECT x.id, y.name AS 名前
    FROM monthly_sales AS x
LEFT JOIN employee_name_entry_year AS y
    USING(id)

ここから次のようなテーブルが作られます。

+------+--------+
| id   | 名前   |
+------+--------+
| A101 | 川越   |
| A101 | 川越   |
| A102 | 大宮   |
| A102 | 大宮   |
| A103 | 浦和   |
| A103 | 浦和   |
| A104 | 戸田   |
| A106 | 大井   |
| B107 | NULL   |
+------+--------+

以前と異なり、B107さんがテーブルに載っていますね。社員データの方に名前が乗っていないため、名前はNULLになっています。左外部結合のSQL文は内部結合のときにJOINだった部分がLEFT JOINに変わっています。

それ以外はまったく同じなのです。左外部結合の書式は次のようになります。

内部結合の時とほとんど変わらないのが実感できますでしょうか。左外部結合の特徴的な考え方はFROMにセットするテーブルを左側、LEFT JOINにセットするテーブルを右側と考えて、左側のすべての要素を表示するようにテーブルを作ります。

その結果、左側の売上テーブルにあったすべてのレコードに社員データから名前が対応されて、社員データのテーブルに存在しないB107さんにはNULLが割り当てられた、ということなわけです。

右外部結合

右外部結合は左外部結合とほとんど同じ考え方で理解できます。書式はこの通り。

左外部結合で使った例を右外部結合にすると、次のようになります。

SELECT x.id, y.name AS 名前
    FROM monthly_sales AS x
RIGHT JOIN employee_name_entry_year AS y
    USING(id);

LEFTがRIGHTに変わっただけですね。これを実行すると、次のようなテーブルを得ます。

+------+--------+
| id   | 名前   |
+------+--------+
| A103 | 浦和   |
| A102 | 大宮   |
| A101 | 川越   |
| A104 | 戸田   |
| A102 | 大宮   |
| A106 | 大井   |
| A103 | 浦和   |
| A101 | 川越   |
| NULL | 赤羽   |
+------+--------+

左外部結合のときとは逆に、売上テーブルには載っていなかった赤羽さんが右外部結合をすることで載るようになりました。

ただ、idは売上テーブルのデータを参照しているため、本来あるはずのid、A105がNULLになってしまっています。売上テーブルには赤羽さんの売上レコードがないため、idが存在せずNULLになってしまうのです。

SELECTで使うidをy.id、つまり社員データのものにすればこの問題は解決できます。このように、カラムの選び方で本来あるはずのデータが表示されない問題はしばしばあります。

実際に使う時はどのテーブルのどのカラムを使うのか、しっかり検討した上で外部結合は行ってください。

左外部結合と右外部結合の互換性

ここまで読んでいただいて、次のようなことを考えませんでしたか？「左外部結合と右外部結合はほとんどやっていることが同じなのだから、FROMとJOIN部分を入れ替えれば同じことができるのでは？」

この指摘はまさにその通りで、例えば右外部結合は次のように左外部結合で書き換えることができます。

SELECT x.id, y.name AS 名前
    FROM employee_name_entry_year AS y
LEFT JOIN monthly_sales AS x
    USING(id);

これは左外部結合のSQL文に対して、FROMとRIGHT JOINの中身を入れ替え、RIGHTをLEFTに書き換えただけのものになります。実行すると、このようなテーブルを得ます。

+------+--------+
| id   | 名前   |
+------+--------+
| A103 | 浦和   |
| A102 | 大宮   |
| A101 | 川越   |
| A104 | 戸田   |
| A102 | 大宮   |
| A106 | 大井   |
| A103 | 浦和   |
| A101 | 川越   |
| NULL | 赤羽   |
+------+--------+

少し上にスクロールしていただくと、これとまったく同じテーブルを右外部結合の説明に見つけることができるでしょう。つまり、左外部結合と右外部結合は入れ替えることができるのです。

むしろ、左外部結合と右外部結合が混在するような処理は後の混乱を招く恐れがあるため、現場では嫌われています。多くの現場では外部結合について、どちらかを使用すること、という規約があるはずです。

自分で1からデータベースを作る機会があったら、外部結合はどちらかしか使わない、という設計上のテクニックをぜひ思い出してください。

まとめ

いかがでしたか？今回はJOINを使った内部結合と外部結合について解説しました。RDBMSでは大きな1つのテーブルですべてを管理する、ということはあまりありません。

世の中のほとんどすべてのデータベースが複数のテーブルを管理しつつ、ときに結合させながら必要なデータを管理しています。

MySQLはYahoo！やFacebook、Twitterなどでも利用されており、これらの大企業がやるような操作を1つできるようになったと思うとなんだかワクワクしませんか？MySQLは1つ1つの積み重ねでどんどん上達することができます。

ぜひ一歩一歩前に進んでいきましょう。この記事が皆様のMySQL学習の一助となれば幸いです。