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