"BOKU"のITな日常

BOKUが勉強したり、考えたことを頭の整理を兼ねてまとめてます。

SQL初心者に説明した「JOIN」のごく基本的なイメージ

f:id:arakan_no_boku:20211127225752p:plain

目次

JOINのイメージがわからないという相談

SQLの勉強を始めた人に「JOINのイメージがわからない」と相談されたのをうけて、ちょこっと説明したら、思ったより喜ばれたので、役に立つかなと思って書いてます。

とはいえ。

内容的に、SQLを使える人なら誰でも知ってるようなごく簡単なことでしかないです。

なので、知ってるよ・・って方はは読む必要はないと思いますけどね。(笑)

 

説明には以下の2つのテーブルとデータを使いました。

急に作ったので、かなりいい加減ですけど、まあ、いいかなと。

f:id:arakan_no_boku:20211128204852p:plain

f:id:arakan_no_boku:20211128204935p:plain

 

JOINで総当たりの表が生成されることを確認するサンプル

最初は、テーブルを単純にカンマ(,)でつなぐだけの、単純なJOINです。

最近はあまり見ない書き方ですけどね。

Whereによる絞り込みをまったくしない連結です。

select * from test_a T1,test_c T2;

こうすると、表はこうなります。

f:id:arakan_no_boku:20211128213129p:plain

単純に「test_a」の各レコードに「test_c」の全レコードが総当たりでくっついて「test_aの行数」×「test_c」の行数になってるのがわかります。

この総当たりの表(この記事では一旦この総当たりの表を「内部表」と書きます)がJOINによって作成されるイメージが「JOIN」を理解するには、とても大切です。

経験的に、JOINが難しいという人のほとんどが、この内部表をイメージせず、元のテーブルだけを眺めて、JOINの結果を理解しようとしてたからです。

 

内部表をイメージできたら条件での絞り込みも理解しやすい

内部表がイメージできたら、ここに条件を加えたときの絞り込みもイメージできます。

例えば、test_aテーブルのinit_id=1に一致するtest_cの「str_c_name」を1レコードだけ取得したいとします。

それを頭にいれて、上記の内部表をみたら「test_aとtest_cでint_idが等しくて、かつ、test_aのint_idが1にしたら、いけそうだな」というのは簡単にわかると思います。

実際のSQLだと、こうですね。

SELECT T1.int_id,T2.str_c_name from test_a T1,test_c T2 where T1.int_id=T2.int_id and T1.int_id=1

結果はこうなります。

f:id:arakan_no_boku:20211130183024p:plain

イメージ通りの結果だと思います。

 

内部結合(INNER JOIN)と外部結合(OUTER)

今度は内部結合(INNER JOIN)と外部結合(OUTER)の話です。

SELECTするときに出力する行を決めるときには2つの条件があります。

ひとつはWhere句で指定する条件(検索条件と以後書きます)です。

これは「指定した条件に一致した行だけを出力」します。

JOINでも、前の段で説明した「カンマでつなぐだけのJOIN」の場合、総当たりで結合した表に対して、単純に「Where」で絞込を行なうことになります。

でも、JOINには別のやり方があります。

from test_c  T1 

 join test_a T2 on T1.str_c_one=T2.str_a_one;

みたいな書き方で、テーブル同士を結合する条件を検索条件とは別に、「On ~」で指定する(結合条件と以後書きます)やり方です。

内部結合と外部結合の違いは。

基準となるテーブルでは検索条件に一致しているのだけれど、JOINの対象となるテーブルで結合条件に一致しないものがある時に、その行を出力対象とする(外部結合)か、しない(内部結合)かの違いです。

ちなみに、MariaDBMySQL)では、上記例のように「join」だけ書くとデフォルト=内部結合になるようです。

 

INNER JOIN(内部結合)

結合条件に指定したフィールドの値が一致するデータだけ取得したいときに使います。

実際の用途の例をあげると「2020年以前に入社した社員でXX会会員に登録している者だけを一覧したい・・」みたいな場合、Where句には「入社年が2020年以前」の条件を指定して「XX会会員マスタ」を社員番号とかで内部結合する・・みたいな感じです。

そうすると「XX会会員マスタ」に社員番号がない社員は「入社年が2020年以前」の条件に一致しても出力されませんから、目的を達することができます。

明確にするために、joinの前に「inner」を追加しといたほうがいいでしょうね。

from test_c  T1 

 INNER join test_a T2 on T1.str_c_one=T2.str_a_one;

(例なので大文字にしてますが、別に小文字でも同じです)

前の例と書き方は違いますけど、この結合条件を決めるときにも、前の段の「内部表」のイメージは重要です。

 

内部結合のイメージサンプル

たとえば。

以下の2つのテーブルをJOINして「str_a_one」と「str_c_one」が等しいレコードだけとりだしたいな・・というような場合があったとします。

f:id:arakan_no_boku:20211128204852p:plain

f:id:arakan_no_boku:20211128204935p:plain

元になっているテーブルだけ見ていると、以下のように結合するときの条件(ONの後ろに書きます)に、T1.str_c_oneとT2.str_a_oneが等しい・・みたいに書けばよさげに勘違いしてしまったりします。

