【JOINの基本】複数のテーブルからデータを取得しよう

頼れる人

データは１つのテーブルの中に全て入っているわけではありません
選手データは選手テーブル、チームデータはチームテーブルと、それぞれに合ったテーブルにデータが入っています
複数のテーブルからデータを取得するにはどうすればいいのかを教えるよ

複数のテーブルを結合する方法には、内部結合と外部結合といったつなげ方があるんだよ
ここだけ読めばテーブル結合の基本がわかるようになるよ

ぴよちゃん

もうすでにINNER JOINやOUTER JOINを使った複数テーブルからの取得方法は知っているよ！という人は、ここは読まなくていいですよ

こけさん

複数のテーブルからデータを取得しよう（INNER JOIN）

１つのテーブルには選手の情報が入ったテーブル
もう１つのテーブルにはチームの情報が入ったテーブル
があります。

選手の情報と一緒に、この選手はどのチームに所属しているのかもあわせて取得したいとき、これらのテーブルをつなげる必要があります。
それを行うために使用するのが、INNER JOINです。

選手テーブルとチームテーブルとをつなげるカラムがどれかを確認します。
チームテーブルはチームIDとチーム名が登録されています。選手テーブルには選手名のほかにチームIDというカラムがあり、この選手はどのチームに所属しているのかを判別する値が入っています。
そのため、選手テーブルのチームIDと、チームテーブルのチームIDとをつなげることとなります。

// 選手テーブルとチームテーブルとをチームID [teamid] でつなげて取得
SELECT players.name       // 選手名
      ,players.age        // 年齢
      ,teams.name         // チーム名
FROM players
INNER JOIN teams
ON teams.teamid = players.teamid;

// 中島　康太,12,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 熊田　文人,11,FC大友中央

FROM句のあとには元となるテーブル（選手テーブル）を指定し、INNER JOINのあとにはつなげるテーブル（チームテーブル）を指定し、すぐあとにONでどのテーブルのどのカラム同士をつなげるのかを指定します。つなげるカラムが複数あるときには、WHERE句のようにANDをつけて条件を追加します。

選手テーブルとチームテーブルからそれぞれのカラムを取得しますが、カラムを指定するときには、テーブル名とドット（. ）をつけるのを忘れないようにしてください。
テーブル名が長いときには、テーブル名.カラム名の指定のしかただと煩わしいときもありますので、そのときにはこのようにテーブル名に短い別名を付けることができます。
※英数字と一部の記号が利用できますが、英字で始まる必要があります

// 選手テーブルとチームテーブルとをチームID [teamid] でつなげて取得
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM players  pl          // ※別名plを指定
INNER JOIN teams  te      // ※別名tmを指定
ON te.teamid = pl.teamid;

// 中島　康太,12,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 熊田　文人,11,FC大友中央

ここで注意すべき点は、選手テーブルの「海藤一之介」とチームテーブルの「白鷺ミナミFC」というのが取得されないということです。
この選手のレコードのチームIDには何も設定されていません。そしてチームテーブルの「白鷺ミナミFC」のチームIDが設定されている選手テーブルはありません。

片方にしかデータがないときでも取得したい（OUTER JOIN）

全ての選手と所属しているチームがあればそれも取得したいとします。
選手の中にはまだ所属しているチームがない選手もいます。
INNER JOINを使って２つのテーブルをつなげようとすると、「海藤一之介」のレコードにはチームIDが設定されていないため、取得できません。

元となるテーブル（選手テーブル）に対して、同じカラムの値が存在しているときでも、存在しないときでも取得できるようにつなげることができるのがOUTER JOINです。

OUTTER JOINには、LEFT (OUTER) JOINとRIGHT (OUTER) JOIN、そしてFULL (OUTER) JOINの３種類があります。
FULL JOINについては後ほど解説しますので、まずはLEFT JOINとRIGHT JOINについて説明します。

元となるテーブル（選手テーブル）をFROM句に指定し、チームIDがテーブルにあってもなくても取得したいテーブルを、LEFT JOINの次に指定します。
ONでどのテーブルのどのカラム同士をつなげるのかを指定するのかは、INNER JOINと同じです。

// 選手テーブルとチームテーブルとをチームID [teamid] でつなげて取得
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM players  pl
LEFT JOIN teams  te
ON te.teamid = pl.teamid;

// 中島　康太,12,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 海藤　一之介,9,null
// 熊田　文人,11,FC大友中央

このように、元となるテーブルのチームIDが設定されていないチームテーブルの名前はnullとなります。
LEFT JOINで指定するテーブルは、つなげる項目の値が存在しても存在しなくてもよいほうを指定します。

