はじめに
SQLにおける「結合」は、「JOIN」とも呼ばれ複数のテーブルを関連させる操作である。
データベースから情報を取得する際、目的となる情報を得るために複数のテーブルを組み合わせなければないシーンは多い。そのような場合に、結合は非常に汎用性が高く、有用である。
結合は、大きく３種類に分けられる。
クロス結合(CROSS JOIN)
実のところ、実務においてクロス結合が役に立つケースは控えめにいって多くはない。
ただし、後で見ていく内部結合、外部結合を理解するために有用なので、しっかりと理解しておこう。
クロス結合によって、各テーブルに存在する行同士のすべての組み合わせが得られる。
実際の動きを見るために、２つのテーブルを用意する。
-- フルーツテーブル
test=# SELECT * FROM fruits ;
name
--------
apple
banana
orenge
(3 rows)
-- 感想テーブル
test=# SELECT * FROM kansou ;
value
----------
おいしい
ふつう
まずい
(3 rows)
早速、クロス結合をしてみよう。
-- クロス結合
test=# SELECT * FROM fruits CROSS JOIN kansou;
name | value
--------+----------
apple | おいしい
apple | ふつう
apple | まずい
banana | おいしい
banana | ふつう
banana | まずい
orenge | おいしい
orenge | ふつう
orenge | まずい
(9 rows)
特に難しくはないだろう。見ての通り、フルーツテーブルと感想テーブルのすべての行の組み合わせが得られた。
また、クロス結合の結果として、全体の行数が非常に大きくなる点にも触れておこう。
上記の例では3行×3行で9行で済んでいるが、100行×100行になると10000行になる。
クロス結合を使う際は、その目的にもよるが、WHERE句で行数を絞ったほうがよいだろう。
test=# SELECT *
FROM fruits f CROSS JOIN kansou k
WHERE f.name = 'banana';
name | value
--------+----------
banana | おいしい
banana | ふつう
banana | まずい
(3 rows)
上記のように、結合を使ったクエリ内で列名を参照するときは、テーブル名に別名を付け、f.nameといった形で指定したほうがよい。
２つのテーブルで列名が重複しない場合は文法的に必須ではないが、可読性がずっと良くなる。
クロス結合を使った応用
クロス結合を活用することで、順列と組み合わせが得られる。
「５種類のフルーツから２種類を選んで並べるパターン（順列）」と、「５種類のフルーツから２種類を選ぶ組み合わせ（順序は問わない）」を求めよう。
使用するテーブルはこちら。
-- 新フルーツテーブル
test=# SELECT * FROM fruits ;
name
--------
apple
banana
orenge
melon
peach
(5 rows)
まずは、５種類のフルーツから２種類選んで並べるパターンだ。
-- 順列
test=# SELECT *
FROM fruits f1 CROSS JOIN fruits f2
WHERE f1.name <> f2.name;
name | name
--------+--------
apple | banana
apple | orenge
apple | melon
apple | peach
banana | apple
banana | orenge
banana | melon
banana | peach
orenge | apple
orenge | banana
orenge | melon
orenge | peach
melon | apple
melon | banana
melon | orenge
melon | peach
peach | apple
peach | banana
peach | orenge
peach | melon
(20 rows)
つづいて、５種類のフルーツから２種類選ぶ組み合わせ。
-- 組み合わせ（コンビネーション）
test=# SELECT *
FROM fruits f1 CROSS JOIN fruits f2
WHERE f1.name < f2.name;
name | name
--------+--------
apple | banana
apple | orenge
apple | melon
apple | peach
banana | orenge
banana | melon
banana | peach
orenge | peach
melon | orenge
melon | peach
(10 rows)
同じテーブルを結合する手法は「自己結合」と呼ばれる。
自己結合で組み合わせを作るテクニックは、『達人に学ぶSQL徹底指南書』から学んだ。この書籍には、更に実務に活かせそうな例がいろいろ載っている。
内部結合(INNER JOIN)
クロス結合では、複数のテーブルを指定し、そこに含まれる行のすべての組み合わせを得た。
それに対して内部結合では、テーブルを結合するキーとなる列名を指定することで、共通の値をもつ行同士を連結する。
具体例を見てみよう。下記の２つのテーブルを使う。
test=# SELECT * FROM table1;
id | t1_value
----+----------
1 | あ
2 | い
3 | う
(3 rows)
test=# SELECT * FROM table2;
id | t2_value
----+----------
2 | か
3 | き
4 | く
(3 rows)
これらのテーブルを結合し、id列が一致する行を連結して表示しよう。
言い換えると、id列をキーとしてtable1とtable2を内部結合する。
-- 内部結合の例
test=# SELECT *
FROM table1 t1 INNER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | id | t2_value
----+----------+----+----------
2 | い | 2 | か
3 | う | 3 | き
(2 rows)
結合の条件（結合のキーとなる列名）をON句で指定している点に注目してもらいたい。
このままではid列が冗長なので、*ではなく列名を明示的に指定しよう。
test=# SELECT t1.id, t1.t1_value, t2.t2_value
FROM table1 t1 INNER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | t2_value
----+----------+----------
2 | い | か
3 | う | き
(2 rows)
内部結合とは（別の解説）
内部結合は、もしかしたら下記のように言ったほうがわかりやすいかもしれない。
内部結合はクロス結合の結果から、ON句で与えられた結合条件を満たさない行を削除する。
『プログラマのためのSQL』P.422
それでは先ほど用意したテーブルをまた使って、クロス結合をした結果を見てみよう。
-- クロス結合
test=# SELECT * FROM table1 CROSS JOIN table2;
id | t1_value | id | t2_value
----+----------+----+----------
1 | あ | 2 | か
1 | あ | 3 | き
1 | あ | 4 | く
2 | い | 2 | か
2 | い | 3 | き
2 | い | 4 | く
3 | う | 2 | か
3 | う | 3 | き
3 | う | 4 | く
(9 rows)
さきほどのON句はON t1.id = t2.idなので、この条件に一致しない行を除外する。
id | t1_value | id | t2_value
----+----------+----+----------
2 | い | 2 | か
3 | う | 3 | き
結合をつかったクエリを読み解くのは、慣れるまで難しいかもしれない。
そうした時に、一度クロス結合を考え、そこからON句で絞り込んでいると考えると、複雑なクエリでも読解のハードルが幾分か下がる。
外部結合(OUTER JOIN)
内部結合が分かっていれば、外部結合は簡単だ。
内部結合では、ON句で指定した条件に当てはまらなかった行は表示されなかったが、外部結合ではその点が異なる。
外部結合では、どちらか一方のテーブルに存在しているならば、そのテーブルの情報が欠けることなく出力されます。
『SQL ゼロからはじめるデータベース操作』
具体例を見よう。使用するテーブルは先ほどと同じだ。
test=# SELECT * FROM table1;
id | t1_value
----+----------
1 | あ
2 | い
3 | う
(3 rows)
test=# SELECT * FROM table2;
id | t2_value
----+----------
2 | か
3 | き
4 | く
(3 rows)
さきほどの引用文でどちらか一方のとあったが、LEFTもしくはRIGHTを付けることで、どちらの情報を残すか指定できる。どちらも残したいならば、FULL(完全外部結合)を指定する。
-- 内部結合の復習
test=# SELECT *
FROM table1 t1 INNER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | id | t2_value
----+----------+----+----------
2 | い | 2 | か
3 | う | 3 | き
(2 rows)
-- 左外部結合
test=# SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | id | t2_value
----+----------+----+----------
1 | あ | |
2 | い | 2 | か
3 | う | 3 | き
(3 rows)
-- 右外部結合
test=# SELECT *
FROM table1 t1 RIGHT OUTER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | id | t2_value
----+----------+----+----------
2 | い | 2 | か
3 | う | 3 | き
| | 4 | く
(3 rows)
-- 完全外部結合
test=# SELECT *
FROM table1 t1 FULL OUTER JOIN table2 t2
ON t1.id = t2.id;
id | t1_value | id | t2_value
----+----------+----+----------
1 | あ | |
2 | い | 2 | か
3 | う | 3 | き
| | 4 | く
(4 rows)
これらの３つの外部結合のうち、左外部結合もっともよく使われる。
単純に右より左のほうをマスターにしたほうが読みやすいからだろう。完全外部結合は、そもそもさほど使われない。
また、LEFTやRIGHTで情報を残すテーブルとして指定されたテーブル（完全外部結合ならば両方）で、結合キーが一致せずに入れるべき値が存在しない箇所（上記の例で空欄となっている箇所）にはnullが入れられる。
参考文献
- 『達人に学ぶSQL徹底指南書』(ミック著、翔泳社)
- 『プログラマのためのSQL 第４版』（ジョー・セルコ著、ミック監訳、翔泳社）
- 『SQL ゼロから始めるデータベース操作』（ミック著、翔泳社）