SELECT T1.int_id,T1.str_c_one from test_c T1 INNER join test_a T2 on T1.str_c_one=T2.str_a_one;

ところが、実行すると、こんな感じで3レコードとれて「あれ?」となります。

f:id:arakan_no_boku:20211129011942p:plain

でも、前の項で確認したように、全部が総当たりで内部表が作られて、その中から条件に一致するものを探しているというイメージがあると、この結果は当たり前だとすぐわかります。

だって、こういうことですからね。

f:id:arakan_no_boku:20211130165223p:plain

じゃあ、どうしたらいいかというと、上記の表を見れば、条件に「両方のint_idが等しい」を加えればいいというのはすぐにわかります。

SELECT T1.int_id,T1.str_c_one from test_c T1 INNER join test_a T2 on T1.str_c_one=T2.str_a_one AND T1.int_id=T2.int_id;

これだと、以下のように望む結果がえられます。

f:id:arakan_no_boku:20211129013210p:plain

こういう感じに条件に一致しないレコードを出力しないのがINNER JOINです。

 

OUTER JOIN(外部結合)

結合条件に一致しなくても、基準になる側(leftとrightが指定できます・・まあ、普通leftしか使わないですけど)に存在してる分は出力して、条件不一致でとってこれなかった値は空(null)にしておくのが外部結合(OUTER JOIN)です。

使い方としては「2020年以前に入社した社員を一覧する。役職・組織・資格などの名称は各マスタからとってきて、マスタにない場合は空欄にしておく」とかです。

内部結合にすると、マスタの登録ミスや漏れがあったとき、社員の一覧から消えてしまってマスタ登録のミスに気づけなくなるのですが、それを回避できます。

OUTERとわざわざ書かなくても、「LEFT join」みたいに書くと、OUTERになります。

 

外部結合のイメージサンプル

違いがわかりやすいように、上記の最後のSQLの「INNER join]」のところを、「LEFT join」に書き換えて、結果をみてみます。

わかりやすいように表示項目に「T2.str_a_one」を追加してます。

SELECT T1.int_id,T1.str_c_one,T2.str_a_one from test_c T1 LEFT join test_a T2 on T1.str_c_one=T2.str_a_one AND T1.int_id=T2.int_id;

実行結果は

f:id:arakan_no_boku:20211129014503p:plain

です。

さっきは抽出されていなかった「T1.str_c_one=T2.str_a_one」の条件を満たせない「int_id=3」のレコードが、出力されています。

たた、結合条件には一致していないので「str_a_one」の値が「null」になってます。

LEFT JOINとしたので、左側にあたる「test_c」が基準になって、そちらに存在しているレコードの値のみが表示されているというわけです。

こういう動きがOUTER JOINです。

 

INNER(内部結合)とOUTER(外部結合)の使い分け

こんな2つのテーブルだけのシンプルな例ならイメージしやすいのですけど。

実際にはたくさんのテーブルをJOINしまくる複雑なSQLを書く場合もあります。

そういうので、ひとつのSQLにOUTERとINNERの両方のJOINが混在していて、基準がよくわからなくなってしまい、どう使い分けたらいいのか?わからない・・というのも、相談項目でした。

でも。

これは実にシンプルな話です。

そもそも、どんなに複雑であろうと、SELECT文にはメインの目的があります。

条件を満たした何か(例えば、社員とか製品とか・・)を抽出したい・・みたいな。

だから、そのメインの目的にそった絞り込みに必要なテーブルは「INNER JOIN」でないといけませんし、逆に、メインの目的にそった絞り込みに関係ないテーブル・・例えば、表示名称をもってくるためのマスターテーブルとかは「OUTER JOIN」でないといけないということです。

当たり前ですよね。

例えば、勤続年数が10年以上の社員のリストを作るためのSQLであれば、勤続年数を計算するのに必要なテーブルは「INNER JOIN」で、社員の付帯情報にあたる氏名・所属・役職なんかのマスタテーブルは、「OUTER JOIN」とするべきなのです。

そうしておかないと、仮にマスタテーブルのメンテナンスをだれかがミスったら、勤続年数10年以上の条件をみたしているのに出力されない社員ができてしまうという、本末転倒としか言いようのない事態がおきます。

これは、明らかに「恥ずかしいレベルのバグ」なのですが。

悲しいかな。

自分が「対象者であるべき社員がリストから漏れている!」みたいなユーザからのクレームを受けて調査すると、単にコードの名称を表示するだけにJOINしているテーブルが「INNER」になっていて、それが原因だった・・ということを何回か経験しています。

実は、今回相談をうけて「参考にしたプログラム」を見せてもらったら、中に書かれているSQLが「名前をもってくるだけのテーブルをINNER JOIN」してました(笑)。

なので、これは間違っているから参考にしないほうがいいよ・・で一見落着したのですけど、あれもどっかで動いてるんでしょうかね( ;∀;)。

難儀なことです。

 

まとめ

あらためて見ると、今回は、本当に誰でもしってそうなことしか書いてません(笑)。

でも、まあ一人が喜んでくれたということは、このような内容でも参考になる人が、あと一人・二人くらいはいるかもしれないな・・ということで書いてます。

ということで、最後まで読んで「なんだ、この低レベルな内容は!」なんて思われた方も許してくださいねm(__)m。

ではでは。