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

Last updated at 2024-05-12Posted at 2024-05-12

本記事の概要

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

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

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

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

本記事の概要

はじめに

今回のゴール

前提知識

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

JOINの種類

と、いうことは・・・

ハンズオンの事前準備

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

操作手順

2. 作った2つのテーブルを確認してみよう

こんな表ができていたかな？

手を動かしながらJOINを学ぶッ！

INNER JOIN（内部結合）

基本構文

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

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

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

LEFT OUTER JOIN（左外部結合）

基本構文

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

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

RIGHT OUTER JOIN（右外部結合）

基本構文

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

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

おまけ（AS句について）

列名を日本語でわかりやすくしたい時

めっちゃ長いテーブル名、SQLで長々と書いてられへん！って時

おわりに

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