テーブル結合は SQL を始めたばかりだとつまづきやすい内容です。
何のためにテーブル結合をするのか？そもそもテーブルがたくさんある理由など、テーブル結合の概念から解説します。
テーブル結合がよくわからないという方は概念的なところから勉強するとわかりやすくなるかもしれません。
SQL が良くわからない方は、以下の記事も合わせてご覧ください。
SQL の面白さを知って、楽しく仕事しましょう。
テーブル結合とは
データベースには、用途ごとに分割された情報が複数のテーブルに格納されています。
そのひとつひとつの情報を関連あるものとしてまとめて取得する方法がテーブル結合です。
ひとつのテーブルだけではまとめられない情報も、複数のテーブルを結合することにより必要な情報として取得することができます。
テーブル分割する理由
そもそも何のためにテーブル分割するのか。という疑問を感じる方もいるかもしれません。
テーブルを分割する理由はいろいろありますが、「用途の異なる情報をまとめて持たない」ことだったり、「同じ情報 (名称) を複数持たない」ことだったりします。
例えば、ある会社の社員情報と所属、技能 (プログラミング言語など) を管理する場合、Excel 等で簡単に作成すると以下のようになります。
Hさんは配属前の新人さんだと思ってください。
一見するとそのままでも使えそうに思えますが、いろいろと問題点が見つかります。
問題点
- 同姓同名の社員が入社した場合に判別ができない
- 所属や技能の名称に変更や、入力ミスがあった場合に気づきにくい
- 社員が存在しない所属や技能が入力できない
- ４つ以上の技能を持つ社員がいた場合、表を横に拡張しないと入力できない
その他にも問題はあるのですが一旦置いておいて、上記の問題に対応したテーブル構成が以下となります。(あくまでも一例です)
解決方法
- 社員基本情報に社員番号を追加することで、同姓同名も管理できるようにした
- 所属情報、技能情報を作成することで、名称を一括管理できるようにした
- 社員が存在しない所属や技能も管理できるようにした
- ４つ以上の技能を持っていた場合でも、横に拡張せずに管理できるようにした
社員数が少なければ Excel 等でも十分管理は可能ですが、社員数が何千人、何万人となると管理が難しくなり、情報も複雑化してくるのでテーブル分割が必要となります。
テーブル結合の本質
テーブル結合する理由は、必要な情報を取得するためです。
上記の分割したテーブルの例だと、社員名の一覧を取得する際にはテーブル結合は不要ですし、各技能を保持している人数だけ知りたければ社員基本情報をテーブル結合する必要はありません。
分割されているテーブルから取得したい項目を考えることを常に意識してください。
テーブル結合方法
テーブルの結合方法は、取得したい内容により「内部結合 (INNER JOIN)」、「外部結合 (OUTER JOIN)」に分かれます。
他にも「交差結合 (CROSS JOIN)」という方法もありますが、ほとんど使わないので覚える必要はありません。
内部結合 ： INNER JOIN
内部結合とは、結合する二つのテーブルの両方に存在するものを取得します。
先ほどのテーブル分割したデータから、「システム部の社員一覧」とか、「技能を持っている社員一覧」などを取得する際に使用します。
前者の場合は、システム部の一覧なので所属ありが前提、後者の場合は、技能を登録していない社員 (経理部や営業部) を取得する必要がないため内部結合で結合します。
外部結合でも取得することは可能ですが、不要な情報を取得してしまう可能性があるため内部結合が推奨されます。
「システム部の社員一覧」は所属番号がわかれば結合しなくても良いのですが、結合の学習なので・・・。
構文
内部結合の構文は以下の通りです。
「システム部の社員一覧」を取得する SQL を作成します。
取得したい結果は以下の通り。
太字部分が SQL の構文で、赤太字部分が内部結合の構文です。
-- 社員基本情報と所属情報を内部結合
SELECT
所属情報.所属名
,社員基本情報.社員名
FROM 社員基本情報
INNER JOIN 所属情報
ON 社員基本情報.所属番号 = 所属情報.所属番号
WHERE 所属情報.所属番号 = '0003'
ORDER BY 社員基本情報.社員番号;説明
① 結合テーブルの設定
まず、社員基本情報と所属情報を結合する部分を考えます。
以下の部分で「社員基本情報と所属情報を内部結合します」と宣言します。
・・・
FROM 社員基本情報
INNER JOIN 所属情報
・・・② 結合条件の設定
その後、社員基本情報と所属情報を結合する条件を指定します。
以下の部分で「社員基本情報と所属情報を所属番号で結合します」となります。
・・・
ON 社員基本情報.所属番号 = 所属情報.所属番号
・・・ポイント
共通項目が複数ある場合は AND でつなぎます。
・・・
ON 社員基本情報.所属番号 = 所属情報.所属番号
AND 社員基本情報.部署番号 = 所属情報.部署番号
AND 社員基本情報.役職番号 = 所属情報.役職番号
・・・③ 取得項目、抽出条件、並び替え
あとは通常の SELECT 文と同じく、取得項目、抽出条件、並び替えを指定します。
-- 社員基本情報と所属情報を結合
SELECT
所属情報.所属名
,社員基本情報.社員名
FROM 社員基本情報
INNER JOIN 所属情報
ON 社員基本情報.所属番号 = 所属情報.所属番号
WHERE 所属情報.所属番号 = '0003'
ORDER BY 社員基本情報.社員番号;注意点
各項目にはテーブル名を明記する必要があります。
例外として、結合したテーブル内にひとつしかない項目の場合は不要ですが、テーブル結合する際は全てに記載するようにしましょう。
ポイント
取得項目やテーブル名は SQL 文内で別名を宣言して使用することができます。
別名を付けた場合の取得結果は以下の通り。(テーブルの別名は結果としては見えません)
SQL 文をきれいに記述したり、同じテーブルを結合したりする際には欠かせないので、別名の付け方は合わせて覚えましょう。
-- 取得項目の別名
SELECT
所属.所属名 AS 所属部署
,社員.社員名 AS 社員名称
-- テーブルの別名
FROM 社員基本情報 AS 社員
INNER JOIN 所属情報 AS 所属
ON 社員.所属番号 = 所属.所属番号
WHERE 所属.所属番号 = '0003'
ORDER BY 社員.社員番号;取得項目、テーブルの別名には AS を使用します。
スペースのみで省略することも可能ですが、見落としや間違いのもとになるので必ずつけた方が良いでしょう。
-- 別名をスペースで定義 (非推奨)
SELECT
所属.所属名 所属部署
,社員.社員名 社員名称
FROM 社員基本情報 社員
INNER JOIN 所属情報 所属
ON 社員.所属番号 = 所属.所属番号
WHERE 所属.所属番号 = '0003'
ORDER BY 社員.社員番号;外部結合 ： LEFT OUTER JOIN、RIGHT OUTER JOIN
外部結合とは、主体となるテーブルにもう一方の情報を付加したものを取得します。
テーブル分割したデータから、「所属名付き社員一覧」、「技能別社員数一覧」などを取得するときに使用します。
前者は所属がない社員も取得、後者は社員が保持していない技能も取得するため外部結合となります。
OUTER は省略可能なので、LEFT JOIN、RIGHT JOIN と覚えましょう。
LEFT、RIGHT の説明は後程。
構文
外部結合の構文は以下の通りです。
「所属名付き社員一覧」を取得する SQL を作成します。
取得したい結果は以下の通り。
どこにも所属していない Hさんも取得したい場合に使用します。
太字部分が SQL の構文で、赤太字部分が外部結合の構文です。
OUTER を付ける場合は、LEFT OUTER JOIN と記載します。
-- 社員基本情報と所属情報を外部結合
SELECT
社員基本情報.社員名
,所属情報.所属名
FROM 社員基本情報
LEFT JOIN 所属情報
ON 社員基本情報.所属番号 = 所属情報.所属番号
ORDER BY 社員基本情報.社員番号;気づいた方はいると思いますが、内部結合の結合方法とほぼ同じです。
INNER が LEFT に替わっただけ。
説明
構文に関する説明は内部結合と同じなので、わからなければ内部結合を見て復習してください。
ここでは、LEFT、RIGHT を説明します。
LEFT JOIN と RIGHT JOIN の違いは、主体とする情報を決めるためで、右左は結合する順番で決まります。
SQL を改行しないで書いた場合に以下となるので、初めのうちは書いてある場所で覚えても良いです。
・・・
FROM 社員基本情報 LEFT JOIN 所属情報
・・・社員基本情報が左で、所属情報が右となります。
左
右
所属情報を主体としたい場合は、RIGHT JOIN となります。
・・・
FROM 社員基本情報 RIGHT JOIN 所属情報
・・・結合結果はそれぞれ以下の通りとなります。
LEFT JOIN の場合は、社員基本情報の全てに所属を追加、RIGHT JOIN の場合は、所属情報の全てに社員を追加となります。
LEFT JOIN
LEFT JOIN では所属を持っていない Hさんも取得対象。
RIGHT JOIN
RIGHT JOIN では、社員が存在しない法務部も取得対象。
複数テーブルの結合
「複数のテーブルを結合するとよくわからなくなる」と良く言われるのですが、二つのテーブル結合ができる人であれば必ず理解できます。
全てまとめて考えず、ひとつひとつ分解して考えましょう。
ここでは、テーブル分割したデータから全社員の保持技能一覧を取得してみます。
取得したい内容は以下の通り。
方法としては、社員基本情報に社員別保持技能を結合して、最後に技能情報から名称を取得すると考えるとわかりやすいです。
テーブル結合には推奨される順番があるのですが、今回はあまり気にせずに行きます。
① 社員基本情報と社員別保持技能を結合
第一段階としては、社員基本情報と社員別保持技能を結合します。
全社員が対象なので外部結合し、所属番号は不要なので取得しません。
-- 社員基本情報と社員別保持技能を結合
SELECT
社員.社員番号
,社員.社員名
,社員技能.技能番号
FROM 社員基本情報 AS 社員
LEFT JOIN 社員別保持技能 AS 社員技能
ON 社員.社員番号 = 社員技能.社員番号
② 結合結果と技能情報を結合
第二段階として、①で結合した結果と技能情報を結合します。
こちらの結合も技能番号を保持していない社員も対象なので外部結合となります。
-- 結合結果と技能情報を結合
SELECT
社員.社員番号
,社員.社員名
,社員技能.技能番号
,技能.技能名
FROM 社員基本情報 AS 社員
LEFT JOIN 社員別保持技能 AS 社員技能
ON 社員.社員番号 = 社員技能.社員番号
LEFT JOIN 技能情報 AS 技能
ON 社員技能.技能番号 = 技能.技能番号③ 取得項目、抽出条件、並び替え
取得項目の追加や抽出条件の変更、取得結果の並び替えが必要であれば最後に変更します。
今回は全社員の保持技能一覧なので、抽出条件なしで、並び替えは社員番号順が見やすそうです。
さらに、社員番号だけの並び替えだと技能が順番に並ばないので、技能番号でも並び替えます。
②の結合結果イメージではすでに並び替えされていますが、並び替えしないと順番がバラバラになります。
-- 結合結果と技能情報を結合
SELECT
社員.社員番号
,社員.社員名
,社員技能.技能番号
,技能.技能名
FROM 社員基本情報 AS 社員
LEFT JOIN 社員別保持技能 AS 社員技能
ON 社員.社員番号 = 社員技能.社員番号
LEFT JOIN 技能情報 AS 技能
ON 社員技能.技能番号 = 技能.技能番号
ORDER BY
社員.社員番号
,技能.技能番号複数テーブルもひとつひとつ考えれば必ず理解できますのでがんばってください。
まとめ
テーブル結合の概念と結合方法でした。
SQL を使い始めたばかりだとテーブル結合はなかなかハードルが高いと思います。
ただ、テーブル結合を覚えると作成できる SQL の幅が大きく広がり、SQL の面白さがわかってきたりします。
以前 SQL の面白さを教えた後輩は、Oracle の資格取得に興味を持って勉強しているようです。
SQL は他のプログラミング言語と違いほぼすべてのシステムで使用しますので、SQL が面白くなると仕事も面白くなるかもしれません。
楽しく覚えて、楽しく仕事しましょう！