言語(PHP他)SQL
更新日 : 2020年9月1日
投稿日 : 2020年2月27日

JOINの中でJOINする、複雑なSQL-相関サブクエリ-の考え方

JOINの中でJOINする、複雑なSQL-相関サブクエリ-の考え方の画像

こんにちわ、PHPエンジニアのエンジニア婦人(@naho_osada)です。
私はPHPエンジニアとして7年~の経験があります。WordPressは2年半~の経験があります。その他、jQuery、HTML、CSSも使用します。
ここでは主に過去に納品した案件や自サイト運営(エンジニア婦人ノート)で遭遇したことについて書いています。

JOINの中でJOINする」―やると複雑になりがちので、やらないで済むならやらないに越したことはありません。それでも、やるときはやる。

今回、最終的に作りたい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-相関サブクエリ-」を作成するにあたり、考え方の手順を紹介します。

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

使用するテーブルのER図
使用するテーブルのER図

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テーブルは書類の基本情報を格納しています。

documentテーブル
documentテーブル

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

doc_targetテーブル
doc_targetテーブル

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

staffテーブル
staffテーブル

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

departmentテーブル

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

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

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

documentテーブルとdoc_targetテーブルをJOINした結果

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人分)した結果
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人分)した結果
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;
staffテーブルとdepartmentテーブルを連結した結果

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

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分が入りました。この結果はこうなります。

相関サブクエリを使って1人の情報を連結した結果
相関サブクエリを使って1人の情報を連結した結果

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する」ことです。

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