JOINの中で複数JOINして結合する、サブクエリを使ったSQLの作り方

こんにちわ、PHPエンジニアのエンジニア婦人（@naho_osada）です。
私はPHPエンジニアとして8年～の経験があります。

「JOINの中でJOINする」―やると複雑になりがち。サブクエリを用いた、複雑なSQLを作らなければならないときの作り方手順を言語化しました。

ポイントは「SELECT文の結果を元にさらにJOINする」ことです。

結合数が多くなりがちですが、じっくり取り組めば大丈夫ですよ。

完成予定のSQL

今回、最終的に作りたいSQLはこれです。

SELECT 
	doc.document_id, doc.document_name, 
	stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section, 
	stf2.staff_name as name, stf2.department_name as department, stf2.section_name as section, 
	stf3.staff_name as name, stf3.department_name as department, stf3.section_name as section, 
	stf4.staff_name as name, stf4.department_name as department, stf4.section_name as section, 
	stf5.staff_name as name, stf5.department_name as department, stf5.section_name as section
FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf5 ON stf5.staff_id = trg.author_id_5;

このような、SQLの「JOINの中でJOINする、複雑なSQL」を作成するときの考え方の手順を紹介します。

※既にある複雑なSQLを修正するときの考え方はこちらです→「複雑なSQLを修正するときの考え方」

やりたいこととテーブルの構造

「documentテーブルから書類名称（doeument_name）とその作成者（staff_name）、部署（department_name / section_name）を取得して一覧表示する」

とします。

documentテーブルの「doc_target_id」はdoc_targetテーブルの「doc_target_id」と紐づきます。
これは「書類のIDとその作成者たちを紐づけている」とします。

doc_targetテーブルの「author_1～5」はstaffテーブルの「staff_id」と紐づきます。

その他、staffテーブルの「department」はdepartmentテーブルの「department_id」、「section」はsectionテーブルの「section_id」と紐づきます。

取得したいイメージはこんな感じです。
作成者は複数いるので、横に繋がっていきます。

取得したい表のイメージ

各テーブルのデータ

各テーブルに入っているデータです。

documentテーブルは書類の基本情報を格納しています。

doc_targetテーブルは書類の作成者情報が入っています。author_id_5まであり、1レコードに5人までの作成者を登録できます。

staffテーブルは作成者となる人の情報が入っています。

departmentテーブルは部署情報が入っています。

sectionテーブルは課の情報が入っています。

お約束

今回は、テーブル構造には突っ込んではいけません。
それぞれのシステムに、それぞれの色々な事情があるんですよ。どうにもならないこともあるんです。

1：documentテーブルとdoc_target_tableの情報を取得する

「documentテーブルから書類名称（doeument_name）とその作成者（staff_name）、部署（department_name / section_name）を取得して一覧表示する」

ので、documentテーブルを基準としてdoc_target_tableをつなげてみます。

SELECT * FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id

これを実行すると、以下の結果が取得できます。

2：staffテーブルを追加連結する

1の状態では著者情報（author_id_1など）が数値のままで、名前がわかりません。
1の結果を元に、staffの情報をJOINして、名前を取得します。まずは1人分。

SELECT * FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN staff as stf1 ON stf1.staff_id = trg.author_id_1

documentテーブル、doc_targetテーブル、staffテーブルを連結（1人分）した結果

右にstaffの情報が出てきました。

これを5人分（author_id_1～5）まで連結すると、こうなります。
単純にLEFT JOINを5回です。

SELECT doc.document_id, doc.document_name, stf1.staff_name, stf2.staff_name, stf3.staff_name, stf4.staff_name, stf5.staff_name FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN staff as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN staff as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN staff as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN staff as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN staff as stf5 ON stf5.staff_id = trg.author_id_5;

documentテーブル、doc_targetテーブル、staffテーブルを連結（5人分）した結果

ちょっと結果が横長になってくるので、表示項目を絞りました。

3：staffテーブルと部署テーブルをつなげてみる

ここまでで「書類名称と作成者」の情報は取得できました。しかしまだ「作成者の部署と課」がわかりません。
これを取得するには「各個人に対してさらにdepartmentテーブルとsectionテーブルを連結する」必要があります。

そこで、一旦「staffテーブルとdepartmentテーブルを連結」してみます。

SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id;

部署名がついてきました。ここに課名も追加します。

SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id;

staffテーブル、departmentテーブル、sectionテーブルを連結した結果

INNER JOINで特に問題なく、人と部署、課名が取得できました。

4：全部まとめて取得する

最後に、2と3のSQLを同時に実行して、一つの表として取得します。

このとき、2でJOINして紐づける「staff_id」と「author_id_1～5」の中に、3のSQLを入れます。

3のSQLを実行した結果を元にしつつ、「staff_id」と「author_id_1～5」を紐づけるということです。

まずは1人分を連結してみます。

SELECT 
	doc.document_id, doc.document_name, 
	stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section
FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1;

LEFT JOINの中に3のSQL分が入りました。この結果はこうなります。

5人分連結するには、これと同じLEFT JOINを量産します。
as stf1のところは数字をかえないとエラーになります。

SELECT 
	doc.document_id, doc.document_name, 
	stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section, 
	stf2.staff_name as name, stf2.department_name as department, stf2.section_name as section, 
	stf3.staff_name as name, stf3.department_name as department, stf3.section_name as section, 
	stf4.staff_name as name, stf4.department_name as department, stf4.section_name as section, 
	stf5.staff_name as name, stf5.department_name as department, stf5.section_name as section
FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf5 ON stf5.staff_id = trg.author_id_5;

JOINの中でJOINする、複雑なSQL-相関サブクエリ- の結果 — JOINの中でJOINする、複雑なSQLの結果

まとめ

JOINの中でさらにJOINする、複雑なSQLも順を追って考えていくとシンプルなSQL文がくっついたものだとわかります。

ポイントは「SELECT文の結果を元にさらにJOINする」ことです。

困ったときにはやりたいことを分解して、一つずつ結び付けていくと欲しい物が作りやすくなるかなと思います。

フリーランスの案件探しはエミリーエンジニア※広告

毎日仕事をしながらも次の案件を気にしなければならない…フリーランスの宿命です。

そんな大変な案件探しは【エミリーエンジニア】のエージェントに助けてもらってはいかがでしょうか。

▼ 登録はここから ▼

【エミリーエンジニア】は

月額60～80万円の高額案件中心
15年以上の信頼と実績があるから、プライム案件を受注できる
フルリモート案件が90%以上

です。

専属のキャリアアドバイザリーコンサルタントに色々と相談もできますよ。

エンジニアの転職はキャリアネクスト※広告

今の職場で頑張っても収入アップは見込めない…

エンジニア転職の【キャリアネクスト】で転職で収入アップを目指してみてはいかがでしょう？

▼ 登録はここから ▼

【キャリアネクスト】なら

エンジニアのキャリア支援を始めて10年以上！
大手からベンチャー、スタートアップまで幅広い
理想のキャリアプランから最適と考える企業様だけをご紹介
応募書類の添削や企業との連絡代行などのフォローあり
不安なことは専任のアドバイザーにすぐ相談

など、仕事をしながら転職活動する上で大変なことをかなりお任せできちゃいます！