MySQLに置いてテーブル同士の結合方法を解説していきます。

用語解説

テーブル名：nameList

id	name	age	addressId
1	Abe	42	3
2	Ide	31	2
3	Hide	24	3
4	Ide	39	1

テーブル

表の事。(上記なら nameList というテーブル名。)

レコード

表の行の事。(上記なら4つのレコードが存在する。)

カラム

表の列の事。(上記なら4つのカラムが存在する。)

テーブル結合について

※ちょっと小難しい話になりますので、飛ばしても構いません。
読む場合も「そうなんだ〜」程度に留めてもらって大丈夫です。

正規化

まずデータベースを設計する段階で、カラムに重複する情報がある場合、そのカラムを別のテーブルに分けるという事をし、テーブルが保持するデータ量の削減と処理の高速化を図ります。これを正規化と言います。

非正規化

正規化のようにテーブルを分けて、扱うデータ量を削減するのに対して、重複する値があってもテーブルを分けずに、１つのテーブルでデータをまとめてしまうという事。これが非正規化になります。

テーブル結合の意味

正規化されたデータは処理速度を早めるのに対して、まとまったデータの解析や抽出などには向いておりません。その為、データの解析や抽出を行う際は、あえて非正規化の状態に戻して、扱いやすいテーブルの構造にします。

これがテーブル結合をする意味になります。

この記事で使用するテーブル

これ以降は指定がない限り、同様のテーブルを利用します。

テーブル名：nameList

id	name	age	addressId
1	Abe	42	3
2	Ide	31	2
3	Hide	24	3
4	Ide	39	1

テーブル名：addressList

id	address
1	Tokyo
2	Osaka
3	Aichi
4	Fukuoka

INNER JOIN(内部結合)

内部結合とは、２つのテーブルで共通のデータが存在しているレコードのみ抽出を行います。
実際のコード例はこちら。

SELECT 
    *
FROM
    nameList 
        INNER JOIN
    addressList ON nameList.addressId = addressList.id;

これだけだとわかりにくいと思うので、順を追って解説していきます。

①まず元となるテーブル名をFROMで記述。( nameList )
②その後ろにINNER JOINを記述。
③結合したいテーブル名を記述。( addressList )
④その後ろにONを記述。
⑤同じ情報が入ったカラムをイコールで繋ぐ。

SELECT内に複数のテーブルを指定する事になるので、カラムを指定する際はどのテーブルのカラムを言っているのか、テーブル名で指定してやる必要があります。(上記なら nameList.addressId と addressList.id です)

また、INNERは省略して書く事もできますが、他の人が見た際にわかりやすくするために、明示的に記述する事が多いです。

上記の出力結果はこちら。

id	name	age	addressId	id	address
4	Ide	39	1	1	Tokyo
2	Ide	31	2	2	Osaka
1	Abe	42	3	3	Aichi
3	Hide	24	3	3	Aichi

nameListテーブル が addressIdカラム 順に並び、隣にあるaddressテーブルのidカラムが対応しているというのが特徴です。

OUTER JOIN(外部結合)

外部結合とは、２つのテーブルで共通のデータが存在しているレコードと、結合させたテーブルでデータが存在しないレコードはNULLとして抽出を行います。

また、外部結合にはRIGHTとLEFTの２種類の結合方法があります。違いは、左右のテーブルの並び順です。

RIGHT JOIN

RIGHT JOINで結合したコードはこちらです。

SELECT 
    *
FROM
    nameList 
        RIGHT JOIN
    addressList ON nameList.addressId = addressList.id;

簡単にいうと、先ほどのINNER JOINの部分をRIGHT JOINに書き換えるだけです。

上記の出力結果はこちら。

id	name	age	addressId	id	address
1	Abe	42	3	3	Aichi
2	Ide	31	2	2	Osaka
3	Hide	24	3	3	Aichi
4	Ide	39	1	1	Tokyo
NULL	NULL	NULL	NULL	4	Fukuoka

基本的にはINNER JOINと同じですが、RIGHT JOINの場合は nameListテーブル の addressIdカラム に、 addressListテーブル の idカラム と対応する値が無い場合、最後のレコードのようにNULL値を出力してくれるという点が違いです。

LEFT JOIN

LEFT JOINで結合したコードはこちらです。

SELECT 
    *
FROM
    nameList 
        LEFT JOIN
    addressList ON nameList.addressId = addressList.id;

