本記事の概要

SQL入門ハンズオン「はじめてのSQL」第1回資料
の続編です。
　（※ SQL全くの初めて！DBって何？って方は、まずは↑を読んでみてください〜）

エンジニアリング質問プラットフォーム・PRIME ANSWERで主催しているSQL入門勉強会 2回目の資料として執筆しました。わいわい！☺️

はじめに

今回のゴール

SQLでJOINを行えるようになる
各種JOINの挙動の違いを理解して使い分けられるようになる

前提知識

JOIN（テーブルの結合）とは？

データベースからデータを取り出す際、ほしいデータの項目が複数のテーブルに及んだり、ある条件に一致するデータを取りたい場合があると思います🌷

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

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

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

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

参考記事
Zenn - SQL素人でも分かるテーブル結合(inner joinとouter join)

JOINの種類

大きく分けてこの2つが存在します👍

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

と、いうことは・・・

INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOINさえわかってればおけまるゴリラということですね👌🦍💚

参考記事
わわわ IT用語辞典 - 内部結合（INNER JOIN）
Qiita - SQL | 分かりにくい JOIN / INNER JOIN / OUTER JOIN / LEFT JOIN / RIGHT JOIN の違い

ハンズオンの事前準備

今回使うブラウザツール：SQLite online

1. データベースを準備しよう（CREATE TABLE & INSERT）

データベースを作って、操作するためのデータを投入しましょう〜！
今回はJOINを学ぶので、くっつけられるように「teams」と「members」の2つのテーブルを用意します。

teamには0〜複数人のmemberが所属するよ
memberは1つのteamに所属するか、所属先のteamがない人もいるよ

操作手順

SQLite online の画面左側のメニューから「PostgreSQL」を選択＞ Click to Connect
下記を画面内にコピペして、画面左上の「Run」を押下

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して確認）

SELECT * FROM teams;

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つのテーブルの合体可能なデータのみ取り出す〜

基本構文

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

SELECT句
どのカラムの値が欲しいか
FROM句
データを取得したいテーブル（くっつけられる側）
INNER JOIN句
内部結合する対象のテーブル（くっつける側）
ON句
くっつけるための条件
WHERE句
データを絞り込むための検索条件

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

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

【構文の意味】

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

【取得結果】

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を組み合わせてみようッ！ 🤛🦍

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

↓

↓

↓

↓

↓

【回答】

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つのテーブルの合体可能なデータに加え、左側のテーブルにしか存在しないデータも取り出す〜

基本構文

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

SELECT句
どのカラムの値が欲しいか
FROM句
データを取得したいテーブル（左側にあるイメージ）
LEFT OUTER JOIN句
左外部結合する対象のテーブル（右側にあるイメージ）
ON句
くっつけるための条件
WHERE句
データを絞り込むための検索条件

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

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順に並べた方が実行結果が見やすいので付けています

【取得結果】

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テーブルをくっつけられず取得できなかったディアボロ、今回は取得されていますね！
~~🤴 「帝王」はこのディアボロだッ!!依然変わりなくッ！~~

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

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

↓

↓

↓

↓

↓

【回答】

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つのテーブルの合体可能なデータに加え、右側のテーブルにしか存在しないデータも取り出す〜

基本構文

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

SELECT句
どのカラムの値が欲しいか
FROM句
データを取得したいテーブル（左側にあるイメージ）
RIGHT OUTER JOIN句
右外部結合する対象のテーブル（右側にあるイメージ）
ON句
くっつけるための条件
WHERE句
データを絞り込むための検索条件

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

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順に並べた方が実行結果が見やすいので付けています

【取得結果】

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テーブルにくっつけられない）ディアボロは取得されていませんね👼

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

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

↓

↓

↓

↓

↓

【回答】

リーダー（幹部）もメンバーもいないチームの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	麻薬チーム

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

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

リーダー（幹部）もメンバーもいないチームの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句で日本語名を付けてみましょう〜！

取得結果の列名をわかりやすくしたい時

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句で別名を付けちゃいましょう〜！

長いテーブル名を楽に書きたい時

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の構文を理解した上で、思い通りにデータを抽出できるよう
ぜひ色んなパターンを試してみてください😊✨

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