SQL入門ハンズオン「はじめてのSQL」 第2回資料 〜JOIN編〜

# 本記事の概要
[SQL入門ハンズオン「はじめてのSQL」 第1回資料
](https://qiita.com/doiko/items/0b4c5353dccd8497b0a8)の続編です。
　（※ SQL全くの初めて！DBって何？って方は、まずは↑を読んでみてください〜）

エンジニアリング質問プラットフォーム・[PRIME ANSWER](https://primeanswer.jp/)で主催している[SQL入門勉強会 2回目](https://primeanswer.connpass.com/event/315752/)の資料として執筆しました。わいわい！☺️

# はじめに
## 今回のゴール
- SQLでJOINを行えるようになる
- 各種JOINの挙動の違いを理解して使い分けられるようになる

# 前提知識
## JOIN（テーブルの結合）とは？
データベースからデータを取り出す際、ほしいデータの項目が複数のテーブルに及んだり、ある条件に一致するデータを取りたい場合があると思います🌷

> 例） members（メンバー）・teams（メンバーが所属するチーム）の2つのテーブルがある場合に、年齢が20歳以上のメンバーの「名前」と「所属チーム名」を取得したい

その際、上の例の場合に「membersテーブルで20歳以上の人の名前を調べる -> その人たちのteam_idカラムとteamsテーブルのIDを照合してチーム名を確認する」みたいなことをやると、時間がかかるしめちゃ面倒ですよね😳

なのでこういった場合は、「teamsとmembersのテーブルをくっつけて、そこから検索する」という方法を取ります。

この「くっつける」ことを「結合」と呼び、その方法として <font color="Red">`内部結合`</font> や <font color="Red">`外部結合`</font> などといったものが存在しています。

- 参考記事
[Zenn - SQL素人でも分かるテーブル結合(inner joinとouter join)
](https://zenn.dev/naoki_mochizuki/articles/60603b2cdc273cd51c59)

## JOINの種類
大きく分けてこの2つが存在します👍
- 内部結合
  - 2つのテーブルの合体可能なデータのみ取り出す
  - INNER JOIN
- 外部結合
  - 2つのテーブルの合体可能なデータに加え、どちらかのテーブルにしか存在しないデータも取り出す
  - LEFT OUTER JOIN、RIGHT OUTER JOIN
    - 他にもあるけど、一般的に使われるのはこの2つ（特にLEFT OUTER JOIN）
    - 一般的には左側のデータを基準にすることが多いのでLEFT OUTER JOINがよく使われますが、クエリがより直感的に理解しやすくなるようにRIGHT OUTER JOINを使うこともあるみたいです。

### と、いうことは・・・
<font color="Red">`INNER JOIN`</font>、<font color="Red">`LEFT OUTER JOIN`</font>、<font color="Red">`RIGHT OUTER JOIN`</font>さえわかってればおけまるゴリラということですね👌🦍💚

- 参考記事
[わわわ IT用語辞典 - 内部結合（INNER JOIN）](https://wa3.i-3-i.info/word1262.html)
[Qiita - SQL | 分かりにくい JOIN / INNER JOIN / OUTER JOIN / LEFT JOIN / RIGHT JOIN の違い](https://qiita.com/Yinaura/items/3ab6fc49ae55f52e2d55)

# ハンズオンの事前準備
今回使うブラウザツール：[SQLite online](https://sqliteonline.com/)

## 1. データベースを準備しよう（CREATE TABLE & INSERT）
データベースを作って、操作するためのデータを投入しましょう〜！
今回はJOINを学ぶので、くっつけられるように「teams」と「members」の2つのテーブルを用意します。
- teamには0〜複数人のmemberが所属するよ
- memberは1つのteamに所属するか、所属先のteamがない人もいるよ

### 操作手順
- [SQLite online](http://sqliteonline.com) の画面左側のメニューから「PostgreSQL」を選択 ＞ Click to Connect
- 下記を画面内にコピペして、画面左上の「Run」を押下

```sql
CREATE TABLE teams
    ("id" int, "name" varchar(20), "has_leader" boolean)
;

INSERT INTO teams
    ("id", "name", "has_leader")
VALUES
    (1, '護衛チーム', TRUE),
    (2, '暗殺チーム', FALSE),
    (3, '麻薬チーム', FALSE)
;

CREATE TABLE members
    ("id" int, "name" varchar(20), "age" int, "team_id" int)
;

INSERT INTO members
    ("id", "name", "age", "team_id")
VALUES
    (1, 'ジョルノ・ジョバァーナ', 15, 1),
    (2, 'ブローノ・ブチャラティ', 20, 1),
    (3, 'リゾット・ネエロ', 28, 2),
    (4, 'ディアボロ', 35, NULL),
    (5, 'プロシュート兄貴', 28, 2)
;
```

🦍 \\\ ゴゴゴゴゴゴ // 🦍

## 2. 作った2つのテーブルを確認してみよう
※ まずは前回コピペ実行したクエリを消してから、下記のクエリをコピペしてRunしようッ！
※ 下記のコードはそれぞれ別々に実行するッ！
（teamsの表をSELECTして確認した後、そのクエリを消してmembersの表をSELECTして確認）

```sql
SELECT * FROM teams;
```

```sql
SELECT * FROM members;
```

- 「＊」（アスタリスク）＝　全ての列
- SELECT　列名　FROM　テーブル名　で　指定した列名の列を取得

### こんな表ができていたかな？
コンセプトは「イタリアのギャング（マフィア）のチームと各構成員」だッ！
（ジョジョ5部を読むとさらに楽しめるぞッ！💛🦍💃🧡）

- teams
  - has_leader: リーダー（幹部）がいるか
  - SQLite ONLINEではTRUE: t、FALSE: fと表示されます

| id  | name       | has_leader |
| --- | ---------- | ---------- |
| 1   | 護衛チーム | t        |
| 2   | 暗殺チーム | f        |
| 3   | 麻薬チーム | f        |


- members

| id  | name                   | age | team_id | 
| --- | ---------------------- | --- | ------- | 
| 1   | ジョルノ・ジョバァーナ | 15  | 1       | 
| 2   | ブローノ・ブチャラティ | 20  | 1       | 
| 3   | リゾット・ネエロ       | 28  | 2       | 
| 4   | ディアボロ             | 35  | NULL    | 
| 5   | プロシュート兄貴       | 28  | 2       | 

# 手を動かしながらJOINを学ぶッ！
## INNER JOIN（内部結合）
〜 2つのテーブルの合体可能なデータのみ取り出す 〜

### 基本構文

```sql
SELECT
  カラム1, カラム2
FROM
  テーブルA
INNER JOIN
  テーブルB
ON
  結合の条件
WHERE
  検索条件
```

- SELECT句
どのカラムの値が欲しいか

- FROM句
データを取得したいテーブル（くっつけられる側）

- INNER JOIN句
内部結合する対象のテーブル（くっつける側）

- ON句
くっつけるための条件

- WHERE句
データを絞り込むための検索条件

### 具体例を見てみようッ！ 👀🦍

```sql
SELECT
  *
FROM
  members
INNER JOIN
  teams
ON
  members.team_id = teams.id
```

【構文の意味】
- membersテーブルに、membersテーブルのteam_idとteamsテーブルのidを対応させた状態でteamsテーブルをくっつけたいよ
- 内部結合 = くっつけられるデータだけを取得したいよ
- 全てのカラムを取得したいよ
<br>
【取得結果】

| members.id | members.name           | members.age | members.team_id | teams.id | teams.name | has_leader | 
| ---------- | ---------------------- | ----------- | --------------- | -------- | ---------- | ---------- |
| 1          | ジョルノ・ジョバァーナ | 15          | 1               | 1        | 護衛チーム | t        |
| 2          | ブローノ・ブチャラティ | 20          | 1               | 1        | 護衛チーム | t        |
| 3          | リゾット・ネエロ       | 28          | 2               | 2        | 暗殺チーム | f        | 
| 5          | プロシュート兄貴       | 28          | 2               | 2        | 暗殺チーム | f        |

元のmembersテーブルと比べて、どうでしょう？
（ディアボロがいなくなっていますね・・・！ ~~帝王なのに~~ ）

元のmembersテーブルを見てもらうと、ディアボロのteam_idだけNULL（値が存在しない＝どのチームにも所属していない。 ~~パッショーネのボスだからな~~ ）でしたよね🙆‍♀️

内部結合の場合、合体可能なデータだけが取得されるので
membersテーブルのteam_idがNULLのディアボロは、teamsテーブルにくっつけられるデータ（team_idがteams.idと一致するデータ）が存在しないので取得されないのです。

### WHEREを組み合わせてみようッ！ 🤛🦍

```sql
SELECT
  members.id, members.name, members.age
FROM
  members
INNER JOIN
  teams
ON
  members.team_id = teams.id
WHERE
  teams.name = '護衛チーム'
```

まずはこのクエリで「何のデータを取得できるのか」読み解いてみましょう〜！

考えた後はクエリをコピペしてSQLiteで実行して、仮説が合っているか検証してみましょう✨

↓

↓

↓

↓

↓

【回答】
チーム名が「護衛チーム」のメンバーの、ID・名前・年齢を取得するためのクエリでした〜！
~~（暗チもよいけど護チ大好き😍）~~

| members.id | members.name           | members.age |
| ---------- | ---------------------- | ----------- |
| 1          | ジョルノ・ジョバァーナ | 15          |
| 2          | ブローノ・ブチャラティ | 20          |

### 取得したい内容から考えてSQLを書いてみようッ！ 🔨🦍

- 20歳以上でチームに所属しているメンバーの名前・年齢・所属チーム名を取得してください
\\\ チームに所属しているメンバーの中で、酒が飲めるのは誰だッ！？🍻🦍 //

↓

↓

↓

↓

↓

【回答】

```sql:20歳以上でチームに所属しているメンバーの名前・年齢・所属チーム名を取得
SELECT
  members.name, members.age, teams.name
FROM
  members
INNER JOIN
  teams
ON
  members.team_id = teams.id
WHERE
  members.age >= 20
```

| members.name           | members.age | teams.name | 
| ---------------------- | ----------- | ---------- | 
| ブローノ・ブチャラティ | 20          | 護衛チーム | 
| リゾット・ネエロ       | 28          | 暗殺チーム |
| プロシュート兄貴       | 28          | 暗殺チーム | 

表と同じ取得結果になりましたか？☺️
ぜひ他にもいろんな取得条件でSQLを考えて・書いて実行してみてくださいね〜

例）
- 20歳以上のメンバーが所属しているチームのID、名前を取得（ただし重複を省く）

## LEFT OUTER JOIN（左外部結合）
〜 2つのテーブルの合体可能なデータに加え、左側のテーブルにしか存在しないデータも取り出す 〜

### 基本構文

```sql
SELECT
  カラム1, カラム2
FROM
  テーブルA
LEFT OUTER JOIN
  テーブルB
ON
  結合の条件
WHERE
  検索条件
```

- SELECT句
どのカラムの値が欲しいか

- FROM句
データを取得したいテーブル（左側にあるイメージ）

- LEFT OUTER JOIN句
左外部結合する対象のテーブル（右側にあるイメージ）

- ON句
くっつけるための条件

- WHERE句
データを絞り込むための検索条件

### 具体例を見てみようッ！ 👀🦍

```sql
SELECT
  *
FROM
  members
LEFT OUTER JOIN
  teams
ON
  members.team_id = teams.id
ORDER BY
  members.id ASC  
```

【構文の意味】
- membersテーブルに、membersテーブルのteam_idとteamsテーブルのidを対応させた状態でteamsテーブルをくっつけたいよ
- 左外部結合 = membersテーブルにあるデータは全て取得したいよ
- 全てのカラムを取得したいよ
- members.idの昇順で並べたいよ
  - id順に並べた方が実行結果が見やすいので付けています 
<br>
【取得結果】

| members.id | members.name           | members.age | members.team_id | teams.id | teams.name | has_leader |
| ---------- | ---------------------- | ----------- | --------------- | -------- | ---------- | ---------- |
| 1          | ジョルノ・ジョバァーナ | 15          | 1               | 1        | 護衛チーム | t        | 
| 2          | ブローノ・ブチャラティ | 20          | 1               | 1        | 護衛チーム | t        |
| 3          | リゾット・ネエロ       | 28          | 2               | 2        | 暗殺チーム | f        |
| 4          | ディアボロ             | 35          | NULL            | NULL     | NULL       |  NULL     | 
| 5          | プロシュート兄貴       | 28          | 2               | 2        | 暗殺チーム | f        |

INNER JOINした結果と比べて、どうでしょう？

内部結合の時はteams_idがNULLのためにteamsテーブルをくっつけられず取得できなかったディアボロ、今回は取得されていますね！
~~🤴 「帝王」はこのディアボロだッ!!依然変わりなくッ！~~
<br>

### 取得したい内容から考えてSQLを書いてみようッ！ 🔨🦍

- 25歳以上のメンバー全員の名前・年齢・チーム名（あれば）を取得してください
\\\ アラサー以上のやつは誰だッ！？大人の魅力を追求していこう🦍❣️ //

↓

↓

↓

↓

↓

【回答】

```sql:25歳以上のメンバー全員の名前・年齢・チーム名（あれば）を取得
SELECT
  members.name, members.age, teams.name
FROM
  members
LEFT OUTER JOIN
  teams
ON
  members.team_id = teams.id
WHERE
  members.age >= 25
```

| members.name           | members.age | teams.name | 
| ---------------------- | ----------- | ---------- | 
| リゾット・ネエロ       | 28          | 暗殺チーム |
| プロシュート兄貴       | 28          | 暗殺チーム | 
| ディアボロ | 35          | NULL | 

表と同じ取得結果になりましたか？☺️


## RIGHT OUTER JOIN（右外部結合）
〜 2つのテーブルの合体可能なデータに加え、右側のテーブルにしか存在しないデータも取り出す 〜

### 基本構文

```sql
SELECT
  カラム1, カラム2
FROM
  テーブルA
RIGHT OUTER JOIN
  テーブルB
ON
  結合の条件
WHERE
  検索条件
```

- SELECT句
どのカラムの値が欲しいか

- FROM句
データを取得したいテーブル（左側にあるイメージ）

- RIGHT OUTER JOIN句
右外部結合する対象のテーブル（右側にあるイメージ）

- ON句
くっつけるための条件

- WHERE句
データを絞り込むための検索条件

### 具体例を見てみようッ！ 👀🦍

```sql
SELECT
  *
FROM
  members
RIGHT OUTER JOIN
  teams
ON
  members.team_id = teams.id
ORDER BY
  members.id ASC  
```

【構文の意味】
- membersテーブルに、membersテーブルのteam_idとteamsテーブルのidを対応させた状態でteamsテーブルをくっつけたいよ
- 右外部結合 = teamsテーブルにあるデータは全て取得したいよ
- 全てのカラムを取得したいよ
- members.idの昇順で並べたいよ
  - id順に並べた方が実行結果が見やすいので付けています 
<br>
【取得結果】

| members.id | members.name           | members.age | members.team_id | teams.id | teams.name | has_leader |
| ---------- | ---------------------- | ----------- | --------------- | -------- | ---------- | ---------- |
| 1          | ジョルノ・ジョバァーナ | 15          | 1               | 1        | 護衛チーム | t        | 
| 2          | ブローノ・ブチャラティ | 20          | 1               | 1        | 護衛チーム | t        |  
| 3          | リゾット・ネエロ       | 28          | 2               | 2        | 暗殺チーム | f        | 
| 5          | プロシュート兄貴       | 28          | 2               | 2        | 暗殺チーム | f        |
| NULL       | NULL       | NULL          | NULL               | 3        | 麻薬チーム | f        |

LEFT OUTER JOINした結果と比べてどうでしょう？

右側のテーブル＝teamsテーブルにあるデータは全て取得されるので、membersに所属メンバーがいない「麻薬チーム」も表示され、membersのカラムは全てNULLになっていますね！

逆に、どのチームにも所属していない（teamsテーブルにくっつけられない）ディアボロは取得されていませんね👼
<br>

### 取得したい内容から考えてSQLを書いてみようッ！ 🔨🦍

- リーダー（幹部）もメンバーもいないチームのID・名前を取得してください
\\\ メンバー雇わないとヤベェチームはどれだッ！？🔍🦍 //

↓

↓

↓

↓

↓

【回答】

```sql:リーダー（幹部）もメンバーもいないチームのID・名前を取得
SELECT
  teams.id, teams.name
FROM
  members
RIGHT OUTER JOIN
  teams
ON
  members.team_id = teams.id
WHERE
  teams.has_leader = FALSE AND members.team_id is NULL
```

「membersにくっつけられるレコードがない」を、「右外部結合した結果のmembersのteam_idがNULL」で判定できますね！✨

| teams.id  | teams.name       |
| --- | ---------- |
| 3   | 麻薬チーム |

表と同じ取得結果になりましたか？🤓
<br>

💡 **ちなみに、上記の条件のSQLをLEFT OUTER JOINで書くと以下のようになります**

```sql:リーダー（幹部）もメンバーもいないチームのID・名前を取得
SELECT
  teams.id, teams.name
FROM
  teams
LEFT OUTER JOIN
  members
ON
  teams.id = members.team_id
WHERE
  teams.has_leader = FALSE AND members.team_id is NULL
```

- 左のテーブルのデータは（くっつけられないものも）全てほしい
- 右のテーブルのデータは（くっつけられないものも）全てほしい

の違いなので、`FROM` と `XXX OUTER JOIN` に記述するテーブル名を入れ替えれば良いだけですもんね☺️

（※ ちなみに、左外部結合と右外部結合が混在するとカオスになるので、基本は右外部結合は使わず全て左外部結合で書く方が無難かなーと筆者は思います。）

## おまけ（AS句について）
〜 カラム名やテーブル名にAS句を使って別名をつけて、長いテーブル名を楽に書いたり取得結果をわかりやすくしようッ！ 〜

### 列名を日本語でわかりやすくしたい時
取得結果の列名が英語でわかりにくい！日本語でオネシャス！って思ったそこのあなた🌷
SELECT句の各カラムにAS句で日本語名を付けてみましょう〜！

```sql:取得結果の列名をわかりやすくしたい時
SELECT
  members.id AS "ID",
  members.name AS "名前",
  members.age AS "年齢",
  teams.name AS "所属チーム名"
FROM
  members
INNER JOIN
  teams
ON
  members.team_id = teams.id
ORDER BY
  members.id ASC
```

**あら、いいわね・・・✨👩**

| ID | 名前           | 年齢 | 所属チーム名 |
| ---------- | ---------------------- | ----------- | --------------- |
| 1          | ジョルノ・ジョバァーナ | 15          | 護衛チーム |
| 2          | ブローノ・ブチャラティ | 20          | 護衛チーム |
| 3          | リゾット・ネエロ      | 28          | 暗殺チーム |
| 5          | プロシュート兄貴      | 28          | 暗殺チーム |

### めっちゃ長いテーブル名、SQLで長々と書いてられへん！って時
今回の例題のものは短いですが、たとえば `super_gorilla_engineers` みたいにめっちゃ長いテーブル名だと
SQLで毎回 `super_gorilla_engineers.id` とか書くの面倒だし、読みづらくなっちゃいますよね・・・！

そんな時はテーブルにAS句で別名を付けちゃいましょう〜！

```sql:長いテーブル名を楽に書きたい時
SELECT
  m.id AS "ID",
  m.name AS "名前",
  m.age AS "年齢",
  ｔ.name AS "所属チーム名"
FROM
  members AS m
INNER JOIN
  teams AS t
ON
  m.team_id = t.id
ORDER BY
  m.id ASC
```

**あら、楽じゃのう・・・✨👵**

※ 上記はあくまで一例です。長いSQLクエリを書く場合に、別名で省略するのをやりすぎると可読性が下がるケースもあるので必要に応じてお使いください〜

# おわりに

今回はJOINの超基本を学びました！
SQLの構文を理解した上で、思い通りにデータを抽出できるよう
ぜひ色んなパターンを試してみてください😊✨

👏😆 お疲れさまでしたァン！！💞