【SQL】JOIN演算を体系的に理解する
JOIN演算を体系的に理解する
なんとなく使用してしまっているJOIN演算について体系的に理解を深める。
JOINの分類
CROSS JOIN とは
CROSS JOINとは2つのテーブルの 直積(デカルト積) を指します。
後ほど紹介するINNER JOIN,OUTER JOINの理解を補助するために、まずは直積(デカルト積)について紹介します。
直積(デカルト積)
直積(デカルト積)とは、以下のように2つのテーブルのそれぞれの行をすべて結合して一つのテーブルにすることを言います。一方のテーブルの行数がM行、もう一方がN行とすると、結果は(M * N)行になります。
FROM A CROSS JOIN B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
また、CROSS JOINは、カンマを使用して暗黙的に記述できます。(カンマクロス結合)
FROM A, B
Table A Table B Result
+-------+ +-------+ +---------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------+
| 1 | a | | 2 | c | | 1 | a | 2 | c |
| 2 | b | | 3 | d | | 1 | a | 3 | d |
+-------+ +-------+ | 2 | b | 2 | c |
| 2 | b | 3 | d |
+---------------+
[INNER] JOIN とは
[INNER] JOINは2つのテーブルの直積(デカルト積)を計算せずに内部結合を実行します
また、構文は一種類ですが、内部結合にはいくつかの種類があることをご存知でしょうか？
以下では、三種類の内部結合を引き合いに、ON句、USING句との関係について触れながら[INNER] JOIN の詳細を記述していきます。
3つの内部結合
θ結合(シータ結合)
上述のFROM A CROSS JOIN Bにより直積したテーブルを思い出してください。
θ結合のθとは比較演算子(=、>、<等)のことで、直積したテーブルから2つの属性(列)X, Yを選択し、その2つの属性において、選択条件「X θ Y」が成立する行を取り出すことをいいます。
θ結合(A.w < B.y)
A, B(直積) Result
+-----------------------+ +-----------------------+
| A.w | A.x | B.y | B.z | -> | A.w | A.x | B.y | B.z |
+-----------------------+ +-----------------------+
| 1 | a | 2 | c | | 1 | a | 2 | c |
| 1 | a | 3 | d | | 1 | a | 3 | d |
| 2 | b | 2 | c | | 2 | b | 3 | d |
| 2 | b | 3 | d | +-----------------------+
+-----------------------+
お気づきかもしれないですが、このθが、JOIN演算でおなじみON句で指定する結合条件にあたります。
ON句はWHERE句と同じ書式で結合条件を指定することが可能です。
FROM A [INNER] JOIN B ON w < y
Table A Table B (直積省略) Result
+-------+ +-------+ (+---------------+) +---------------+
| w | x | * | y | z | = (| w | x | y | z |) = | w | x | y | z |
+-------+ +-------+ (+---------------+) +---------------+
| 1 | a | | 2 | c | (| 1 | a | 2 | c |) | 1 | a | 2 | c |
| 2 | b | | 3 | d | (| 1 | a | 3 | d |) | 1 | a | 3 | d |
+-------+ +-------+ (| 2 | b | 2 | c |) | 2 | b | 3 | d |
(| 2 | b | 3 | d |) +---------------+
(+---------------+)
等結合
等結合とは、θ結合で指定する比較演算子に=を選択した結合のことをいいます。
つまり、on句のあとに2つの属性を=で指定したものといえます。ON句の活用においてはかなり一般的ですね。
等結合:θ結合(A.w = B.y)
A, B Result
+-----------------------+ +-----------------------+
| A.w | A.x | B.y | B.z | -> | A.w | A.x | B.y | B.z |
+-----------------------+ +-----------------------+
| 1 | a | 2 | c | | 2 | b | 2 | c |
| 1 | a | 3 | d | +-----------------------+
| 2 | b | 2 | c |
| 2 | b | 3 | d |
+-----------------------+
自然結合
直積の自然結合は、USING句を用いた[INNER] JOINにあたります。
つまり、自然結合とは等結合(θ：=)で選択した2つの属性(列)から一方を削除したものです。
自然結合(A.w = B.y)
A, B θ結合:等結合(A.w = B.y) Result
+-----------------------+ +-----------------------+ +-----------+
| A.w | A.x | B.y | B.z | -> | A.w | A.x | B.y | B.z | -> | A | B | C |
+-----------------------+ +-----------------------+ +-----------+
| 1 | a | 2 | c | | 2 | b | 2 | c | | 2 | b | c |
| 1 | a | 3 | d | +-----------------------+ +-----------+
| 2 | b | 2 | c |
| 2 | b | 3 | d |
+-----------------------+
FROM A [INNER] JOIN B USING(w)
Table A Table B (直積省略) Result
+-------+ +-------+ (+---------------+) +-----------+
| w | x | * | w | z | = (| w | x | w | z |) = | w | x | z |
+-------+ +-------+ (+---------------+) +-----------+
| 1 | a | | 2 | c | (| 1 | a | 2 | c |) | 2 | b | c |
| 2 | b | | 3 | d | (| 1 | a | 3 | d |) +-----------+
+-------+ +-------+ (| 2 | b | 2 | c |)
(| 2 | b | 3 | d |)
(+---------------+)
まとめ
[INNER] JOINは2つのテーブルの直積(デカルト積)を計算せずに内部結合を実行している-
θ結合とは、直積したテーブルから2つの属性(列)X, Yを選択し、その2つの属性において、選択条件「
X θ Y」が成立する行を取り出す内部結合- ※
θとは比較演算子(=,>,<等)を指す -
※ここでの
θは、ON句で指定する結合条件にあたるもので、ON句はWHERE句と同じ書式で結合条件を指定することが可能
- ※
-
等結合とは、θ結合の比較演算子を
=で指定したもの -
自然結合とは、等結合から共通属性の一方を削除したものであり、直積を自然結合することは
using句を用いた[INNER] JOINに該当する
OUTER JOIN とは
OUTER JOINとは外部結合のことで、FULL/RIGHT/LEFT [OUTER] JOINの三種類の構文が存在します。
外部結合とは、二つのテーブルからそれぞれ共通属性(列)を指定し、値が一致する行に関しては結合し、一致する値が存在しない行もそのまま抽出する(NULLを返す)ものをいい、三種類の構文いずれも上記の概念が基本です。(言語化が微妙すぎるので下記の具体例を参考ください、、)
三種類の構文について、具体例を用いながら記述します。
LEFT [OUTER] JOIN
おそらく三種類の中で最も使用頻度の高い構文だと思います。
LEFTは、左側のテーブルが基準となり外部結合します。
下記の例にあてると、
- A(左側)が基準となり、問答無用にすべてのA(左側)の行は返される
- A(左側)とB(右側)の指定属性で一致する行に関しては結合
- A(左側)とB(右側)の指定属性で一致しない行に関してはB(右側)の行に
NULLを返して結合
-
on句とusing句の違いに関しては上述した等結合と自然結合の違いと同様
FROM A LEFT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL | <- 1
| 2 | b | | 3 | m | | 2 | b | 2 | k | <- 2
| 3 | c | | 3 | n | | 3 | c | 3 | m | <- 2
| 3 | d | | 4 | p | | 3 | c | 3 | n | <- 2
+-------+ +-------+ | 3 | d | 3 | m | <- 2
| 3 | d | 3 | n | <- 2
+---------------------------+
RIGHT [OUTER] JOIN
LEFT JOINと対になる構文です。
RIGHT JOINはメインテーブルがわかりづらいので基本は使用しないはずです。もしも僕のチームで誰かが意味なく使用していたらLEFT JOINに修正してもらいます。
FROM A RIGHT OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 2 | b | 2 | k |
| 2 | b | | 3 | m | | 3 | c | 3 | m |
| 3 | c | | 3 | n | | 3 | c | 3 | n |
| 3 | d | | 4 | p | | 3 | d | 3 | m |
+-------+ +-------+ | 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
FULL [OUTER] JOIN
完全外部結合といい、LEFT/RIGHTをあわせた構文になります。
上述した外部結合の説明がFULL [OUTER] JOINの説明にあたります。
二つのテーブルからそれぞれ共通属性(列)を指定し、値が一致する行に関しては結合し、一致する値が存在しない行もそのまま抽出する(
NULLを返す)もの
FROM A FULL OUTER JOIN B ON A.w = B.y
Table A Table B Result
+-------+ +-------+ +---------------------------+
| w | x | * | y | z | = | w | x | y | z |
+-------+ +-------+ +---------------------------+
| 1 | a | | 2 | k | | 1 | a | NULL | NULL |
| 2 | b | | 3 | m | | 2 | b | 2 | k |
| 3 | c | | 3 | n | | 3 | c | 3 | m |
| 3 | d | | 4 | p | | 3 | c | 3 | n |
+-------+ +-------+ | 3 | d | 3 | m |
| 3 | d | 3 | n |
| NULL | NULL | 4 | p |
+---------------------------+
Discussion