当記事の想定
- 社員は1人につき1台の社用スマホを貸与されているが、入社間もない桃子は現時点では社用スマホを貸与されていない
- 6台の社用スマホのうち、2台(id:2とid:6)は誰にも貸与されていない
テーブルの内容
# 社員テーブル(employee)
$ MariaDB [test]> SELECT * FROM employee;
+----+--------+----------+
| id | name | phone_id |
+----+--------+----------+
| 1 | 太郎 | 1 |
| 2 | 次郎 | 3 |
| 3 | 三郎 | 4 |
| 4 | 花子 | 5 |
| 5 | 桃子 | NULL |
+----+--------+----------+
# 社用スマホテーブル(phone)
$ MariaDB [test]> SELECT * FROM phone;
+----+---------------+
| id | tel |
+----+---------------+
| 1 | 090-1111-1111 |
| 2 | 090-2222-2222 |
| 3 | 090-3333-3333 |
| 4 | 090-4444-4444 |
| 5 | 090-5555-5555 |
| 6 | 090-6666-6666 |
+----+---------------+
目次
INNER JOIN(内部結合)
両方のテーブルのレコードのうち、指定したカラムの値が一致するレコードのみを取得する。
以下の場合は社用スマホを貸与されている社員とその電話番号が表示される。
逆に、社用スマホを持たない社員(id:5の桃子)と、誰にも貸与されていない社用スマホ(id:2とid:6)は表示されない。
INNER JOIN
$ MariaDB [test]> SELECT * FROM employee INNER JOIN phone ON employee.phone_id = phone.id;
+----+--------+----------+----+---------------+
| id | name | phone_id | id | tel |
+----+--------+----------+----+---------------+
| 1 | 太郎 | 1 | 1 | 090-1111-1111 |
| 2 | 次郎 | 3 | 3 | 090-3333-3333 |
| 3 | 三郎 | 4 | 4 | 090-4444-4444 |
| 4 | 花子 | 5 | 5 | 090-5555-5555 |
+----+--------+----------+----+---------------+
指定したカラムの値が一致するかどうかが取得する or しないの判断基準なので、結合時にテーブルの順番を入れ替えても取得できるレコード内容は同じ(カラムの並びは当然違う)。
INNER JOIN
$ MariaDB [test]> SELECT * FROM phone INNER JOIN employee ON phone.id = employee.phone_id;
+----+---------------+----+--------+----------+
| id | tel | id | name | phone_id |
+----+---------------+----+--------+----------+
| 1 | 090-1111-1111 | 1 | 太郎 | 1 |
| 3 | 090-3333-3333 | 2 | 次郎 | 3 |
| 4 | 090-4444-4444 | 3 | 三郎 | 4 |
| 5 | 090-5555-5555 | 4 | 花子 | 5 |
+----+---------------+----+--------+----------+
OUTER JOIN(外部結合)
両方のテーブルのレコードのうち、指定したカラムの値が一致するレコードのみを取得するのはINNER JOINと一緒。
それに加えて片方のテーブルにしか存在しないレコードも取得する。
LEFT JOIN
左側のテーブル(先に指定したテーブル)のレコードは必ず取得する。結合するレコードがないときはNULLになる。
以下の場合だと桃子はまだ社用スマホを貸与されていないのでNULLとなる。
「LEFT JOIN」の通り、左側のテーブル(= employee)が基準となるので、誰にも貸与されていない社用スマホ(id:2とid:6)は表示されない。
LEFT OUTER JOIN
$ MariaDB [test]> SELECT * FROM employee LEFT OUTER JOIN phone ON employee.phone_id = phone.id;
+----+--------+----------+------+---------------+
| id | name | phone_id | id | tel |
+----+--------+----------+------+---------------+
| 1 | 太郎 | 1 | 1 | 090-1111-1111 |
| 2 | 次郎 | 3 | 3 | 090-3333-3333 |
| 3 | 三郎 | 4 | 4 | 090-4444-4444 |
| 4 | 花子 | 5 | 5 | 090-5555-5555 |
| 5 | 桃子 | NULL | NULL | NULL |
+----+--------+----------+------+---------------+
テーブルを入れ替えると左側のphoneテーブルのレコードを全部取得した上でemployeeテーブルを結合するので、誰にも貸与されていないスマホはNULLになる。
LEFT OUTER JOIN
MariaDB [test]> SELECT * FROM phone LEFT OUTER JOIN employee ON phone.id = employee.phone_id;
+----+---------------+------+--------+----------+
| id | tel | id | name | phone_id |
+----+---------------+------+--------+----------+
| 1 | 090-1111-1111 | 1 | 太郎 | 1 |
| 2 | 090-2222-2222 | NULL | NULL | NULL |
| 3 | 090-3333-3333 | 2 | 次郎 | 3 |
| 4 | 090-4444-4444 | 3 | 三郎 | 4 |
| 5 | 090-5555-5555 | 4 | 花子 | 5 |
| 6 | 090-6666-6666 | NULL | NULL | NULL |
+----+---------------+------+--------+----------+
RIGHT JOIN
右側のテーブル(後に指定したテーブル)のレコードは必ず取得する。結合するレコードがないときはNULLになる。
以下の場合だと右側のphoneの全レコードを取得した上で左側にemployeeテーブルを結合するので、誰にも貸与されていないスマホ(id:2とid:6)はNULLになる。
RIGHT OUTER JOIN
$ MariaDB [test]> SELECT * FROM employee RIGHT OUTER JOIN phone ON employee.phone_id = phone.id;
+------+--------+----------+----+---------------+
| id | name | phone_id | id | tel |
+------+--------+----------+----+---------------+
| 1 | 太郎 | 1 | 1 | 090-1111-1111 |
| NULL | NULL | NULL | 2 | 090-2222-2222 |
| 2 | 次郎 | 3 | 3 | 090-3333-3333 |
| 3 | 三郎 | 4 | 4 | 090-4444-4444 |
| 4 | 花子 | 5 | 5 | 090-5555-5555 |
| NULL | NULL | NULL | 6 | 090-6666-6666 |
+------+--------+----------+----+---------------+
テーブルを入れ替えると右側のemployeeの全レコードを取得した上で左側にphoneテーブルを結合する。
社用スマホを貸与されていない桃子はNULLになり、また、誰にも貸与されていないスマホ(id:6)のレコードは表示されない。
RIGHT OUTER JOIN
$ MariaDB [test]> SELECT * FROM phone RIGHT OUTER JOIN employee ON phone.id = employee.phone_id;
+------+---------------+----+--------+----------+
| id | tel | id | name | phone_id |
+------+---------------+----+--------+----------+
| 1 | 090-1111-1111 | 1 | 太郎 | 1 |
| 3 | 090-3333-3333 | 2 | 次郎 | 3 |
| 4 | 090-4444-4444 | 3 | 三郎 | 4 |
| 5 | 090-5555-5555 | 4 | 花子 | 5 |
| NULL | NULL | 5 | 桃子 | NULL |
+------+---------------+----+--------+----------+
LEFT OUTER JOIN と RIGHT OUTER JOINの入れ替え
LEFT OUTER JOINとRIGHT OUTER JOINは以下のように入れ替えると同じレコード内容を取得できる。
ただし、カラムの順番は異なるのでそこはSELECT句で調整しないといけない。
LEFT OUTER JOIN と RIGHT OUTER JOINの入れ替え
# 取得できるレコード内容が同じ
$ SELECT * FROM employee LEFT OUTER JOIN phone ON employee.phone_id = phone.id;
$ SELECT * FROM phone RIGHT OUTER JOIN employee ON phone.id = employee.phone_id;
# 取得できるレコード内容が同じ
$ SELECT * FROM phone LEFT OUTER JOIN employee ON phone.id = employee.phone_id;
$ SELECT * FROM employee RIGHT OUTER JOIN phone ON employee.phone_id = phone.id;