2016年06月01日

MariaDBでSQLを学ぶ [INNER JOIN編]

前回

次回はDBのデータを検索する方法についてメモしようと思います。

とか書いたのですが、今回はSQLのINNER JOINについてメモします。

INNER JOINとは

「二つのテーブルとデータを検索する条件があり、その二つのテーブルそれぞれのカラムから条件に一致するデータだけを結合して取り出す方法」
・・・らしい。

何を言ってるのか全然わからんので、前回作った試験環境で実際に打ち込んで確認する。

基本文法

select * from テーブル名1 inner join テーブル名2 on 検索条件

やってみた

MariaDB [hakozaru]> select * from tes1;
+------+------+------+
| name | num  | id   |
+------+------+------+
| aaa  |  100 |    1 |
| bbb  |  200 |    2 |
| ccc  |  300 |    3 |
+------+------+------+
3 rows in set (0.00 sec)
MariaDB [hakozaru]> select * from tes2;
+--------+------+------+
| name   | num  | id   |
+--------+------+------+
| aaa    |  100 |    1 |
| bbb    |  200 |    1 |
| ccc    |  300 |    1 |
| name   | 1234 | 9999 |
| name   | 1234 | 9999 |
| name   | 1234 | 9999 |
| 123456 | 2222 | 1111 |
| 123456 | 2222 | 1111 |
| 123456 | 2222 | 1111 |
+--------+------+------+
9 rows in set (0.00 sec)

という二つのテーブルがあった場合

MariaDB [hakozaru]> select * from tes1 inner join tes2 on tes1.id = tes2.id;

こう書くと

+------+------+------+------+------+------+
| name | num  | id   | name | num  | id   |
+------+------+------+------+------+------+
| aaa  |  100 |    1 | aaa  |  100 |    1 |
| aaa  |  100 |    1 | bbb  |  200 |    1 |
| aaa  |  100 |    1 | ccc  |  300 |    1 |
+------+------+------+------+------+------+

こんなデータを取得することができる。

何が起こった?

fromで指定されたtes1テーブルの各レコードに対して、inner joinで指定されたtes2テーブルと条件をもとに比較して、条件を満たすデータがtes2にあれば取り出して結合する。
onの後に書く条件式はfromの後のテーブルと、inner joinの後のテーブルが持つカラムから指定する。
どのようにデータが取り出されているのかをみると

1. まず、(fromで指定された)tes1テーブルの最初のレコードに対して結合が行われる

+------+------+------+
| name | num  | id   |
+------+------+------+
| aaa  |  100 |    1 |
+------+------+------+

このデータ

2. 結合条件はtes1.id = tes2.idですので、tes2テーブルからtes1.idと一致するレコードがないか検索します

3. tes2テーブルにはidが1のレコードが3つありますので、その全てのレコードに対して結合が行われます。
よって以下のデータが得られます

+------+------+------+------+------+------+
| name | num  | id   | name | num  | id   |
+------+------+------+------+------+------+
| aaa  |  100 |    1 | aaa  |  100 |    1 |
| aaa  |  100 |    1 | bbb  |  200 |    1 |
| aaa  |  100 |    1 | ccc  |  300 |    1 |
+------+------+------+------+------+------+

4. 続いて次のレコードに対しても同じように検索と結合を行います。
tes1テーブルの次のレコードは

+------+------+------+
| name | num  | id   |
+------+------+------+
| bbb  |  200 |    2 |
+------+------+------+

ですが、条件を満たすレコード(id=2)がtes2には無いため、データは得られません。
結合条件に一致しないデータは取得されません。

5. その後は4と同じで、条件を満たすレコードが得られないため、最終的に得られる結果は最初のテーブルになったのでした。

+------+------+------+------+------+------+
| name | num  | id   | name | num  | id   |
+------+------+------+------+------+------+
| aaa  |  100 |    1 | aaa  |  100 |    1 |
| aaa  |  100 |    1 | bbb  |  200 |    1 |
| aaa  |  100 |    1 | ccc  |  300 |    1 |
+------+------+------+------+------+------+

他にも色々試す

tes2テーブルを基準にした場合

MariaDB [hakozaru]> select * from tes2 inner join tes1 on tes2.num = tes1.num;
+------+------+------+------+------+------+
| name | num  | id   | name | num  | id   |
+------+------+------+------+------+------+
| aaa  |  100 |    1 | aaa  |  100 |    1 |
| bbb  |  200 |    1 | bbb  |  200 |    2 |
| ccc  |  300 |    1 | ccc  |  300 |    3 |
+------+------+------+------+------+------+
3 rows in set (0.00 sec)

ちゃんとselectで取得するカラムを書けば、結合された結果からさらに一部のみ取り出すことができる。

MariaDB [hakozaru]> select tes2.name, tes2.num, tes2.id from tes2 inner join tes1 on tes2.num = tes1.num;
+------+------+------+
| name | num  | id   |
+------+------+------+
| aaa  |  100 |    1 |
| bbb  |  200 |    1 |
| ccc  |  300 |    1 |
+------+------+------+
3 rows in set (0.00 sec)
(結合された結果からtes2に関するカラムのみ取り出す場合)
MariaDB [hakozaru]> select tes1.name, tes1.num, tes1.id from tes2 inner join tes1 on tes2.num = tes1.num;
+------+------+------+
| name | num  | id   |
+------+------+------+
| aaa  |  100 |    1 |
| bbb  |  200 |    2 |
| ccc  |  300 |    3 |
+------+------+------+
3 rows in set (0.00 sec)
(結合された結果からtes1に関するカラムのみ取り出す場合)
MariaDB [hakozaru]> select tes2.name, tes1.num, tes2.id from tes2 inner join tes1 on tes2.num = tes1.num;
+------+------+------+
| name | num  | id   |
+------+------+------+
| aaa  |  100 |    1 |
| bbb  |  200 |    1 |
| ccc  |  300 |    1 |
+------+------+------+
3 rows in set (0.00 sec)
(複合パターン(わかりづらい。。。))

ちょっとDBのレコードがイマイチなので、いい結合結果を示せなかったのですが、とりあえずinnner joinの基礎はこんなところでしょうか。
これにwhereやらandやらが混ざってくると本当にややこしいです. . .