SQL – 異なるテーブルの結合｜Chopesu Blog - 文系・未経験でエンジニアに転身した元営業マンのエンジニアライフ

SQLでは複数テーブルに分けて格納されたデータを同時に取り出すことができる。
これを「結合」という。

テーブルを作成する際に、DB設計の段階で正規化等のプロセスを経て、テーブルを複数に分けることがある。それはデータベースの視点で見ると、データが分割されていた方がデータを安全かつ確実かつ早く扱いやすいから。
ちょっと話は反れるけど、EXCELで表を作るときも、1つの表に情報（列）を入れすぎると、画面を横にスクロールしたり、画面の縮尺を小さくしないと表全体が見れなくなったりと、使い勝手がかなり悪くなった記憶が。
そんなとき、確かに表を分けたりして工夫してたなーとか思い出した。SQLではそんな分けた表たちを見やすくするために簡単に結合できるらしい。これは色々なことに使えそうな予感が。。。

Contents

テーブルの結合

テーブルの結合

基本的な使い方

SELECT 日付,名前 AS 費目, メモ  -- ここで名前という列名を使えるのは、JOINで結合した後の表を参照しているから
FROM 家計簿
JOIN 費目  -- 結合する他の表を指定
ON 家計簿.費目ID = 費目.ID  -- 結合条件を指定

SELECT 日付,名前 AS 費目, メモ -- ここで名前という列名を使えるのは、JOINで結合した後の表を参照しているから

FROM 家計簿

JOIN 費目 -- 結合する他の表を指定

ON 家計簿.費目ID = 費目.ID -- 結合条件を指定

以下、結合のイメージ図。あくまでFROM句の主となる家計簿テーブルに、費目テーブルを結合している。

（JOIN）ONの条件を満たす結合相手が見つからない場合

例えば、家計簿テーブルの費目IDが4なのに、リレーションシップを築いている費目テーブルにはID=4という行がない場合。結合後、家計簿テーブルの費目IDが4を含む行は強制削除されてしまう。このような結合を内部結合(INNER JOIN)という。

強制削除されてしまうと、結合データをもとに集計作業をしたりするときは困るので、削除されないようにしたい。。。

左外部結合（LEFT JOIN）

左外部結合を使えば、費目テーブルに費目ID=4があろうが、それがNULLであろうが、家計簿テーブルの行を強制削除することなく、結合することができる。

SELECT 日付,名前 AS 費目, メモ  -- ここで名前という列名を使えるのは、JOINで結合した後の表を参照しているから
FROM 家計簿
LEFT JOIN 費目  -- 結合する他の表を指定
ON 家計簿.費目ID = 費目.ID  -- 結合条件を指定

SELECT 日付,名前 AS 費目, メモ -- ここで名前という列名を使えるのは、JOINで結合した後の表を参照しているから

FROM 家計簿

LEFT JOIN 費目 -- 結合する他の表を指定

ON 家計簿.費目ID = 費目.ID -- 結合条件を指定

これと同じように
■右外部結合（RIGHT JOIN）⇒費目テーブルの全行を必ず出力
■完全外部結合（FULL JOIN）⇒家計簿テーブルも費目テーブルも全行を必ず出力
もある。

結合するテーブルが3つ以上になっても書き方や考え方は同じで、記述したSQL文の上から順番に結合されていく。このように結合条件が合わなくても行を削除することなく結合することを、外部結合(OUTER JOIN)という。

その他の構文

テーブル名を指定

/*結合元と結合相手に同じ列名があるときは「テーブル名.列名」で識別する*/
SELECT 日付, 家計簿.メモ, 費目.メモ
FROM 家計簿
JOIN 費目
ON 家計簿.費目ID = 費目.ID

/*テーブル名が長いときは「AS」を使って別名に変換して記述する*/
SELECT 日付, Y.メモ, M.メモ
FROM 家計簿 AS Y
JOIN 費目 AS M
ON Y.費目ID = M.ID

/*結合元と結合相手に同じ列名があるときは「テーブル名.列名」で識別する*/

SELECT 日付, 家計簿.メモ, 費目.メモ

FROM 家計簿

JOIN 費目

ON 家計簿.費目ID = 費目.ID

/*テーブル名が長いときは「AS」を使って別名に変換して記述する*/

SELECT 日付, Y.メモ, M.メモ

FROM 家計簿 AS Y

JOIN 費目 AS M

ON Y.費目ID = M.ID

副問い合わせの結果と結合

SELECT 日付, 費目.名前, 費目.経費区分ID
FROM 家計簿
JOIN (SELECT* FROM 費目
　　　WHERE 経費区分 = 1
　　　) AS 費目  -- 副問い合わせの結果を結合
ON 家計簿.費目ID = 費目.ID

SELECT 日付, 費目.名前, 費目.経費区分ID

FROM 家計簿

JOIN (SELECT* FROM 費目

　　　WHERE 経費区分 = 1

　　　) AS 費目 -- 副問い合わせの結果を結合

ON 家計簿.費目ID = 費目.ID

外部結合の使い方

繰り返し項目を1列にまとめる

