データベースの結合について考える

諸事情により結合を基本的なところから、おさらいしたくなった。学び直すという意味合いもあるけれど、言葉の定義や使い方を再確認し、良くわかっていなかいがスルーしても問題なかった疑問点や曖昧にしている箇所について、間違えてても良いから確認し考えて答えを出すという作業をしてみようと思う。ここ数年は特にそういうことに時間を使っている気がする。生きているといろんなフェーズがあると思うし、そのフェーズの順序も人によって大きくことなる。だから、馬鹿にされても気にせず、自分にとって今必要だと思うことを愚直にやっていこうと思う。その活動の一環としての今回は「データベースの結合」だ。

事前準備

使用するデータベース

今回はMySQL8.1を使用する。

テーブル定義

フルーツの在庫を管理するためのテーブルを作成する。 fruitテーブルはフルーツの在庫数と用途を管理する。fruit_kindはフルーツの種類を管理する。フルーツの種類とはりんごとかオレンジとかバナナとかそういうデータだ。

CREATE TABLE fruit (
  id integer primary key,
  description text,
  kind_id integer,
  quantity integer
);

CREATE TABLE fruit_kind (
  id integer primary key,
  name text
);

テストデータ

テスト用のデータとして次のデータを想定する。

id	name
1	apple
2	orange
3	banana

fruit_kind (フルーツ種別)

id	description	kind_id	quantity
1	my apple item	1	10
2	apple for gift	1	20
3	my orange item	2	30

fruit (フルーツの在庫)

これらのデータを挿入するために、以下のSQLを実行する。

INSERT INTO fruit_kind VALUES
  (1, 'apple'),
  (2, 'orange'),
  (3, 'banana');
  
INSERT INTO fruit VALUES
  (1, 'my apple item', 1, 10),
  (2, 'apple for gift', 1, 20),
  (3, 'my orange item', 2, 30);

まずはテーブル結合を試す

複数のテーブルをシンプルに結合する。存在する行の組合せの全てのパターンを羅列する。

SELECT * FROM fruit, fruit_kind;
+----+----------------+---------+----------+----+--------+
| id | description    | kind_id | quantity | id | name   |
+----+----------------+---------+----------+----+--------+
|  3 | my orange item |       2 |       30 |  1 | apple  |
|  2 | apple for gift |       1 |       20 |  1 | apple  |
|  1 | my apple item  |       1 |       10 |  1 | apple  |
|  3 | my orange item |       2 |       30 |  2 | orange |
|  2 | apple for gift |       1 |       20 |  2 | orange |
|  1 | my apple item  |       1 |       10 |  2 | orange |
|  3 | my orange item |       2 |       30 |  3 | banana |
|  2 | apple for gift |       1 |       20 |  3 | banana |
|  1 | my apple item  |       1 |       10 |  3 | banana |
+----+----------------+---------+----------+----+--------+
9 rows in set (0.00 sec)

FULL JOIN は上記のテーブル結合と意味は同じとなる。

SELECT * FROM fruit FULL JOIN fruit_kind;
+----+----------------+---------+----------+----+--------+
| id | description    | kind_id | quantity | id | name   |
+----+----------------+---------+----------+----+--------+
|  3 | my orange item |       2 |       30 |  1 | apple  |
|  2 | apple for gift |       1 |       20 |  1 | apple  |
|  1 | my apple item  |       1 |       10 |  1 | apple  |
|  3 | my orange item |       2 |       30 |  2 | orange |
|  2 | apple for gift |       1 |       20 |  2 | orange |
|  1 | my apple item  |       1 |       10 |  2 | orange |
|  3 | my orange item |       2 |       30 |  3 | banana |
|  2 | apple for gift |       1 |       20 |  3 | banana |
|  1 | my apple item  |       1 |       10 |  3 | banana |
+----+----------------+---------+----------+----+--------+
9 rows in set (0.00 sec)

2つのSQLのEXPLAINを確認すると、優劣がないことがわかる。

EXPLAIN SELECT * FROM fruit, fruit_kind;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|  1 | SIMPLE      | fruit      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                          |
|  1 | SIMPLE      | fruit_kind | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using join buffer (hash join) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM fruit FULL JOIN fruit_kind;
EXPLAIN SELECT * FROM fruit FULL JOIN fruit_kind;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                         |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
|  1 | SIMPLE      | FULL       | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                          |
|  1 | SIMPLE      | fruit_kind | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using join buffer (hash join) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
2 rows in set, 1 warning (0.00 sec)

JOIN 句を使用しない場合、特定の行を表示するには WHERE 句を用いて条件を制限する。

SELECT * FROM fruit, fruit_kind  WHERE fruit_kind.id=1;
+----+----------------+---------+----------+----+-------+
| id | description    | kind_id | quantity | id | name  |
+----+----------------+---------+----------+----+-------+
|  1 | my apple item  |       1 |       10 |  1 | apple |
|  2 | apple for gift |       1 |       20 |  1 | apple |
|  3 | my orange item |       2 |       30 |  1 | apple |
+----+----------------+---------+----------+----+-------+
3 rows in set (0.00 sec)

JOIN 句を使用すると on 句によって WHERE 句と同等のことができる。以下の2つのSQLは同じ結果を返す。

SELECT * FROM fruit FULL JOIN fruit_kind ON fruit_kind.id=1;
+----+----------------+---------+----------+----+-------+
| id | description    | kind_id | quantity | id | name  |
+----+----------------+---------+----------+----+-------+
|  1 | my apple item  |       1 |       10 |  1 | apple |
|  2 | apple for gift |       1 |       20 |  1 | apple |
|  3 | my orange item |       2 |       30 |  1 | apple |
+----+----------------+---------+----------+----+-------+
3 rows in set (0.00 sec)

