(SQL) テーブル結合をちゃんと理解する【inner、outer、full、cross join の違い】

本記事では、
(SQL) テーブル結合をちゃんと理解する【inner、outer、full、cross join の違い】
と言うテーマで記事を書いていこうと思う。

具体的には、筆者の過去の経験を踏まえ、SQLのテーブル結合について、基本的な使い方や違い（inner、outer、full、cross）はもちろん、それらをちゃんと理解し、適切に使い分けれるようになるための知見をまとめていこうと思う。

※パフォーマンスのチューニングとか、上級なことは書きません。

Contents

1 JOINの種類・違いをさらっとおさらい
2 全てはCROSS JOINから考えると腑に落ちる
3 複数テーブルの結合は、書いた順番通りに一個一個行われる
4 SQLの結合で覚えておきたいポイントまとめ
5 SQL参考記事

JOINの種類・違いをさらっとおさらい

まずは簡単に、inner 、outer、right、full、cross joinの使い方、それぞれの違いを説明する。

なお説明に当たり、言葉の定義をちゃんとしておくと、

「結合先」→ 左テーブル（FROM テーブル名に相当）
「結合元」→ 右テーブル（JOIN テーブル名に相当）

と言うことになるので、それらを踏まえて読み進めてほしい。

INNER JOINの使い方

INNER JOINは、結合先と結合元のテーブルのうち、結合条件を満たすレコードのみ抽出するJOINのこと。

結合条件を満たさなかった両テーブルのレコードは削除されてしまう。

実務では最も使用頻度が高いJOINの仕方と言って良いだろう。

LEFT OUTER JOIN（RIGHT OUTER JOIN）の使い方

LEFT OUTER JOINは、結合先と結合元のテーブルのうち、結合条件を満たすレコード＋結合条件を満たさなかった結合先（左テーブル）の全てのレコードを抽出するJOINのこと。

結合条件を満たさなかった結合元（右テーブル）のレコードは削除されてしまうが、結合先（左テーブル）は何がなんでも全レコード表示しようとするJOINの仕方。そのため、結合条件を満たさなかった結合元（右テーブル）のレコードは全てNULLで埋め合わせされて表示される。

これも実務で最も使用頻度が高いJOINの一つだろう。

RIGHT OUTER JOINは、LEFT OUTER JOINの反対で、結合条件を満たすレコード＋結合条件を満たさなかった結合元（右テーブル）の全てのレコードを抽出するJOINのこと。

覚え方としては、LEFTの逆なだけなので、覚えやすいが、残念ながら最も使用頻度の低いJOINと言っても良いのではないだろうか。

FULL OUTER JOINの使い方

FULL OUTER JOINは、LEFT＋RIGHTのこと、つまり結合条件を満たすレコード＋結合条件を満たさなかった結合先と結合元の全てのレコードを抽出するJOINのこと。

左右どちらかのレコード削除されると言うことがないJOIIN。

DB製品によっては使えないこともあるが、使えると何気に便利なFULL JOIN。でも使用頻度は高くないと思う。

CROSS JOINの使い方

CROSS JOINは、結合先と、結合元のレコードの全ての組み合わせを抽出するJOINのこと。

ここまで説明してきたJOINの概念とはちょっと毛色の違うJOINの仕方だ。

要は「左テーブルの全レコード」×「右レコードの全レコード」と言うことを実行するのがCROSS JOINなのだが、、初見では結構意味不明、と言うかJOINって言う感じのイメージとかけ離れていると思う。そして、実務で使うことはまず無いJOINだろう。

しかし、先に言っておこう。

JOINを理解することにおいて、CROSS JOINを理解することこそが最も重要なキーポイントであると。それほどに重要なJOINの概念を持つのがCROSS JOIN なのだと思ってもらいたい。

crossを除いて、ここまではなんとなく理解しているよって方が多いのではないだろうか。
ではここからさらに掘り下げて結合のポイント・コツについて説明していく

全てはCROSS JOINから考えると腑に落ちる