先程と同じで、INNER JOINの部分をLEFT JOINに書き換えるだけです。

上記の出力結果はこちら。

id	name	age	addressId	id	address
4	Ide	39	1	1	Tokyo
2	Ide	31	2	2	Osaka
1	Abe	42	3	3	Aichi
3	Hide	24	3	3	Aichi

RIGHT JOINは右側にあるテーブルをベースに、左側のテーブルに無い値をNULL値として出力。
LEFT JOINは左側にあるテーブルをベースに、右側のテーブルに無い値をNULL値とします。

今回の例でいうと、左側にある addressListテーブル の idカラム に、 nameListテーブル の addressIdカラム で対応する値が全てあるので、NULL値の出力はありません。

そのため、こういう場合はINNER JOINと同様の結果になります。

CROSS JOIN(掛け合わせ)

CROSS JOINとは、左右のテーブルで組み合わせ可能な全てのレコードを抽出する事ができます。例えば片方のテーブルで６件のレコード、もう片方のテーブルで４件のレコードが存在する場合、２４件のレコードからなるテーブルが出力されます。

言葉ではわかりにくいと思うので、実際のコードから見ていきましょう。

SELECT 
    *
FROM
    nameList 
        CROSS JOIN
    addressList;

これまで紹介してきた結合と違うのは、CROSS JOINの後に結合したいテーブルを記述した後、それぞれのテーブルで対応するカラム同士の記述が必要ないという点です。

上記の出力結果はこちら。

id	name	age	addressId	id	address
1	Abe	42	3	1	Tokyo
2	Ide	31	2	1	Tokyo
3	Hide	24	3	1	Tokyo
4	Ide	39	1	1	Tokyo
1	Abe	42	3	2	Osaka
2	Ide	31	2	2	Osaka
3	Hide	24	3	2	Osaka
4	Ide	39	1	2	Osaka
1	Abe	42	3	3	Aichi
2	Ide	31	2	3	Aichi
3	Hide	24	3	3	Aichi
4	Ide	39	1	3	Aichi
1	Abe	42	3	4	Fukuoka
2	Ide	31	2	4	Fukuoka
3	Hide	24	3	4	Fukuoka
4	Ide	39	1	4	Fukuoka

nameListテーブル のレコードは４件、 addressListテーブル のレコードも４件。したがって、全ての組み合わせを表示すると、

4レコード * 4レコード = 16レコード

という出力結果になります。

補足

因みにCROSS JOINで、それぞれのテーブルで対応するカラム同士の記述をした場合は、INNER JOINと同じ出力結果になります。
実際に記述してみた例がこちら。

SELECT 
    *
FROM
    nameList 
        CROSS JOIN
    addressList ON nameList.addressId = addressList.id;

上記を実行すると、下記の出力結果のように、`INNER JOIN`で内部結合した結果と同じになります。

id	name	age	addressId	id	address
4	Ide	39	1	1	Tokyo
2	Ide	31	2	2	Osaka
1	Abe	42	3	3	Aichi
3	Hide	24	3	3	Aichi

SELF JOIN(同じテーブルの結合)

SELF JOINとは、同じテーブル同士を連結させて抽出する事ができます。

こちらでは、以下のテーブルを利用します。
(住所ではなく家族関係を示すカラムとして、 addressIdカラム を familyIdカラム に変更。)

テーブル名：nameList

id	name	age	familyId
1	Abe	42	3
2	Ide	31	2
3	Hide	24	3
4	Ide	39	1

ではSELF JOINする際の、実際のコードはこちら。

SELECT 
    *
FROM
    nameList nL1
        JOIN
    nameList nL2 ON nL1.familyId = nL2.id;

SELF JOINというクエリは無いので、代わりにJOIN(INNER JOIN)を利用します。ON以下でカラム同士の関係を表す際は、どちらのテーブルのカラムを指しているのか指定するために、テーブル名を記述した後にエイリアス(別名設定)を付けています。

では上記の出力結果はこちら。

id	name	age	familyId	id	name	age	addressId
4	Ide	39	1	1	Abe	42	3
2	Ide	31	2	2	Ide	31	2
1	Abe	42	3	3	Hide	24	3
3	Hide	24	3	3	Hide	24	3

このように、同じテーブル同士のテーブル結合もできます。

