はじめに
テーブル結合の動作についてまとめてみました！
ログを見た時に、何してるか分かるようになることを今回の目的とします。
本記事は、全て動作済みです。
DBMS: Mysql
##テーブル結合の挙動について
まずはテーブルの結合がどのように行われているのか見て行きましょう。
結合にはJOIN句が使われます。
使い方
SELECT *
FROM 結合元のテーブル名(左)
JOIN 結合先のテーブル名(右)
ON 両テーブルの結合条件
JOINで結合先のテーブルを指定し、ONより後ろに、どの条件で結合するのかを記述します。
例として以下のusersテーブルとpostsテーブルがあるとします。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
それではこの二つのテーブルを結合するために以下のsqlを発行して見ましょう。
SELECT *
FROM users -- 結合元の左テーブル
JOIN posts -- 結合先の右テーブル
ON users.id = posts.user_id -- 左テーブルのidと右テーブルのuser_idが一致するもの
流れは以下の通り。
-
usersテーブルの右にpostsテーブルを結合することを宣言。(JOIN) -
usersテーブルのidと、postsテーブルのuser_idが一致するものを結合する(ON) -
usersテーブルとpostsテーブルを結合した状態で全カラムを取得。
つまり上のSQLでは、**「usersテーブルのidと、postsテーブルのuser_idが一致した状態で全カラム取得してください！」**っという命令になります。
よって、結果は以下の通りです↓
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 3 | hanako | 1 | Hello | 3 |
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
+------+--------+------+-------+---------+
usersテーブルのidと、postsテーブルのuser_idが一致していることがお分かりでしょうか。
今回は全カラムを取得していましたが、結合済みの場合、結合先のテーブルからも取得することができます。
SELECT users.name, posts.body -- 変更箇所
FROM users
JOIN posts
ON users.id = posts.user_id;
+--------+-------+
| name | body |
+--------+-------+
| hanako | Hello |
| taro | Hi |
| jiro | Good |
+--------+-------+
これで誰が何を投稿したのか一目瞭然ですね！
FROMの次がusersになってるため、**「usersテーブルからしか取得できないのでは？」**と思うかもしれませんが、postsとusersが結合された状態から取得することができます。
ここからは先ほど使っていたJOIN(INNER JOIN)の挙動について解説して行きます。
##INNER JOIN(内部結合)
JOINは先ほど使っていたもので、内部結合をします。
補足: JOIN と INNER JOINは同じです！
【特徴】
1. 右テーブルの行数に合わせて左テーブルの行数を複製する
2. 結合相手がいない行は結合結果から消滅する
一つづつ見て行きましょう。
###【INNER JOINの挙動①】右テーブルの行数に合わせて左テーブルの行数を複製する
例えば、以下のテーブルがあったとします。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
| 4 | Why? | 2 |
postsテーブルにid: 4が追加されましたね。
現状では、idが2のuserが一つに対し、user_idが2のpostが二つあります。
ここで先ほどのjoinを使って結合しましょう。
SELECT *
FROM users -- 結合元の左テーブル
JOIN posts -- 結合先の右テーブル
ON users.id = posts.user_id -- 左テーブルのidと右テーブルのidが一致するもの
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 3 | hanako | 1 | Hello | 3 |
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
| 2 | jiro | 4 | Why? | 2 |
+------+--------+------+-------+---------+
usersテーブルの行が一つ増えましたね！
このように、右テーブルに繋ぐべき行が複数ある場合、左テーブルの行を複製して結合します。
では、この繋ぐべき行が左テーブルになかったらどうなるでしょうか？それを次に見て行きましょう。
###【INNER JOINの挙動②】 結合相手がいない行は結合結果から消滅する
それでは、結合先のいないidが4のuserを追加して見ましょう。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
| 4 | saito |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
先ほどと同じSQLを実行すると以下のようになります。
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 3 | hanako | 1 | Hello | 3 |
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
+------+--------+------+-------+---------+
このように、結合結果から削除されます。
これはpostsテーブルに結合先のない行を追加した場合も同じです。どちらか一方のテーブルになければ複製されず打ち消されるのです。
また、先ほど追加したuserのid(4)がNULLだった場合も結合結果から消滅されます。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
| NULL | saito |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
| 3 | hanako | 1 | Hello | 3 |
+------+--------+------+-------+---------+
idがNULLのsatoさんがいなくなってますね。
SQLの世界ではどんな値にNULLを比較してもFALSEになるためです。
(つまり、NULLとNULL同士もFALSEになる)
##内部結合と外部結合
テーブルの結合には大きく分けて、**内部結合(INNER JOIN)と外部結合(OUTER JOIN)**の2種類があります。
先ほどまで使っていたのが、INNER JOINという名の内部結合です。
内部結合では、どちらかのテーブルに存在しない場合は結合結果から消滅されるという挙動をしていましたね。
しかし、
「どちらかのテーブルに存在しないから削除されるなんていやだ！」
「片方がNULLであっても結合して欲しい！」
と思った方もいるのではないでしょうか。
そんなときに使うのが次に紹介する外部結合(OUTER JOIN)です。
外部結合では、内部結合のように条件に一致させた状態で結合してくれるのに加え、どちらかのテーブルに存在しないもの、NULLのものに関しても強制的に取得してくれます。
さらに外部結合にはLEFT JOIN、RIGHT JOIN, FULL JOINの3種類があります。
一つづつ見て行きましょう。
【補足】
LEFT JOIN = LEFT OUTER JOIN
RIGHT JOIN = RIGHT OUTER JOIN
FULL JOIN = FULL OUTER JOIN
##LEFT JOIN(左外部結合)
左外部結合のことで、左のテーブルは全て表示します。
それでは結合先のない値を左テーブルに指定した場合をみてみましょう。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
| 4 | saito |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
LEFT JOINを使って結合します。
SELECT *
FROM users
LEFT JOIN posts
ON users.id = posts.user_id
↓
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 3 | hanako | 1 | Hello | 3 |
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
| 4 | saito | NULL | NULL | NULL |
+------+--------+------+-------+---------+
先ほど行なっていた内部結合とは違い、消滅されていたはずの行が出力されていますね。
このように、左の行は強制的に全て表示し、右テーブルには全ての値がNULLである行を生成して結合してくれます。
また,NULLを指定した場合も同じです。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
| NULL | saito |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 3 | hanako | 1 | Hello | 3 |
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
| NULL | saito | NULL | NULL | NULL |
+------+--------+------+-------+---------+
##RIGHT JOIN（右外部結合)
右外部結合のことで、さっきの逆。
右のテーブルを全て表示して結合します。
postsテーブルのuser_idがNULLになってることを確認してください。
usersテーブル
| id | name |
|---|---|
| 1 | taro |
| 2 | jiro |
| 3 | hanako |
postsテーブル
| id | body | user_id |
|---|---|---|
| 1 | Hello | 3 |
| 2 | Hi | 1 |
| 3 | Good | 2 |
| 4 | Sorry | 4 |
それではRIGHT JOINを発行してみましょう。
SELECT *
FROM users
RIGHT JOIN posts
ON users.id = posts.user_id
↓
+------+--------+------+-------+---------+
| id | name | id | body | user_id |
+------+--------+------+-------+---------+
| 1 | taro | 2 | Hi | 1 |
| 2 | jiro | 3 | Good | 2 |
| 3 | hanako | 1 | Hello | 3 |
| NULL | NULL | 4 | Sorry | 4 |
+------+--------+------+-------+---------+
このように、右のテーブルは何が何でも全て表示し、左のusersテーブルには4がないため、全値がNULLの行を生成して結合しています。
(user_idがNULLの場合も同様)
完全外部結合、mysqlじゃ無理っぽいので省略します(m_ _m)
##ざっくりまとめ
内部結合・・・ 結合すべき行が見つからなかった場合にその行は消滅する。
外部結合・・・ 結合すべき行が見つからなくても諦めず、全ての値がNULLである行を生成して結合する
【結合の種類】
| 名前 | 種類 | 内容 |
|---|---|---|
| JOIN | 内部結合 |
・右テーブルの行数に合わせて左テーブルの行数を複製する ・結合相手がいない行は結合結果から消滅する |
| LEFT JOIN | 左外部結合 |
・左の行は強制的に全て表示する ・条件に合わないものは、右テーブルに値が全てNULLである行を生成して結合する |
| RIGHT JOIN | 右外部結合 |
・右の行は強制的に全て表示する ・条件に合わないものは、右テーブルに値が全てNULLである行を生成して結合する |
| FULL JOIN | 完全外部結合 | 左右の全テーブルを全て表示させる |
この記事で扱った簡単なデータはこっから作れると思うので、検証したい方は以下のddlやdmlを実行し、自分なりにデータをいじりながら確認してみてください。
create database test_db;
use test_db;
create table users(id int, name varchar(20));
create table posts(id int, body varchar(20), user_id int);
insert into users values(1, 'taro'), (2, 'jiro'), (3, 'hanako');
insert into posts values(1, 'Hello', 3), (2, 'Hi', 1), (3, 'Good', 2);
select * from users join posts on users.id = posts.user_id;