JOINを理解することにおいて、CROSS JOINを理解することこそが最も重要なキーポイントであると述べた。ここからは、inner、outer、full、cross joinそれぞれの違いがざっくり理解できたところで、更なる理解のため、「全てはCROSS JOINから考える」と言う視点で理解を深めていってもらいたいと思う。

まずはいきなりだが、参考記事を紹介する。

CROSS JOINの参考記事

筆者が伝えたい内容はほぼ上記の記事に詰まっているので、詳しいことは参考記事を熟読していただきたいが、、まとめると要はCROSS JOINとは以下のようなものである。

CROSS JOINとは

結合先と、結合元のレコードの全ての組み合わせ（直積）を抽出するJOIN
INNER、OUTER全てのJOINは、CROSS JOINから条件に合うレコードだけを残したもの

「結合先と、結合元のレコードの全ての組み合わせ（直積）を抽出するJOINであること」は周知の通りだろう。それよりも大事なのは、「INNER、OUTER全てのJOINは、CROSS JOINから条件に合うレコードだけを残したもの」と言う点だ。この理解を常に頭の中に残しておくことで、SQLのJOINの理解はできたも同然と言ってよい。

ではf上記が理解できるよう、さらに踏み込んで、CROSS JOINで考えることのメリットを説明していく。

「レコードが増える」概念が理解できる

SQLの初学者であった当時、「レコードが増える」と言う概念がなかなか理解できなかった。

joinの基本的な使い方を覚えると、「左と右をくっつける」と言う意識はできても、その際に、「レコードが増える」現象が起きることは初心者の頃は難しい概念だった。

どういうことかと言うことかというと、下記のようなこと


usersテーブル
+------+--------+
| id   | name   |
+------+--------+
|    1 | taro   |
|    2 | jiro   |
|    3 | hanako |
+------+--------+

postsテーブル
+------+-------+---------+
| id   | body  | user_id |
+------+-------+---------+
|    1 | Hello |       3 |
|    2 | Hi    |       1 |
|    3 | Good  |       2 |
|    4 | Why?  |       2 |
+------+-------+---------+



SELECT
　　　　*
FROM
　　　　users -- 結合先の左テーブル
INNER JOIN
　　　　posts --　結合元の右テーブル
　　　　ON users.id = posts.user_id -- 左テーブルのidと右テーブルのidが一致するもの


+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
|    2 | jiro   |    4 | Why?  |       2 |　　　★存在しないはずの左レコードが増えた
------+--------+------+-------+---------+

（参考：https://qiita.com/ngron/items/db4947fb0551f21321c0）

見ての通り、結合条件を満たす右テーブルのレコード分、左テーブルのレコードが複製されて（増えて）表示されると言うSQLの当たり前の挙動が起きている。

これは「増える」と考えると難しいのだが、実はCROSS JOINから考えると非常に簡単に理解できる。

CROSSから考える

どうゆうことかというと、実際これは増えているのではなくて、CROSS JOINで抽出した全ての結果から、結合条件に合う条件を絞った結果、左テーブルのレコードが増えて見えると言うだけの話なのである。

SELECT
　　　　*
FROM
　　　　users -- 結合先の左テーブル
INNER JOIN
　　　　posts --　結合元の右テーブル
　　　　ON users.id = posts.user_id -- 左テーブルのidと右テーブルのidが一致するもの



まずはCROSS JOINで考える
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    1 | taro   |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |　　★条件を満たす
|    1 | taro   |    3 | Good  |       2 |
|    1 | taro   |    4 | Why?  |       2 |
|    2 | jiro   |    1 | Hello |       3 |
|    2 | jiro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |　★条件を満たす
|    2 | jiro   |    4 | Why?  |       2 |　★条件を満たす
|    3 | hanako |    1 | Hello |       3 |　★条件を満たす
|    3 | hanako |    2 | Hi    |       1 |
|    3 | hanako |    3 | Good  |       2 |
|    3 | hanako |    4 | Why?  |       2 |
+------+--------+------+-------+---------+


↓↓↓　そこから結合条件（ON users.id = posts.user_id ）に合うレコードを抽出


