MySQLのJOINとUNIONについて

mysql join union

データベース

2018-07-19 12:56:20

概要

MySQLのJOINの種類とUNIONについてまとめる

INNER JOIN

指定カラムの値が一致するレコード同士を結合する。
指定したカラムの値が一致しない場合は結合しない。
(両方のテーブルに一致するデータだけ結合される。)

users
+------+--------+------+
| id | sex | name |
+------+--------+------+
| 0 | male | John |
| 1 | female | Risa |
| 2 | male | Taro |
+------+--------+------+

accounts
+------+---------+---------------------+
| id | user_id | created_at |
+------+---------+---------------------+
| 0 | 0 | 2018-07-18 14:47:41 |
| 1 | 1 | 2018-07-18 14:48:01 |
| 3 | 3 | 2018-07-18 15:07:37 |
+------+---------+---------------------+

SELECT * FROM users INNER JOIN accounts ON users.id = accounts.user_id
+------+--------+------+------+---------+---------------------+
| id | sex | name | id | user_id | created_at |
+------+--------+------+------+---------+---------------------+
| 0 | male | John | 0 | 0 | 2018-07-18 14:47:41 |
| 1 | female | Risa | 1 | 1 | 2018-07-18 14:48:01 |
+------+--------+------+------+---------+---------------------+

usersテーブルのidが2のレコードはaccoutsテーブルに一致するものが含まれないので結合されない。
accountsテーブルのuser_idが3のレコードはusersテーブルに一致するものが含まれないので結合されない。

LEFT OUTER JOIN

指定のカラムの値が一致するレコード同士を結合する。
左のテーブルに存在し、右のテーブルに存在しない値はNULLでパディングされる。
(左のテーブルに存在するレコードは全て結合される)

users
+------+--------+------+
| id | sex | name |
+------+--------+------+
| 0 | male | John |
| 1 | female | Risa |
| 2 | male | Taro |
+------+--------+------+

accounts
+------+---------+---------------------+
| id | user_id | created_at |
+------+---------+---------------------+
| 0 | 0 | 2018-07-18 14:47:41 |
| 1 | 1 | 2018-07-18 14:48:01 |
| 3 | 3 | 2018-07-18 15:07:37 |
+------+---------+---------------------+

SELECT * FROM users LEFT OUTER JOIN accounts ON users.id = accounts.id
+------+--------+------+------+---------+---------------------+
| id | sex | name | id | user_id | created_at |
+------+--------+------+------+---------+---------------------+
| 0 | male | John | 0 | 0 | 2018-07-18 14:47:41 |
| 1 | female | Risa | 1 | 1 | 2018-07-18 14:48:01 |
| 2 | male | Taro | NULL | NULL | NULL |
+------+--------+------+------+---------+---------------------+

左のテーブルであるusersテーブルには、idが2のレコードがあるが、右のテーブルであるaccountテーブルには一致するものがないので、NULLでパディングして結合されている。

RIGHT OUTER JOIN

LEFT OUTER JOINの逆。
指定のカラムの値が一致するレコード同士を結合する。
右のテーブルに存在し、左のテーブルに存在しない値はNULLでパディングされる。
(右のテーブルに存在するレコードは全て結合される)

users
+------+--------+------+
| id | sex | name |
+------+--------+------+
| 0 | male | John |
| 1 | female | Risa |
| 2 | male | Taro |
+------+--------+------+

accounts
+------+---------+---------------------+
| id | user_id | created_at |
+------+---------+---------------------+
| 0 | 0 | 2018-07-18 14:47:41 |
| 1 | 1 | 2018-07-18 14:48:01 |
| 3 | 3 | 2018-07-18 15:07:37 |
+------+---------+---------------------+

SELECT * from users RIGHT OUTER JOIN accounts ON users.id = accounts.id
+------+--------+------+------+---------+---------------------+
| id | sex | name | id | user_id | created_at |
+------+--------+------+------+---------+---------------------+
| 0 | male | John | 0 | 0 | 2018-07-18 14:47:41 |
| 1 | female | Risa | 1 | 1 | 2018-07-18 14:48:01 |
| NULL | NULL | NULL | 3 | 3 | 2018-07-18 15:07:37 |
+------+--------+------+------+---------+---------------------+

右のテーブルであるaccountsテーブルには、user_idが3のレコードがあるが、左のテーブルであるusersテーブルには一致するものがないので、NULLでパディングして結合されている。

CROSS JOIN

MySQLでは、CROSS JOINとINNER JOINは構文上同等。(参考:MySQL 8.2.1.11 ネストした結合の最適化

UNION

テーブルとテーブルの重複を省いた形で結合する。
条件として、列数が同じがテーブルでなければならない。

users
+------+--------+------+
| id | sex | name |
+------+--------+------+
| 0 | male | John |
| 1 | female | Risa |
| 2 | male | Taro |
+------+--------+------+

accounts
+------+---------+---------------------+
| id | user_id | created_at |
+------+---------+---------------------+
| 0 | 0 | 2018-07-18 14:47:41 |
| 1 | 1 | 2018-07-18 14:48:01 |
| 3 | 3 | 2018-07-18 15:07:37 |
+------+---------+---------------------+

SELECT * FROM users UNION SELECT * FROM accoounts
+------+--------+---------------------+
| id | sex | name |
+------+--------+---------------------+
| 0 | male | John |
| 1 | female | Risa |
| 2 | male | Taro |
| 0 | 0 | 2018-07-18 14:47:41 |
| 1 | 1 | 2018-07-18 14:48:01 |
| 3 | 3 | 2018-07-18 15:07:37 |
+------+--------+---------------------+

参考