上記のような、DB設計において正規化をしていない困ったテーブルをあるべき姿に変換する

SELECT class, student_1 AS student FROM Students
UNION ALL
SELECT class, student_2 AS student FROM Students
UNION ALL
SELECT class, student_3 AS student FROM Students;

SELECT class, student_1 AS student FROM Students

UNION ALL

SELECT class, student_2 AS student FROM Students

UNION ALL

SELECT class, student_3 AS student FROM Students;

クロス表で入れ子の表側を作る

ここからは、達人に学ぶSQL指南書のサンプルコードを一部お借りして、まとめたいと思います。

SQL文

SELECT MASTER.age_class AS age_class,
　　　　 MASTER.sex_cd AS sex_cd,
　　　　 DATA.pop_kansai AS pop_kansai,
　　　　 DATA.pop_kyusyu AS pop_kyusyu
FROM (SELECT age_class, sex_cd
　　　　FROM TblAge CROSS JOIN TblSex) MASTER -- クロス結合でマスタ同士の直積を作る
　LEFT OUTER JOIN
　　(SELECT age_class, sex_cd,
　　　　　　　　SUM(CASE WHEN pre_name IN('大阪', '兵庫')
　　　　　　　　　　　THEN population ELSE NULL END) AS pop_kansai,
　　　　　　　　SUM(CASE WHEN pre_name IN('福岡', '熊本')
　　　　　　　　　　　THEN population ELSE NULL END) AS pop_kyusyu,
　　　FROM TblPop
　　　GROUP BY age_class, sex_cd) DATA
　　　　　ON MASTER.age_class = DATA.age_class
　　　　 AND MASTER.sex_cd = DATA.sex_id;

SELECT MASTER.age_class AS age_class,

　　　　 MASTER.sex_cd AS sex_cd,

　　　　 DATA.pop_kansai AS pop_kansai,

　　　　 DATA.pop_kyusyu AS pop_kyusyu

FROM (SELECT age_class, sex_cd

　　　　FROM TblAge CROSS JOIN TblSex) MASTER -- クロス結合でマスタ同士の直積を作る

　LEFT OUTER JOIN

　　(SELECT age_class, sex_cd,

　　　　　　　　SUM(CASE WHEN pre_name IN('大阪', '兵庫')

　　　　　　　　　　　THEN population ELSE NULL END) AS pop_kansai,

　　　　　　　　SUM(CASE WHEN pre_name IN('福岡', '熊本')

　　　　　　　　　　　THEN population ELSE NULL END) AS pop_kyusyu,

　　　FROM TblPop

　　　GROUP BY age_class, sex_cd) DATA

　　　　　ON MASTER.age_class = DATA.age_class

　　　　 AND MASTER.sex_cd = DATA.sex_id;

上記のSQL文でも問題なく動くが、途中のインラインビュー（中間テーブル）を削除することで、パフォーマンスを上げることができる。ビューについては、こちらを参照。

SELECT MASTER.age_class AS age_class,
　　　　 MASTER.sex_cd AS sex_cd,
　　　　 SUM(CASE WHEN pre_name IN('大阪', '兵庫')
　　　　 　　　THEN population ELSE NULL END) AS pop_kansai,
　　　　 SUM(CASE WHEN pre_name IN('福岡', '熊本')
　　　　 　　　THEN population ELSE NULL END) AS pop_kyusyu
FROM(SELECT age_class, sex_cd
　　　　　　　FROM TblAge CROSS JOIN TblSex MASTER)
　　　　　　　LEFT OUTER JOIN TblPop DATA
　　　　　　　　　　　　　　　ON MASTER.age_class = DATA.age_class
　　　　　　　　　　　　　　　AND MASTER.age_sex = DATA.age_sex
GROUP BY MASTER.age_class, MASTER.sex_cd;

SELECT MASTER.age_class AS age_class,

　　　　 MASTER.sex_cd AS sex_cd,

　　　　 SUM(CASE WHEN pre_name IN('大阪', '兵庫')

　　　　　　　THEN population ELSE NULL END) AS pop_kansai,

　　　　 SUM(CASE WHEN pre_name IN('福岡', '熊本')

　　　　　　　THEN population ELSE NULL END) AS pop_kyusyu

FROM(SELECT age_class, sex_cd

　　　　　　　FROM TblAge CROSS JOIN TblSex MASTER)

　　　　　　　LEFT OUTER JOIN TblPop DATA

　　　　　　　　　　　　　　　ON MASTER.age_class = DATA.age_class

　　　　　　　　　　　　　　　AND MASTER.age_sex = DATA.age_sex

GROUP BY MASTER.age_class, MASTER.sex_cd;

上記のSQL文は、クロス結合で作成したMASTER(テーブル)とTblPop(DATA)が「一対多」の関係にあるため、これらを結合しても、得られる表の行数は変わらないため、中間ビューは作らないようにしている。なので、先にMASTERの中間ビューを作ってそこから参照する一つ目の記述よりも処理手順が減るので、パフォーマンスが上がる。
結合元と結合先のテーブルが「一対一」あるいは「一対多」の関係になっていれば、上記のような記述が可能になる。