これとは反対に、元となるテーブルをRIGHT JOINで指定して、テーブルをつなげます。
LEFT JOINと逆の指定方法をすることで、同じ結果が得られます。

// 選手テーブルとチームテーブルとをチームID [teamid] でつなげて取得
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM teams  te
RIGHT JOIN players  pl
ON te.teamid = pl.teamid;

// 中島　康太,12,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 海藤　一之介,9,null
// 熊田　文人,11,FC大友中央

実は、わたしはRIGHT JOINは使いません。
理由は元となるテーブルをまずFROM句で指定して、そのテーブルとつなげるテーブルを順番に指定していくことでSQLが作りやすく、第三者にもわかりやすいからです。
そうなると、LEFT JOINを使ってつなげていくこととなります。

両方のテーブルのデータをすべて取得したい（FULL JOIN）

選手テーブルとチームテーブルの両方のデータを取得できるようにつなげることができるのが、FULL JOINです。

選手テーブルをFROM句に指定し、チームテーブルをFULL JOINの次に取得します。
ONでどのテーブルのどのカラム同士をつなげるのかを指定するのかは、INNER JOINと同じです。
どちらを元となるテーブルにしても結果は同じです。

// 選手テーブルとチームテーブルとをチームID [teamid] でつなげて取得
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM players  pl
FULL JOIN teams  te
ON te.teamid = pl.teamid;

// 中島　康太,12,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 海藤　一之介,9,null
// 熊田　文人,11,FC大友中央
// null,null,白鷺ミナミFC

両方のテーブルにチームIDがつながっているレコードは、それぞれのレコードに入っているデータが取得できます。
チームIDでつながっていないレコードも取得できますが、つながっていないデータのカラムはともにnullとなります。

FULL JOINはあまり使うことはありません。INNER JOINやLEFT JOINを使うことがほとんどです。

両方のテーブルのデータのすべての組み合わせを取得しよう（CROSS JOIN）

選手テーブルとチームテーブルの中のデータのすべての組み合わせを取得するためには、CROSS JOINを使います。

選手テーブルをFROM句に指定し、チームテーブルをCROSS JOINの次に取得します。
どちらを元となるテーブルにしても結果は同じです。

１つのテーブルにA, Bのレコード、もう１つのテーブルにX, Y, Zのレコードがあったときには
AとX、AとY、AとZ
BとX、BとY、BとZ
このように、１つのテーブルのデータｎコ × もう１つのテーブルのデータｍコ分のデータを取得することができます。

// 選手テーブルとチームテーブルとを取得
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM players  pl
CROSS JOIN teams  te;

// 中島　康太,12,横浜キッカーズ
// 中島　康太,12,さつきFC
// 中島　康太,12,FC大友中央
// 中島　康太,12,白鷺ミナミFC
// 山本　茂樹,10,横浜キッカーズ
// 山本　茂樹,10,さつきFC
// 山本　茂樹,10,FC大友中央
// 山本　茂樹,10,白鷺ミナミFC
// 海藤　一之介,9,横浜キッカーズ
// 海藤　一之介,9,さつきFC
// 海藤　一之介,9,FC大友中央
// 海藤　一之介,9,白鷺ミナミFC
// 熊田　文人,11,横浜キッカーズ
// 熊田　文人,11,さつきFC
// 熊田　文人,11,FC大友中央
// 熊田　文人,11,白鷺ミナミFC

CROSS JOINも、FULL JOINと同じようにそうそう使うことはありません。
２つのマスタテーブルをCROSS JOINでつなげてデータを取得して、全てのパターンのテストデータを作ったりするときに使います。

// 選手テーブルとチームテーブルとの交差結合データで作成
INSERT INTO XXXXX
SELECT pl.name            // 選手名
      ,pl.age             // 年齢
      ,te.name            // チーム名
FROM players  pl
CROSS JOIN teams  te;

まとめ

複数のテーブルをつなげて、データを取得するには、INNER JOINもしくは、LEFT JOIN・RIGHT JOINを使います。

両方のテーブルにキーとなる項目が存在するレコードを取得したいときには、INNER JOINでテーブルをつなげます。
片方のテーブルにはキーとなる項目が存在しなくてもレコードを取得したいときには、LEFT JOINもしくはRIGHT JOINでテーブルをつなげます。

３つ以上のテーブルをつなげるときにも、同じようにINNER JOINもしくは、LEFT JOIN・RIGHT JOINでつなげていきます。
LEFT JOINとRIGHT JOINはどちらを使っても構いませんが、混在して使わないようにしましょう。

▶　一覧に戻る