JOINとは
JOINとは、文字通り、二つのテーブルの列を結合させることです。
ここでいう列とは、カラムに関する情報のことを指しています。
SELECT..FROMを例にとってみます。
上記は本来、FROM句で指定したテーブルに含まれる列の情報しか取り出すことができませんが、
JOINを使って他のテーブルと結合させることで、複数のテーブルのデータを、
あたかもひとつのテーブルのデータかのように扱うことができます。
本記事で扱うテーブルとデータ
本記事では、JOIN各種の説明を行うために、ProductテーブルとCategoryテーブルを用いて説明します。
ひとつの商品にはひとつの商品カテゴリーが紐づくものとします。
また、商品カテゴリーはOptionalな値であり、どのカテゴリーにも該当しないものは、category_idにNullを入れるものとします。
テーブル定義
productsテーブル
| カラム名 | データ型 | NULL | 説明 |
|---|---|---|---|
| id | INT | NO | 商品ID |
| name | VARCHAR | NO | 商品名 |
| description | TEXT | YES | 商品説明 |
| price | INT | NO | 商品価格 |
| category_id | INT | YES | 商品カテゴリーID |
categoriesテーブル
| カラム名 | データ型 | NULL | 説明 |
|---|---|---|---|
| id | INT | NO | 商品カテゴリID |
| name | VARCHAR | NO | 商品カテゴリ名 |
想定データ
productsテーブル
| id | name | description | price | category_id |
|---|---|---|---|---|
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL |
categoriesテーブル
| id | name |
|---|---|
| 1 | 食品 |
| 2 | 書籍 |
| 3 | 電子機器 |
| 4 | 家具 |
JOINの種類
JOINには大きく分けて三つの種類があります。
- CROSS JOIN(交差結合)
- INNER JOIN(内部結合)
- OUTER JOIN(外部結合)
本記事では、これらを順番に説明していきます。
CROSS JOIN(交差結合)
最初に滅多に使うことのないCROSS JOINから取り扱います。
というのも、CROSS JOINはJOINの概念を理解するために最もシンプルな例だからです。
JOINという概念については、すでにざっくりとですが説明しました。
2つのテーブルの列を結合し、1つのテーブルのように扱うことです。
そこで、JOINを利用し、productsテーブルとcategoriesテーブルを結合して、
2つのテーブルにまたがる情報を1つのクエリで取り出したいとしましょう。
しかし、両方のテーブルにレコードが複数あるため、どのレコードに対応する列情報を結合すればよいか。という問題が出てきます。
ここで考えられる最もシンプルな方法は、productsテーブルのすべてのレコードの列情報に対し、categoriesテーブルのすべてのレコードの列情報を結合させることです。
そしてこの方法こそがCROSS JOINです。
CROSS JOINは下記のようなクエリで実行できます。
SELECT *
FROM products
CROSS JOIN categories;
CROSS JOIN句で結合したいテーブルを指定するだけでOKです。
そして、実行結果は下記のようになります。
| id(商品ID) | name(商品名) | description | price | category_id | id(商品カテゴリID) | name(商品カテゴリ名) |
|---|---|---|---|---|---|---|
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 4 | 家具 |
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 3 | 電子機器 |
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 2 | 書籍 |
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 1 | 食品 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 4 | 家具 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 3 | 電子機器 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 2 | 書籍 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 1 | 食品 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 4 | 家具 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 3 | 電子機器 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 2 | 書籍 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 1 | 食品 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 4 | 家具 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 3 | 電子機器 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 2 | 書籍 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 1 | 食品 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 4 | 家具 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 3 | 電子機器 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 2 | 書籍 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 1 | 食品 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 4 | 家具 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 3 | 電子機器 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 2 | 書籍 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 1 | 食品 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL | 4 | 家具 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL | 3 | 電子機器 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL | 2 | 書籍 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL | 1 | 食品 |
とてもシンプルですね。
両方のレコード分すべて掛け合わせているので、7 * 4で計28レコードが取得されます。
CROSS JOINは直積結合とも呼ばれ、組み合わせのすべてを網羅するJOINです。
最初に述べた通り、実際に使う機会は滅多にありませんが、これでJOINという概念の意味するところがなんとなくわかってきたかと思います。
INNER JOIN(内部結合)
CROSS JOINはシンプルですが、結果のレコードが大量になってしまうのであまり有用ではありません。
実際にJOINを使用するのは、
ある商品に紐づいている商品カテゴリの情報を一緒に取得したい。
というようなケースが多いです。
そこで、JOINを行うための条件を指定するようにします。
それが、INNER JOINとLEFT OUTER JOINです。
まず、INNER JOINの方を見ていきます。
categoriesテーブルとproductsテーブルは1対多の関係にあるので、各商品レコードは商品カテゴリーIDを持っています。
商品に紐づく商品カテゴリをJOINするためには、productsテーブルの各レコードが持っているcategory_idと一致するidを持つ、categoriesのレコードの列情報を結合してあげれば良いでしょう。
実際に発行するSQLは下記のようになります。
SELECT *
FROM products
INNER JOIN categories ON products.category_id = categories.id;
CROSS JOIN同様、INNER JOINでJOINしたいテーブルを指定します。
CROSS JOINと違うのはON句で結合条件を指定している部分です。
見たまんまですが、productsのcategory_idとcategoriesのidが一致しているレコード同士を結合させるようにしています。
結果は下記のようになります。
| id(商品ID) | name(商品名) | description | price | category_id | id(商品カテゴリID) | name(商品カテゴリ名) |
|---|---|---|---|---|---|---|
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 1 | 食品 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 3 | 電子機器 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 3 | 電子機器 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 4 | 家具 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 4 | 家具 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 3 | 電子機器 |
あれ..?と思った方がいるかもしれません。
そう、productsのなぞのガラクタとcategoriesの書籍の姿が見当たりません。
これはINNER JOINがON句で指定した、両方のテーブルに共通の値を持ってるレコードのみを結果として出力するという特徴があるからです。
なぞのガラクタに該当するレコードはcategory_idがNULLですし、書籍に該当するレコードに紐づくproductsのレコードはありません。
そのため、これらのレコードは結果として出力されません。
このように、二つのテーブルのうち指定した条件をもとに結合し、
共通部分のみを結果として出力するのがINNER JOINです。
OUTER JOIN(外部結合)
結合条件を指定して結合し、共通部分だけを出力するのがINNER JOINでした。
それでは、共通部分以外も出力するJOINもあるはずです。
察しの良い方ならお気づきかと思いますが、それがOUTER JOINです。
OUTER JOINでは、共通部分がないテーブルは、各列情報がNULLとなって結合が行われます。
ただし、共通部分がなくても表示されるのは基本的に片方のテーブルのみです。
なので、どちらのテーブルを基準として結合を行うかが重要となります。
FROM句で指定したテーブルを元にするか、
JOIN句で指定したテーブルを基準とするかで2通りのOUTER JOINが考えられます。
そこで、前者を左側、後者を右側として、
LEFT OUTER JOINとRIGHT OUTER JOINの二つのOUTER JOINが存在します。
両方のテーブルを出力するFULL JOIN, FULL OUTER JOINというクエリが使える場合もありますが、使用するRDBMSに依存する話なので、本記事では取り扱いません。
LEFT OUTER JOIN
LEFT OUTER JOINは左側、つまりFROM句で指定したテーブルを基準として、OUTER JOINを実行します。
実際に発行するSQLは下記のようになります。
SELECT *
FROM products
LEFT OUTER JOIN categories ON products.category_id = categories.id;
INNER JOINの部分がLEFT OUTER JOINになっただけでほぼ一緒です。
結果は、下記のようになります。
| id(商品ID) | name(商品名) | description | price | category_id | id(商品カテゴリID) | name(商品カテゴリ名) |
|---|---|---|---|---|---|---|
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 1 | 食品 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 3 | 電子機器 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 3 | 電子機器 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 4 | 家具 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 4 | 家具 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 3 | 電子機器 |
| 7 | なぞのガラクタ | 正体不明のガラクタです。 | 100 | NULL | NULL | NULL |
無事、FROM句に指定していたproductsのデータはすべて出力されました。
なぞのガラクタには紐づくcategoriesのデータがないため、新たに結合されたid, nameはNULLとなっています。
一方、相変わらず書籍の姿はありません。
RIGHT OUTER JOIN
RIGHT OUTER JOINは右側、つまりJOIN句で指定したテーブルを基準として、OUTER JOINを実行します。
実際に発行するSQLは下記のようになります。
SELECT *
FROM products
RIGHT OUTER JOIN categories ON products.category_id = categories.id;
LEFTがRIGHTになっただけですね。
結果は、下記のようになります。
| id(商品ID) | name(商品名) | description | price | category_id | id(商品カテゴリID) | name(商品カテゴリ名) |
|---|---|---|---|---|---|---|
| 1 | 卵 | 3日以内に採れた新鮮な鶏の卵です | 250 | 1 | 1 | 食品 |
| NULL | NULL | NULL | NULL | NULL | 2 | 書籍 |
| 6 | タブレット | 高性能なタブレットです。 | 60000 | 3 | 3 | 電子機器 |
| 3 | ノートパソコン | 高性能なノートパソコンです。 | 200000 | 3 | 3 | 電子機器 |
| 2 | スマートフォン | 最新のスマートフォンです。 | 15000 | 3 | 3 | 電子機器 |
| 5 | 椅子 | 快適な座り心地の椅子です。 | 1000 | 4 | 4 | 家具 |
| 4 | デスク | オフィス用のデスクです。 | 30000 | 4 | 4 | 家具 |
こんどは無事、書籍が結果に出力され、代わりになぞのガラクタがいなくなりました。
このように、LEFT OUTER JOINとRIGHT OUTER JOINはどちらのテーブルを基準としてOUTER JOINを行うか、という違いがあります。
また、当然ですが、FROM句とJOIN句で指定しているテーブルを入れ替えれば、LEFT OUTER JOINとRIGHT OUTER JOINの結果は同じになります。
つまり、下記2つのSQLは同じ結果になります。
(ただし、*でカラムを指定している場合は、出力される列の順番が異なります。)
SELECT *
FROM products
LEFT OUTER JOIN categories ON products.category_id = categories.id;
SELECT *
FROM categories
RIGHT OUTER JOIN products ON categories.id = products.category_id;
必要なケースに応じて、使い分けましょう。
個人的にはFROM句に指定したテーブルを基準とするのが直感的で分かりやすいのでLEFT OUTER JOINを使うことが多いです。
まとめ
この記事の内容をまとめると次のようになります。
- CROSS JOINは二つのテーブルのすべてのレコードの列情報を結合する
- INNER JOINは、結合条件に一致するレコードの列情報を結合し、共通するレコードのみを出力する
- OUTER JOINは、結合条件に一致するレコードの列情報を結合し、基準としたテーブルのレコードはすべて出力する。
- LEFT OUTER JOINはFROM句で指定したテーブルを基準としてOUTER JOINを実行する。
- RIGHT OUTER JOINはJOIN句で指定したテーブルを基準んとしてOUTER JOINを実行する。
JOINは種類が多く、集合論の話にもなりがちなので、最初はとっつきづらく感じる方も多いと思いますが、
蓋を開けてみればそんなに難しいものではありません。
SQLの中でも必須級のものだと思いますので、ぜひ使いこなしてみてください。