こんにちは!よしななです。
業務中に SQL のJOIN
をする機会があったのですが、挙動についてわからない部分があったので備忘録としてまとめます。
目次
- 実行環境
- JOIN について
- INNER JOIN:内部結合
- LEFT (OUTER) JOIN:左外部結合
- RIGHT (OUTER) JOIN:右外部結合
- FULL JOIN:完全外部結合
- CROSS JOIN:交差結合
実行環境
本記事では、Amazon Athena で SQL を実行しています。
以下の2つの .jsonl データを Amazon S3 にアップロード済みです。
users.jsonl
{"id": "1", "name": "山田太郎", "age": "30"}
{"id": "2", "name": "佐藤花子", "age": "25"}
{"id": "3", "name": "田中一郎", "age": "22"}
{"id": "4", "name": "伊藤美咲", "age": "28"}
{"id": "5", "name": "渡辺健", "age": "35"}
{"id": "6", "name": "小山裕太", "age": "19"}
{"id": "7", "name": "加藤純一", "age": "41"}
books_rental.jsonl
{"id": "1", "title": "プログラミング入門", "rental_day": "2023-01-01"}
{"id": "2", "title": "経済学の基礎", "rental_day": "2023-01-02"}
{"id": "3", "title": "世界史概観", "rental_day": "2023-01-03"}
{"id": "4", "title": "美術の理解", "rental_day": "2023-01-04"}
{"id": "5", "title": "現代物理学の展望", "rental_day": "2023-01-05"}
{"id": "1", "title": "人工知能と社会", "rental_day": "2023-01-06"}
{"id": "2", "title": "子どもと教育", "rental_day": "2023-01-07"}
{"id": "3", "title": "心理学入門", "rental_day": "2023-01-08"}
{"id": "4", "title": "日本の名詩選", "rental_day": "2023-01-09"}
{"id": "5", "title": "健康と栄養", "rental_day": "2023-01-10"}
{"id": "1", "title": "地球環境問題", "rental_day": "2023-01-11"}
{"id": "2", "title": "生物学の基本", "rental_day": "2023-01-12"}
{"id": "3", "title": "宇宙の謎", "rental_day": "2023-01-13"}
{"id": "4", "title": "数学の魅力", "rental_day": "2023-01-14"}
{"id": "5", "title": "情報技術の最前線", "rental_day": "2023-01-15"}
{"id": "1", "title": "歴史的建造物の旅", "rental_day": "2023-01-16"}
{"id": "2", "title": "料理の科学", "rental_day": "2023-01-17"}
{"id": "3", "title": "新しい医療の展開", "rental_day": "2023-01-18"}
{"id": "4", "title": "映画理論入門", "rental_day": "2023-01-19"}
{"id": "5", "title": "音楽史", "rental_day": "2023-01-20"}
上記 .jsonl データは ChatGPT を使用して生成しています。
上記 .jsonl データをもとにCREATE TABLE
を実行し、Amazon Athena 上にusers
テーブル、rental_day
テーブルを作成しています。
※CREATE TABLE
手順は こちらの記事 を参考にしてください。
JOIN について
JOIN
とは、2つ以上のテーブルを結合するSQL構文の一つです。
異なるテーブル間で共通するカラム(キー)を使用し、データを結合します。
JOIN
には複数種類があるため、次項で SQL 構文と実行結果をまとめます。
INNER JOIN:内部結合
2つのテーブルから、指定した条件に一致する行のみを結合します。
users
テーブル、rental_day
テーブルに存在するid
カラムをキーとして、users
テーブル、rental_day
テーブルを結合しています。
SELECT * FROM users
INNER JOIN rental_day
ON users.id = rental_day.id;
実行結果
rental_day
テーブルにid
=6,7 のレコードが存在しないため、実行結果にはid
=6,7 のレコードは含まれません。
LEFT (OUTER) JOIN:左外部結合
FROM {テーブル名}
:左側で指定したテーブルにあるすべての行と、LEFT (OUTER) JOIN {テーブル名}
:右側で指定したテーブルにある一致する行をキー:id
カラムで結合します。
LEFT (OUTER) JOIN {テーブル名}
:右側で指定したテーブルにFROM {テーブル名}
:左側で指定したテーブルと一致する行がない場合、結合後のテーブルはNULL
になります。
※OUTER
はオプショナルです。
SELECT * FROM users
LEFT (OUTER) JOIN rental_day
ON users.id = rental_day.id;
実行結果
users
テーブルにはid
=6,7のレコードが存在しますが、rental_day
テーブルにはid
=6,7のレコードが存在しないため、実行結果のid
=6,7:title
,rental_day
カラムはNULL
となります。
RIGHT (OUTER) JOIN:右外部結合
RIGHT (OUTER) JOIN {テーブル名}
:右側で指定したテーブルにあるすべての行と、FROM {テーブル名}
:左側で指定したテーブルにある一致する行をキー:id
カラムで結合します。
FROM {テーブル名}
:左側で指定したテーブルに一致する行がない場合、結合後のテーブルはNULL
になります。
※OUTER
はオプショナルです。
SELECT * FROM users
RIGHT (OUTER) JOIN rental_day
ON users.id = rental_day.id;
実行結果
rental_day
テーブルにはid
=6,7のレコードが存在しないため、実行結果にid
=6,7のレコードは含まれません。
FULL JOIN:完全外部結合
2つのテーブル間で一致するすべての行をキー:id
カラムで結合します。
どちらかのテーブルにしか存在しない行も、結果に含まれます。
SELECT * FROM users
FULL JOIN rental_day
ON users.id = rental_day.id;
実行結果
users
テーブルにはid
=6,7のレコードが存在しますが、rental_day
テーブルにはid
=6,7のレコードが存在しないため、実行結果のid
=6,7:title
,rental_day
カラムはNULL
となります。
CROSS JOIN:交差結合
2つのテーブル間で可能な全ての組み合わせが生成されます。
SELECT * FROM "users"
CROSS JOIN "rental_day";
実行結果
id
,name
× id
,title
,rental_day
の全ての組み合わせ:合計140通りが実行結果で返ります。
商品のSKU(サイズ × 色)などの組み合わせを生成したい場合に便利ですが、実行結果が大きくなる可能性があるため実行には注意が必要です。
上記の通り、様々な手法でテーブルの結合が可能です。
ここまで読んでいただきありがとうございました!