+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |　★
|    1 | taro   |    2 | Hi    |       1 |　★
|    2 | jiro   |    3 | Good  |       2 |　★
|    2 | jiro   |    4 | Why?  |       2 |　★存在しないはずの左レコードが増えた（ように見える）
+------+--------+------+-------+---------+

結合をする前は左テーブルは１レコードだったものが、CROSS JOINをした途端、直積により全ての組み合わせの分レコード分が作成される。ここまでがどんなJOINだろうと共通で、そこからINNER、LEFT、RIGHT、FULLなど「残すものに合わせてレコードを削っていく」のである。

そう考えると、よく言う「レコードが増える」現象は、実は「CROSS JOINから条件に合う残りもの」であると考えることができ、より「レコードが増える」といった魔法感は払拭できるのではないかと筆者は考える（実際筆者はそうだった）。

慣れてしまえばなんてことはない当たり前のSQLの挙動なのだが、初学者の頃の自分にとっては、「inner（減らす）なのにレコードが増えることもあるんか〜い、JOINってただ条件にあうレコードをがっちゃんこするんじゃないんか〜いもう訳分から〜ん」ってな感じになったのを覚えている。ぜひ、レコードが増えているのではなくて、CROSS JOINからの残り物が表示されているだけなんだよと言う考えを常に持ってほしい。

on句は結局、CROSSの１レコード毎に「trueかfalseか」しか判断してない

筆者のように、上記「レコードが増える」と言う現象がしっくりと理解できない人に対して、
結合を理解するポイントとして、on句は、結局のところCROSSの結果できたテーブルを１レコード毎に見ていき、記載した結合条件が「trueかfalseか」ってことしか判断していないと言うことを理解してもらいたい。

ここにタイトル

on句は結局、CROSSの１レコード毎に「trueかfalseか」しか判断してない

なので、必ずしも、

ON users.id = posts.user_id

みたいな書き方をしなくとも、
まずはCROSS JOINの抽出結果が頭に浮かんでいれば、

on 右.id = 3

や

on (右.id = null or 右.id = 0)

のように、一方のテーブルだけに焦点を当てて結合するといったことも可能だし、
また、

on 左.id in ( id, id, id, id )
and　 左.type = 右.type

みたいに、
まず左テーブルのレコードを絞って、その中で右テーブルと条件が一致する部分を探すみたいな考え方も可能だ。

これらは、crossの状態が頭に浮かんでいるからこそ理解できる考え方なのである。

けど、CROSS JOINの概念がない状態だと、いきなり「on 右.id = 3」とか言われても、「あれ？on 左.type = 右.typeみたいにガッちゃんこするんじゃないの？」と言う風に、本質が分かっていない故の混乱の元になってしまうのが問題なのである。

複数のテーブルを結合する場合も考え方は一緒

SQL初心者にありがちなのが、
「一個までのテーブルの結合は理解できるけど、複数テーブルの結合となると途端にハードルが上がる」と言うことが挙げられる。

複数のテーブルを結合する場合も考え方は一緒で、結合する際に、複数テーブルのカラムを条件に指定しても、要は最終的にon句全体でtrueかfalseだけである点を考えれば良い。

以下複数テーブルを結合する例（※だいぶ簡潔に記載しています）

from A
left join B
    a=b
left join C
    a=c
    b=c

C テーブルを結合する際、結合条件にAテーブルもBテーブルも使用しているが、ここまで読み進めていただいたならもう何が言いたいか理解できると思う。

複数のテーブルを結合するために、複数のテーブルのカラムを結合条件に使用することになったとしても、結局のところ、CROSSの結果（上記の場合A-B-Cの順番でCROSS）から、１レコード毎に見ていって、on句に書いてある条件を満たすかどうかに過ぎないのである。

CROSS JOINまとめ

CROSS JOINの要点をまとめると、上記のように、cross joinの結果を１レコードずつ見ていって、on句に書いた結果がtrueのレコードが残る、と言うのがjoinの肝となる。

要は何が言いたいかと言うと、「joinは、右と左で一致するところをガッちゃんこすることだ」という覚え方をしてはいけないと言うことだ。

