SQLのJOIN(INNER JOIN,OUTER JOIN)について解説する
JOINとは
JOINとはリレーショナルデータベースにおいて、テーブルとテーブルを結合することです。
結合することで複数のテーブルから一つずつデータを取り出すより効率よくデータを取得することができます。
例えば、ユーザーテーブルと投稿テーブルが存在し、ユーザーは複数の投稿を作成可能だとします。
その際にユーザーの名前と投稿タイトル一覧を取得したい場合、JOINなしだとユーザーテーブルを検索してユーザー名を取得するSQLと、投稿テーブルを検索して、投稿タイトル一覧を取得するSQLを書かなくてはなりません。
2回SQLを発行するのは手間ですし、パフォーマンス的にもよろしくないため1回のSQLでデータを取得できるようにJOINを利用するわけです。
JOINを利用すると、複数のテーブルをあたかも１つのテーブルであるかのように扱うことができます。
JOINの種類
JOINには複数種類が存在しますが主に使うのは以下の二種類です
- INNER JOIN
- OUTER JOIN
それぞれについて解説します。
INNER JOIN
INNER JOINは内部結合とも呼ばれるもので、二つのテーブルを結合して結合対象が存在するレコードのみ表示します。
例として、以下でユーザーテーブルと投稿テーブルをINNER JOINしてみます。
- ユーザーテーブル
| ID | 苗字 | 名前 | 年齢 |
|---|---|---|---|
| 1 | 山田 | 太一 | 25 |
| 2 | 大山 | 洋介 | 42 |
| 3 | 田辺 | 由美 | 32 |
| 4 | 大河内 | 真和 | 29 |
- 投稿テーブル
| ID | USER_ID | タイトル | 本文 |
|---|---|---|---|
| 1 | 3 | サンプルタイトル001 | サンプル本文001 |
| 2 | 2 | サンプルタイトル002 | サンプル本文002 |
| 3 | 3 | サンプルタイトル003 | サンプル本文003 |
- 結合後テーブル
| ID | 苗字 | 名前 | 年齢 | ID | USER_ID | タイトル | 本文 |
|---|---|---|---|---|---|---|---|
| 2 | 大山 | 洋介 | 42 | 2 | 2 | サンプルタイトル002 | サンプル本文002 |
| 3 | 田辺 | 由美 | 32 | 1 | 3 | サンプルタイトル001 | サンプル本文001 |
| 3 | 田辺 | 由美 | 32 | 3 | 3 | サンプルタイトル003 | サンプル本文003 |
結合後テーブルを見てみると、ユーザーID001の山田太一さんがいなくなっているのがわかると思います。
これは投稿テーブルのUSER_IDに001が存在せず、結合対象が見つからなかったために除外されてしまったからです。
このようにINNER JOINは結合対象が存在するもののみ取得したい場合に使用します。
INNER JOINを用いてテーブルを結合したい場合のSQLは以下になります。
SELECT *
FROM users
INNER JOIN posts
ON users.id = posts.user_id
FROM句の直後にINNER JOIN句で結合対象のテーブルを指定し、その次のON句でどのカラムで結合するのかを指定します。
さらにWHERE句で条件を指定することができ、その際のSQLと結合後テーブルは以下のようになります。
SELECT *
FROM users
INNER JOIN posts
ON users.id = posts.user_id
WHERE posts.id == 1
- 結合後テーブル
| ID | 苗字 | 名前 | 年齢 | ID | USER_ID | タイトル | 本文 |
|---|---|---|---|---|---|---|---|
| 3 | 田辺 | 由美 | 32 | 1 | 3 | サンプルタイトル001 | サンプル本文001 |
OUTER JOIN
次にOUTER JOINです。
OUTER JOINは外部結合とも呼ばれ、INNER JOINとは逆に結合対象が存在しないレコードも取得します。
この際に、左側のテーブルを基準にする場合はLEFT OUTER JOIN句を使用し、右側のテーブルを基準にする場合はRIGHT OUTER JOIN句を使用します。
言葉だけだとイメージしづらいと思いますので、こちらも実テーブルを用いて見ていきましょう。
- 顧客テーブル
| ID | 苗字 | 名前 | 年齢 |
|---|---|---|---|
| 1 | 山田 | 太一 | 25 |
| 2 | 大山 | 洋介 | 42 |
| 3 | 田辺 | 由美 | 32 |
| 4 | 大河内 | 真和 | 29 |
- 商品テーブル
| ID | 購入者ID | 商品名 | カテゴリ |
|---|---|---|---|
| 1 | 3 | テレビ | 家電 |
| 2 | 5 | 花瓶 | 家具 |
| 3 | 4 | ペンケース | 文房具 |
| 4 | 1 | 漫画 | 書籍 |
以上の２テーブルを用いてJOINをしていきます。
まずは、LEFT OUTER JOINです。
SQLは以下のようになります。
SELECT *
FROM costomers
LEFT OUTER JOIN goods
ON costomers.id = goods.costomer_id
INNER JOINと基本は同じ書き方で、INNER JOIN句をそのままLEFT OUTER JOIN句に書き換えます。
そして結合されたテーブルは以下のようになります。
- 結合後テーブル
| ID | 苗字 | 名前 | 年齢 | ID | 購入者ID | 商品名 | カテゴリ |
|---|---|---|---|---|---|---|---|
| 1 | 山田 | 太一 | 25 | 4 | 1 | 漫画 | 書籍 |
| 2 | 大山 | 洋介 | 42 | null | null | null | null |
| 3 | 田辺 | 由美 | 32 | 1 | 3 | テレビ | 家電 |
| 4 | 大河内 | 真和 | 29 | 3 | 4 | ペンケース | 文房具 |
この場合左側のテーブルはcostomersテーブルとなり、こちらが基準として結合が行われます。
つまり、costomersテーブルは結合対象が存在しなくても前レコードが取得されます。
その際に右側のgoodsテーブルにレコードが存在しない場合は該当箇所が全てnullで取得されます。
次に同じテーブルを用いてRIGHT OUTER JOINをしてみます。
SQLは以下の通りです。
SELECT *
FROM costomers
RIGHT OUTER JOIN goods
ON costomers.id = goods.costomer_id
こちらもINNER JOINの時とほぼ同じで、INNER JOIN句をRIGHT OUTER JOIN句に置き換えるだけです。
結合されたテーブルは以下になります。
- 結合後テーブル
| ID | 苗字 | 名前 | 年齢 | ID | 購入者ID | 商品名 | カテゴリ |
|---|---|---|---|---|---|---|---|
| 3 | 田辺 | 由美 | 32 | 1 | 3 | テレビ | 家電 |
| null | null | null | null | 2 | 5 | 花瓶 | 家具 |
| 4 | 大河内 | 真和 | 29 | 3 | 4 | ペンケース | 文房具 |
| 1 | 山田 | 太一 | 25 | 4 | 1 | 漫画 | 書籍 |
RIGHT OUTER JOINの場合は右側のgoodsテーブルを基準に結合が行われます。
goodsテーブルは結合対象がなくても全件取得され、左側のcostomersテーブルにレコードが存在しない場合は該当箇所が全てnullで取得されます。
最後に
以上が基本的なJOINの使用方法になります。
INNER JOINとOUTER JOINはSQLで頻繁に使用することになると思うので、書き方と動きはしっかりと理解しておきましょう。
Discussion