ただし FULL JOIN を使用する場合 JOIN 句で結合したテーブル以外のテーブルを参照することはできない。上記の例では fruit_kind を指定しているため ON に fruit テーブルのカラムを参照しようとするとエラーになる。これは FULL JOIN のみに適応される制約だ。

SELECT * FROM fruit FULL JOIN fruit_kind ON fruit.id=1;
ERROR 1054 (42S22): Unknown column 'fruit.id' in 'on clause'

内部結合の典型的な例

先述のテーブル結合をすると、全ての組み合わせが羅列されているため、行数は増えるし、計算量も多くなる。ただし実際には、ありえない組合せも羅列されている。

fruitテーブルにはkind_idがある。これはフルーツ種別のIDを保持するカラムであり、fruit_kindテーブルのidの値を保持する。fruit.kind_idが1のものはfruit_kind.idが1である種別ということになる。fruit_kind.idが1なものはappleなので、fruit.kind_id=1であればapple、つまり「りんご」なのだ。しかしよくみると次のような組み合わせの行も表示されている。

fruit_kind	fruit_kind	fruit	fruit	fruit	fruit
id	name	id	description	kind_id	quantity
3	banana	1	my apple item	1	10

fruit_kind.idが3(バナナ)とfruit.idが1(りんご10個の自分用の在庫)が結合されている。どういう状態なのだろうか。意味が分からないかもしれない。これは組合せとして無理矢理結合すると、こんな組み合わせで結合もできるということだけだ。全ての組合せを表示しているのだから、当然この組合せも表現できる。しかし実際には種別がバナナである自分用のりんごの在庫なんてものは意味をなさない。それぞれ値の意味を考慮した結果を得るには、この一覧から条件を絞る必要がある。fruit.kind_idとfruit_kind.idが同じ値であるような組合せの結果には意味がないため、それを除外する必要がある。そこで使用するのが内部結合だ。

フルーツ在庫の用途、個数、種別の名前を一覧で見たいとする。フルーツ在庫の用途はfruitテーブルのdescriptionに保存されている。個数はfruitテーブルのquantityに保存されている。種別の名前はfruit_kindテーブルのnameに保存されている。そこで、fruitテーブルのkind_idとfruit_kindテーブルのidが同じ行のみを抽出し、その結合された行のfruit.description、fruit.quantity、fruit_kind.nameを表示することで、これを実現できる。

SELECT fruit.description, fruit.quantity,fruit_kind.name FROM fruit, fruit_kind WHERE fruit.kind_id=fruit_kind.id;
+----------------+----------+--------+
| description    | quantity | name   |
+----------------+----------+--------+
| my apple item  |       10 | apple  |
| apple for gift |       20 | apple  |
| my orange item |       30 | orange |
+----------------+----------+--------+
3 rows in set (0.00 sec)

descriptionの内容から my apple item が自分用で apple for gift がギフト用と読める。すると、自分用のりんごが10個、ギフト用のりんごが20個、自分用のオレンジが30個あることが分かる。

このSQLを JOIN 句を用いた表現をすることもできる。

SELECT fruit.description, fruit.quantity, fruit_kind.name FROM fruit JOIN fruit_kind ON fruit.kind_id=fruit_kind.id;
+----------------+----------+--------+
| description    | quantity | name   |
+----------------+----------+--------+
| my apple item  |       10 | apple  |
| apple for gift |       20 | apple  |
| my orange item |       30 | orange |
+----------------+----------+--------+
3 rows in set (0.00 sec)

この2つのSQLの実行計画も確認してみると同じだということがわかる。ではどちらをを使えば良いのかという事が気になるかもしれない。実行計画上は同じであるので、どちらでも良いことになるが、私の感覚では JOIN 句を用いた結合を良く見る。O/Rマッパーなどで生成されるクエリでも JOIN が使用されることが多い。口語でも「JOINする」や「JOINしたくない」と言う表現でコミュニケーションをすることも多い。また、現状ではとても単純なテーブル構造と小さなデータ量に対するクエリで確認しているため、実行計画が同じになるが、複雑なテーブル構造やインデックスの状況や大規模なデータでは、実行計画が異なったり、レイテンシーが異なる事もあるかもしれない。そういったこと鑑みると、テーブルやクエリの意味を明確にする上でも結合を明示的に意味する JOIN を用いたほうが良いと思う。

mysql> explain SELECT fruit.description, fruit.quantity,fruit_kind.name FROM fruit, fruit_kind WHERE fruit.kind_id=fruit_kind.id;
explain SELECT fruit.description, fruit.quantity,fruit_kind.name FROM fruit, fruit_kind WHERE fruit.kind_id=fruit_kind.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | fruit      | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    3 |   100.00 | Using where |
|  1 | SIMPLE      | fruit_kind | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testing.fruit.kind_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain SELECT fruit.description, fruit.quantity, fruit_kind.name FROM fruit JOIN fruit_kind ON fruit.kind_id=fruit_kind.id;
explain SELECT fruit.description, fruit.quantity, fruit_kind.name FROM fruit JOIN fruit_kind ON fruit.kind_id=fruit_kind.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | fruit      | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  |    3 |   100.00 | Using where |
|  1 | SIMPLE      | fruit_kind | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testing.fruit.kind_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)