良くjoin使い方の参考サイトである説明で、「左.type = 右.type」で結合しますよ。な説明だけだと、「右と左で一致するところをガッちゃんこするのがjoinの使い方なんだ！」と言う風にしか理解できず、JOINのON句の本質である「on句が最終的にtrueかfalseかしか見てない」と言う概念は理解できないなあ、使い方や視野を狭めるなあと思ったのが経緯である。

cross自体はおそらく実務では使用する機会はまずないだろう

しかし、JOINを理解する上で最も重要な点として、全てはcrossから考えて、そこからinner 、left、right、full、それぞれの形で条件にあうレコードだけ残したのがjoinなんだよと言うことが本記事では伝えたい。

慣れてくれば頭の中でinner 、left、right、full、の挙動を意識しながらジャカジャカ結合できるのだが、、初心者にはなれるまでは、結構難しいので、、ぜひ最初は全てCROSS JOINから考えるくせをつけることをおすすめする。

補足：CROSS JOINからの削り方

JOINを理解する上で最も重要な点として、全てはcrossから考えて、そこからinner 、left、right、full、それぞれの形で条件にあうレコードだけ残したのがjoinなんだよと上述したがその時の残し方（削り方）について、補足説明をしておく。

というのも以下のような質問を頂いたからだ。

crossの結果を絞り込んだものがinnerというのは理解できたのですが、その他のleft, right, fullがよくわかりませんでした。結合の条件に合致しない場合、外部結合ではNULLになると思うのですがCROSS JOINの結果にはそれらが含まれないような気がしてしまいまして。。

結論（回答）から言うと、left, right, fullの場合は、以下のように考えると分かりやすい

CROSS JOINの結果に条件を満たすレコードが無かったとき（on句の条件を満たさなかったとき）は、CROSSする前の最初の１レコードのみ、全てNULLのレコードをくっつけて抽出する。

文面だと分かりづらいと思うので、例を踏まえて解説する。
以下のようなテーブルAとテーブルBを、LEFT JOINで結合をする場合を考える。


Aテーブル
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

Bテーブル
+------+
| id   |
+------+
|    1 |
|    2 |
|    4 |
+------+



SELECT
　　　　*
FROM
　　　　A
LEFT JOIN
　　　　B
　　　　ON A.id = B.id

結合結果は以下のようになる


+------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |　　　 NULL|
+------+------+

LEFT JOINなので当然左テーブルの123のレコードは全部表示された。その際、左3のレコードは右にないので右がNULLで表示された。一方、右4のレコードは左にないので結果に表示されなかった。LEFT JOINの挙動として、まあ簡単な例だろう。

では次に、これをCROSS JOINからどう削って表示したのかを解説する。
解説結果は以下のようである。

まずはCROSS JOINで考える

A-B
1-1 ○　条件を満たす！
1-2 ×
1-4 ×

2-1 ×
2-2 ○　条件を満たす！
2-4 ×

3-1 ×　条件を満たすレコードがないなあ、、　
3-2 ×　でもLEFT JOINだから左テーブルを表示しないわけにもいかないしなあ、、　
3-4 ×　しょうがないから右はNULLで返すかあ　→（3-NULL）★

抽出結果 +------+------+
| id   | id   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |　　　 NULL|
+------+------+

CROSS JOINの結果にコメントで考え方を記載した。

ポイントは最初に説明した通り、
「CROSS JOINの結果に条件を満たすレコードが無かったとき（on句の条件を満たさなかったとき）は、CROSSする前の最初の１レコードのみ、全てNULLのレコードをくっつけて抽出する。」
と言うことだ。

上記で言うと、左が3の場合右に3が無い。つまり条件を満たすレコードがないので、しょうがないので、CROSSする前の最初の１レコードのみ右はNULLのレコードにして返しているのが分かる。

CROSSから考えてLEFT JOIN（左は全部表示する）と考えると、「CROSSの結果の左を全部表示する」と考えてしまうかもしれないが、そうではなく、「CROSSに条件を満たす左が無かった場合、CROSSする前の最初の１レコードのみ右がNULLのレコードをくっつけて抽出されるんだよ」と言う点を押さえてもらいたい。