ON以下でカラム同士の関係を逆にすると、左右のテーブルが入れ替わり、なんの為のテーブル結合かわかりにくくなるので、注意が必要です。

３つ以上のテーブル結合

３つ以上のテーブルを結合する場合は、テーブルの数だけJOINの記述するというやり方になります。

例えば、何らかの理由で nameListテーブル に addressListテーブル を結合し、さらに nameListテーブル を結合するとします。その際のコードがこちら。

SELECT 
	*
FROM
    nameList nL1
        INNER JOIN
    addressList aL ON nL1.id = aL.id
        INNER JOIN
    nameList nL2 ON aL.id = nL2.id;

上記の出力結果はこちら。

id	name	age	addressId	id	address	id	name	age	addressId
1	Abe	42	3	1	Tokyo	1	Abe	42	3
2	Ide	31	2	2	Osaka	2	Ide	31	2
3	Hide	24	3	3	Aichi	3	Hide	24	3
4	Ide	39	1	4	Fukuoka	4	Ide	39	1

このように、３つ以上のテーブルの結合もできます。

ここでは省略しますが、RIGHT JOINやLEFT JOINなどと組み合わせて、利用する事もできます。

UNION(縦結合)

今まで解説してきたテーブルは、全て横に結合するものでしたが、UNIONを利用すればテーブル同士を縦に結合して抽出する事が可能です。

また縦の結合には、UNIONとUNION ALLという２種類の結合方法があります。違いは、重複しているレコードを抽出するかしないかです。

UNION

まずは実際のコードを見ていきましょう。

SELECT 
    *
FROM
    nameList
WHERE
    id >= 3
UNION SELECT 
    *
FROM
    nameList
WHERE
    id <= 2;

特徴的なのは、SELECTで作成したテーブル同士を、UNIONで記述ごと結合させている部分です。

解説すると、UNIONの前にある記述は、 nameListテーブル から idカラム が３以上のレコードを取得。

id	name	age	addressId
3	Hide	24	3
4	Ide	39	1

UNIONの後にある記述は、 nameListテーブル から idカラム が２以下のレコードを取得。

id	name	age	addressId
1	Abe	42	3
2	Ide	31	2

そして、上記２つのテーブルを縦に結合するという流れです。
実際の出力結果はこちらです。

id	name	age	addressId
3	Hide	24	3
4	Ide	39	1
1	Abe	42	3
2	Ide	31	2

また、UNIONには重複したレコードは出力しない、という特徴があります。
例えば以下のコード。

SELECT 
    *
FROM
    nameList
WHERE
    id <= 3
UNION SELECT 
    *
FROM
    nameList
WHERE
    id <= 2;

こちらの出力結果はこのようになります。

id	name	age	addressId
1	Abe	42	3
2	Ide	31	2
3	Hide	24	3

さらに、ここでは紹介しませんが、別のテーブル同士での結合も可能です。
その際にカラムとして出力されるのは、同じカラム名とデータ型を持ったカラムのみになります。

UNION ALL

UNIONは重複するレコードを削除するのに対して、UNION ALLは重複するレコードも抽出を行います。
例えば、先ほどのコードをUNION ALLに変えたのがこちら。

SELECT 
    *
FROM
    nameList
WHERE
    id <= 3
UNION ALL SELECT 
    *
FROM
    nameList
WHERE
    id <= 2;

特徴としては、UNIONの後にALLを付け加えただけです。

上記の出力結果がこちらです。

id	name	age	addressId
1	Abe	42	3
2	Ide	31	2
3	Hide	24	3
1	Abe	42	3
2	Ide	31	2

このようにUNION ALLでは、重複したレコードも抽出を行うことが可能です。
その他の機能や注意事項は、UNIONで解説したものと同様です。

まとめ

以上がMySQLにおける、テーブルの結合方法になります。

ある程度使いこなせるようになれば、さらにWHEREやGROUP BY、ORDER BYなどと組み合わせて、自由自在にデータの抽出が行えるようになります。

また興味のある方は実際にMySQLをインストールして、MySQL WorkbenchというGUIアプリを利用すれば、簡単に同じ事ができるので、ぜひ試してみてください。

最後まで読んでいただき、ありがとうございました！

筆者：yuki｜学習10日目で初案件獲得→現在はフルスタックエンジニア転職に向けて学習中
Qiita：https://qiita.com/yuki4839
Twitter：https://twitter.com/yuki35522891