MySQLの内部結合と外部結合のやり方。主に結合の違いによってどういう動きをするかの紹介。
例題のテーブル
ユーザーテーブル
mysql> SELECT * FROM users;
+----+-----------+
| id | user_name |
+----+-----------+
| 1 | 伊藤 |
| 2 | 鈴木 |
| 3 | 大森 |
| 4 | 田中 |
| 5 | 藤井 |
+----+-----------+
5 rows in set (0.00 sec)商品テーブル
mysql> SELECT * FROM items;
+----+--------------+-------+
| id | name | price |
+----+--------------+-------+
| 1 | りんご | 100 |
| 2 | えんぴつ | 200 |
| 3 | 本 | 1000 |
| 4 | ぶどう | 300 |
+----+--------------+-------+
4 rows in set (0.00 sec)購入履歴テーブル
mysql> SELECT * FROM purchase_history;
+----+---------+---------+---------------------+
| id | user_id | item_id | created_at |
+----+---------+---------+---------------------+
| 1 | 2 | 3 | 2019-10-03 20:21:36 |
| 2 | 4 | 1 | 2019-11-13 23:22:23 |
| 3 | 1 | 5 | 2020-06-05 15:24:21 |
| 4 | 2 | 4 | 2020-07-03 13:02:11 |
+----+---------+---------+---------------------+
4 rows in set (0.00 sec)
# item_idの5という値は商品テーブルに存在しないが、挙動の参考にのせる内部結合(INNER JOIN)
基本の書き方
SELECT カラム名 FROM テーブル１ INNER JOIN テーブル２ ON 結合するテーブル１のカラム名 = 結合するテーブル２のカラム名;例題の３つのテーブルを結合すると以下のようになる。
mysql> SELECT * FROM purchase_history INNER JOIN users ON purchase_history.user_id = users.id INNER JOIN items ON purchase_history.item_id = items.id;
+----+---------+---------+---------------------+----+-----------+----+-----------+-------+
| id | user_id | item_id | created_at | id | user_name | id | name | price |
+----+---------+---------+---------------------+----+-----------+----+-----------+-------+
| 1 | 2 | 3 | 2019-10-03 20:21:36 | 2 | 鈴木 | 3 | 本 | 1000 |
| 2 | 4 | 1 | 2019-11-13 23:22:23 | 4 | 田中 | 1 | りんご | 100 |
| 4 | 2 | 4 | 2020-07-03 13:02:11 | 2 | 鈴木 | 4 | ぶどう | 300 |
+----+---------+---------+---------------------+----+-----------+----+-----------+-------+
3 rows in set (0.01 sec)
# INNER JOINと明示しなくてもJOINでも同様の結果が得られる
SELECT * FROM purchase_history JOIN users ON purchase_history.user_id = users.id JOIN items ON purchase_history.item_id = items.id;結合対象となるカラムで同じ値を紐づけてとってくる。ポイントは全てのテーブルに合致する値であるという点。例えば、購入履歴に存在しないidが混入していても（今回でいうと購入履歴テーブルのid3）、内部結合で商品テーブルに存在しないのであれば結合できないので、誤った購入履歴は検出されない。
参考に購入履歴とユーザー、購入履歴と商品の２つを内部結合した結果ものせる。
購入履歴とユーザーの結合
mysql> SELECT * FROM purchase_history INNER JOIN users ON purchase_history.user_id = users.id;
+----+---------+---------+---------------------+----+-----------+
| id | user_id | item_id | created_at | id | user_name |
+----+---------+---------+---------------------+----+-----------+
| 1 | 2 | 3 | 2019-10-03 20:21:36 | 2 | 鈴木 |
| 2 | 4 | 1 | 2019-11-13 23:22:23 | 4 | 田中 |
| 3 | 1 | 5 | 2020-06-05 15:24:21 | 1 | 伊藤 |
| 4 | 2 | 4 | 2020-07-03 13:02:11 | 2 | 鈴木 |
+----+---------+---------+---------------------+----+-----------+上記の場合item_idは結合に関与していないので、商品テーブルに存在しない値でも取得できている
購入履歴と商品
mysql> SELECT * FROM purchase_history INNER JOIN items ON purchase_history.item_id = items.id;
+----+---------+---------+---------------------+----+-----------+-------+
| id | user_id | item_id | created_at | id | name | price |
+----+---------+---------+---------------------+----+-----------+-------+
| 1 | 2 | 3 | 2019-10-03 20:21:36 | 3 | 本 | 1000 |
| 2 | 4 | 1 | 2019-11-13 23:22:23 | 1 | りんご | 100 |
| 4 | 2 | 4 | 2020-07-03 13:02:11 | 4 | ぶどう | 300 |
+----+---------+---------+---------------------+----+-----------+-------+
3 rows in set (0.00 sec)外部結合(OUTER JOIN)
外部結合は、結合に特定のカラムの同じ値を用いるのは一緒だが、一方のテーブルに存在しない値があってもとってこれる点が異なる。
左外部結合(LEFT JOIN)
LEFT JOINの場合、結合する際に左側のテーブルが優先して採用される。なので文の中で左にあるテーブルのカラムは対応する値が右のテーブルになくても表示される。
下記の場合ユーザーテーブルが優先されているのでユーザー名は全て表示され、購入履歴がないユーザーに関しては購入履歴のIDがNULLとして表示される。テーブルの位置を入れ替えてみると、購入履歴が優先されるので対応しないユーザー名は表示されない。
mysql> SELECT P.id AS p_id,U.id AS u_id,user_name FROM users AS U LEFT JOIN purchase_history AS P ON P.user_id = U.id;
+------+------+-----------+
| p_id | u_id | user_name |
+------+------+-----------+
| 1 | 2 | 鈴木 |
| 2 | 4 | 田中 |
| 3 | 1 | 伊藤 |
| 4 | 2 | 鈴木 |
| NULL | 3 | 大森 |
| NULL | 5 | 藤井 |
+------+------+-----------+
6 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,user_name FROM purchase_history AS P LEFT JOIN users AS U ON P.user_id = U.id;
+------+------+-----------+
| p_id | u_id | user_name |
+------+------+-----------+
| 1 | 2 | 鈴木 |
| 2 | 4 | 田中 |
| 3 | 1 | 伊藤 |
| 4 | 2 | 鈴木 |
+------+------+-----------+
4 rows in set (0.01 sec)３つのテーブルを結合した場合。結合の順番を入れ替えることで優先度が変わるので、とってこれるレコードも変化する。下記の２番目の場合だとユーザーが優先されるので他のカラムがNULLになってもユーザー名は全部取得できている。
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM purchase_history AS P LEFT JOIN users AS U ON P.user_id = U.id LEFT JOIN items AS I ON P.item_id = I.id;
+------+------+------+-----------+-----------+-------+
| p_id | u_id | i_id | user_name | name | price |
+------+------+------+-----------+-----------+-------+
| 1 | 2 | 3 | 鈴木 | 本 | 1000 |
| 2 | 4 | 1 | 田中 | りんご | 100 |
| 3 | 1 | NULL | 伊藤 | NULL | NULL |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 |
+------+------+------+-----------+-----------+-------+
4 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM users AS U LEFT JOIN purchase_history AS P ON U.id = P.user_id LEFT JOIN items AS I ON P.item_id = I.id;
+------+------+------+-----------+-----------+-------+
| p_id | u_id | i_id | user_name | name | price |
+------+------+------+-----------+-----------+-------+
| 1 | 2 | 3 | 鈴木 | 本 | 1000 |
| 2 | 4 | 1 | 田中 | りんご | 100 |
| 3 | 1 | NULL | 伊藤 | NULL | NULL |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 |
| NULL | 3 | NULL | 大森 | NULL | NULL |
| NULL | 5 | NULL | 藤井 | NULL | NULL |
+------+------+------+-----------+-----------+-------+
6 rows in set (0.00 sec)３つ以上の結合も珍しくないと思うので、外部結合を用いる場合は基準となるテーブル、優先関係にあるテーブルをきちんと考慮しないと、容易に意図しない結果を取得することになる。
右外部結合(RIGHT JOIN)
RIGHT JOINの場合は優先関係が逆になり、右にあるテーブルが優先される。
mysql> SELECT P.id AS p_id,U.id AS u_id,user_name FROM users AS U RIGHT JOIN purchase_history AS P ON P.user_id = U.id;
+------+------+-----------+
| p_id | u_id | user_name |
+------+------+-----------+
| 1 | 2 | 鈴木 |
| 2 | 4 | 田中 |
| 3 | 1 | 伊藤 |
| 4 | 2 | 鈴木 |
+------+------+-----------+
4 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,user_name FROM purchase_history AS P RIGHT JOIN users AS U ON P.user_id = U.id;
+------+------+-----------+
| p_id | u_id | user_name |
+------+------+-----------+
| 1 | 2 | 鈴木 |
| 2 | 4 | 田中 |
| 3 | 1 | 伊藤 |
| 4 | 2 | 鈴木 |
| NULL | 3 | 大森 |
| NULL | 5 | 藤井 |
+------+------+-----------+
6 rows in set (0.00 sec)
左右の結合を混在させてみる
購入履歴テーブル基準で全て左外部結合する場合はやっているので、それ以外で混在させてみた。
複数結合させていく時は結合していく中で最優先のものが何かを意識しとけばよさそう。勝ち上がり式なイメージで一番強いテーブルに合わせていく感じ。
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM purchase_history AS P RIGHT JOIN users AS U ON P.user_id = U.id LEFT JOIN items AS I ON I.id = P.item_id;
+------+------+------+-----------+-----------+-------+
| p_id | u_id | i_id | user_name | name | price |
+------+------+------+-----------+-----------+-------+
| 1 | 2 | 3 | 鈴木 | 本 | 1000 |
| 2 | 4 | 1 | 田中 | りんご | 100 |
| 3 | 1 | NULL | 伊藤 | NULL | NULL |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 |
| NULL | 3 | NULL | 大森 | NULL | NULL |
| NULL | 5 | NULL | 藤井 | NULL | NULL |
+------+------+------+-----------+-----------+-------+
6 rows in set (0.01 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM purchase_history AS P LEFT JOIN users AS U ON P.user_id = U.id RIGHT JOIN items AS I ON I.id = P.item_id;
+------+------+------+-----------+--------------+-------+
| p_id | u_id | i_id | user_name | name | price |
+------+------+------+-----------+--------------+-------+
| 2 | 4 | 1 | 田中 | りんご | 100 |
| NULL | NULL | 2 | NULL | えんぴつ | 200 |
| 1 | 2 | 3 | 鈴木 | 本 | 1000 |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 |
+------+------+------+-----------+--------------+-------+
4 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM purchase_history AS P RIGHT JOIN users AS U ON P.user_id = U.id RIGHT JOIN items AS I ON I.id = P.item_id;
+------+------+------+-----------+--------------+-------+
| p_id | u_id | i_id | user_name | name | price |
+------+------+------+-----------+--------------+-------+
| 2 | 4 | 1 | 田中 | りんご | 100 |
| NULL | NULL | 2 | NULL | えんぴつ | 200 |
| 1 | 2 | 3 | 鈴木 | 本 | 1000 |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 |
+------+------+------+-----------+--------------+-------+
4 rows in set (0.00 sec)記述の注意
当然だが、結合条件に用いるカラムのテーブルが先に書かれていないとエラーになる。
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price FROM users AS U LEFT JOIN items AS I ON P.item_id = I.id RIGHT JOIN purchase_history AS P ON U.id = P.user_id;
ERROR 1054 (42S22): Unknown column 'P.item_id' in 'on clause'また、結合の基準となるテーブルやカラムがぶれると結合が複雑になりがちである。例えば、ユーザーテーブルに紐づくコメントテーブルを作って何も考えず結合する。
mysql> SELECT * FROM comments;
+----+---------+----------+
| id | user_id | comments |
+----+---------+----------+
| 1 | 1 | aaaa |
| 2 | 4 | bbbbbb |
| 3 | 3 | cc |
+----+---------+----------+
3 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price,comments FROM purchase_history AS P RIGHT JOIN users AS U ON P.user_id = U.id RIGHT JOIN items AS I ON I.id = P.item_id RIGHT JOIN comments AS C ON C.user_id = U.id;
+------+------+------+-----------+-----------+-------+----------+
| p_id | u_id | i_id | user_name | name | price | comments |
+------+------+------+-----------+-----------+-------+----------+
| NULL | NULL | NULL | NULL | NULL | NULL | aaaa |
| 2 | 4 | 1 | 田中 | りんご | 100 | bbbbbb |
| NULL | NULL | NULL | NULL | NULL | NULL | cc |
+------+------+------+-----------+-----------+-------+----------+コメントが優先されているので他はNULLばかりになり、内部結合で正常にいくパターンは問題ないが他はダメということになる。
コメントとユーザー名は紐づいて欲しいだけであれば、少なくとも下記のようにクエリ文の順序を取得すれば取得は可能ではある。
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price,comments FROM comments AS C LEFT JOIN users AS U ON C.user_id = U.id LEFT JOIN purchase_history AS P ON P.user_id = U.id LEFT JOIN items AS I ON I.id = P.item_id;
+------+------+------+-----------+-----------+-------+----------+
| p_id | u_id | i_id | user_name | name | price | comments |
+------+------+------+-----------+-----------+-------+----------+
| 2 | 4 | 1 | 田中 | りんご | 100 | bbbbbb |
| 3 | 1 | NULL | 伊藤 | NULL | NULL | aaaa |
| NULL | 3 | NULL | 大森 | NULL | NULL | cc |
+------+------+------+-----------+-----------+-------+----------+
3 rows in set (0.00 sec)
mysql> SELECT P.id AS p_id,U.id AS u_id,I.id AS i_id,user_name,name,price,comments FROM comments AS C RIGHT JOIN users AS U ON C.user_id = U.id LEFT JOIN purchase_history AS P ON P.user_id = U.id LEFT JOIN items AS I ON I.id = P.item_id;
+------+------+------+-----------+-----------+-------+----------+
| p_id | u_id | i_id | user_name | name | price | comments |
+------+------+------+-----------+-----------+-------+----------+
| 1 | 2 | 3 | 鈴木 | 本 | 1000 | NULL |
| 2 | 4 | 1 | 田中 | りんご | 100 | bbbbbb |
| 3 | 1 | NULL | 伊藤 | NULL | NULL | aaaa |
| 4 | 2 | 4 | 鈴木 | ぶどう | 300 | NULL |
| NULL | 3 | NULL | 大森 | NULL | NULL | cc |
| NULL | 5 | NULL | 藤井 | NULL | NULL | NULL |
+------+------+------+-----------+-----------+-------+----------+
6 rows in set (0.00 sec)上記のようにクエリを工夫することで任意の値が取得できれば問題ないが、結合するテーブルが増加すると同様の問題に悩まされることもある。
前提として持ってくる情報の基準がテーブルやカラムとして定まっていると問題も起こりにくいのかなと思う。今回のケースでいえば購入履歴に各種のIDが集約しているところに、ユーザーに紐づくものを追加しようとしているので複雑化している。ユーザーテーブルに集約されていれば調整もスムーズにいくかもしれない。
とはいえ、テーブル構造を変えることが安易にできないケースもある。
単純にクエリの発行を増やしてもパフォーマンス上問題ないのであれば、全て結合せず、分割してもってくることも考える。