上記はrightでもfullでも同じことが言えるので解説は以上とする。

複数テーブルの結合は、書いた順番通りに一個一個行われる

複数テーブルの結合は、書いた順番通りに一個一個行われる

当たり前と言えば当たり前だけど、、と言うのも、筆者が実務に入った時に面食らったのが、以下のような感じのSQL（だいぶ簡略化して書いてあります）。

from A
inner join B
　　　　on a=b
left join C
  on b=c
inner join D
  on b=d
  on c=d
left join (
  from E
  inner join F
    on e=f
  left join (
    from G
      inner join H
      on g=h
    ) GH
) EFGH
  on d=efgh

上記で何を思ったかと言うと、「げ〜こんなに結合するんかい多すぎてもう訳分からん」って感じで面食らった記憶があるのである（実際は上記SQLよりはるかにカオスなSQLがほとんど）。

研修とか参考書では、こんなに複数の結合をすることはなかったので、

その際に何が面食らったかと言うと、以下のような点である。

結合するテーブルと、結合方法（inner とleftが）が多い。
テーブルが複雑に入り混じっているせいで面食らった。

これに対しては、以下のように考えるだけでスッキリする

複数テーブルの結合は、書いた順番通りに一個一個行われれる

上記で言うと、

まずはサブクエリで最も深い階層GとHの結合から実行する（GHテーブル）。
次に同階層となるEとFとGHのテーブルを結合する（EFGHテーブル）
最後に一番上の階層のAとBとCとDとEFGHのテーブルを結合する、

といった具合である。

inner とleftが入り混じっていても同じで、結局は順番通りに結合していくだけだ。
例えばAとBとCの結合部分に注目すると、

まずはAとBをinner joinする→１個のテーブルができる（ABテーブルと名付ける）。
次に、ABとCをleft joinする→１個のテーブルができる、、

と言うように、joinする時は書いた順番に都度テーブルを結合、作成を繰り返していく。

まあ何が言いたいかと言うと、
結合は、複数テーブルの結合であっても書いた順番通りに一個一個行われれると言うことにすぎないので、落ち着いて考えれば、何も問題ないのと言うことである。

（なんか分からんが、筆者は初学者のころ、上記の当たり前の内容が分からなかった、、なので実務に入っていきなり面食らうなんてことがないように、、と言う思いで書いている）。

SQLの結合で覚えておきたいポイントまとめ

SQLの結合で覚えておきたいポイントをまとめると以下のようになる

SQLの結合で覚えておきたいポイント

結合方法は大別すると、inner 、left、right、full、crossがある
レコードが増える（ように見えるだけ、CROSSから削った結果が残っているだけ）
on句は、「trueかfalseか」ってだけ
複数テーブルの結合は、書いた順番通りに一個一個行われる

間違った点や、こんな点もポイントだよって言うのがあればぜひご教示願います。

SQL参考記事

合わせて読みたい

2021.12.29

関連記事(MySQL) 文字コードと照合順序を理解する

2022.01.30

4件のコメント

はじめまして、結合について良い記事をありがとうございます。
CROSS JOINをベースに考えていく方法は大変勉強になりました。

一点記事の下記について質問させてください。
「JOINを理解する上で最も重要な点として、全てはcrossから考えて、そこからinner 、left、right、full、それぞれの形で条件にあうレコードだけ残したのがjoinなんだよ」

もしよければ教えていただけますと幸いです。
どうぞよろしくお願いします。

返信

本記事を参考にしていただきありがとうございます。嬉しいです。
質問について回答をさせていただきますと、、
結論から言うと、left,right,fullの場合、結合の条件に合致しない場合、
「CROSSする前の最初の１レコードにのみ、全てNULLのレコードをくっつけて表示する」
と言うのが回答になります。。

と言っても？？？だと思いますので、補足次項として、記事中に解説を掲載させていただきました。
よろしければお読みいただけると幸いです。