SQLのJOINが苦手という人に向けて

はじめに

この仕事に携わっていると、結構な経験者でもSQL、特にテーブル同士の結合が苦手という人によく会います。
で、話を聞くと苦手な理由はテーブル同士のリレーションを視覚的にイメージ出来ないことにあるのかと。。

かつて自分もSQLは大の苦手でした。
２、３年目の時に某電力の大規模Javaプロジェクトに放り込まれました。
テーブル同士の多重度が多対多、オンラインPGで1SELECTあたり3000〜5000行という変態的なSQL。
MVCモデルが出始めの頃、Webフレームワークなんて言葉が流行り出す前
バックエンドからフロントエンドまで全てをベタ書き。
期日までに徹夜・休出しながら直したり書かないといけなかったものですから、それはもう大変な苦労と挫折を味わいました。

そんな環境下でテーブル同士のリレーションを図に整理しながら、書いたり、手直ししたりする内に、視覚的にイメージ出来るようになり、SQLへの苦手意識はほとんど無くなりました。

「乗り越えた壁はいつしか自分を守る盾になる」と誰かが言ってたアレです（笑）

SQLが苦手な方向けの説明になってます。
ある程度のSQL経験者には退屈な内容になっておりますので、あしからず。

3つのJOIN

INNER JOIN・LEFT (OUTER) JOIN・FULL (OUTER) JOINの3つのJOINについて説明します。

※他にもRIGHT (OUTER) JOIN、CROSS JOIN等があります。
ただし、RIGHT (OUTER) JOINはLEFT (OUTER) JOINをただ左右反転させたもの、CROSS JOINはよっぽど切羽詰まった状況使うか、使う時点で処理フローを変えることを検討した方が良い、どちらも実務で使うことは稀なので割愛します。

INNER JOIN

説明するまでもありませんが、最も一般的でよく使われるJOINですね。
内部結合っていわれるやつです。
仮にAとBというテーブルをINNER JOINした場合の関係性をベン図に表すとこんな感じです。

「テーブルAにも存在し、かつ、テーブルBにも存在するレコードを取ってくる」とゆうJOINです。
真ん中の赤く重なり合ってるところをとります。
テーブルA・Bがこんな状態だったとします。

• テーブルA

PK_A	VAL_A
001	AAA
002	BBB
004	CCC
005	DDD
007	EEE
009	FFF

• テーブルB

PK_B	VAL_B
001	GGG
003	HHH
006	III
008	JJJ
009	KKK

PK_A・PK_Bがそれぞれの主キーです。

AのPK_AとBのPK_BでJOINします。

SELECT
    PK_A,
    VAL_A,
    PK_B,
    VAL_B
FROM
    A
    INNER JOIN B ON
        A.PK_A = B.PK_B

結果は

PK_A	VAL_A	PK_B	VAL_B
001	AAA	001	GGG
009	FFF	009	KKK

ですね。
A・B両方に存在する001と009のみが抽出されます。

LEFT OUTER JOIN

これもよく使われるJOINです。
左外部結合っていわれます。
たまにレガシーなところ行くといまだに(+)なんて記述もありますが、アレは古いOracleの方言です。
可読性・保守性が著しく低下するので間違っても(+)を記述するのはやめておきましょう。
また、OUTERは省略可能ですが、INNER JOINとの違いを明確にさせるために、あえて省略しないで説明します。

ベン図に表すとこんな感じです。

「テーブルAの全てプラス、テーブルAと重なり合うテーブルBのレコードを取ってくる」とゆうJOINです。

先ほどと同じようにAのPK_AとBのPK_BでJOINします。

SELECT
    PK_A,
    VAL_A,
    PK_B,
    VAL_B
FROM
    A
    LEFT OUTER JOIN B ON
        A.PK_A = B.PK_B

結果は

PK_A	VAL_A	PK_B	VAL_B
001	AAA	001	GGG
002	BBB	null	null
004	CCC	null	null
005	DDD	null	null
007	EEE	null	null
009	FFF	009	KKK

Aの全件と、Aのキーと一致するBが取得されていることがわかります。
Aのキーと一致しないBの歯抜けの部分についてはnullが埋め込まれていますね。

FULL OUTER JOIN

完全外部結合と言われます。
実務で使われる頻度は少ないですが、覚えておいて損はないです。

AとBを重なり合う部分も含めて全件取ってきます。

SELECT
    PK_A,
    VAL_A,
    PK_B,
    VAL_B
FROM
    A
    FULL OUTER JOIN B ON
        A.PK_A = B.PK_B

結果は

PK_A	VAL_A	PK_B	VAL_B
001	AAA	001	GGG
002	BBB	null	null
null	null	003	HHH
004	CCC	null	null
005	DDD	null	null
null	null	006	III
007	EEE	null	null
null	null	008	JJJ
009	FFF	009	KKK

A・Bの全件が取得されていることがわかります。
そしてお互いの歯抜けの部分についてはnullが埋め込まれていますね。

こんなんどうでしょう？

ここまでお読み頂き、JOINの視覚的なイメージが着いたでしょうか？

では、こんなのはいかがでしょう？

「テーブルBと重なり合わないテーブルAのレコードを取ってくる」とゆうJOIN、、
もちろん、そんなJOINありません。

ではどうすれば良いか？

SELECT
    PK_A,
    VAL_A,
    PK_B,
    VAL_B
FROM
    A
    LEFT OUTER JOIN B ON
        A.PK_A = B.PK_B
WHERE
    B.PK_B IS NULL

AとBをLEFT OUTER JOINさせてから、
WHERE句でBの主キーPK_BがNULLのレコードを指定しています。

結果は

PK_A	VAL_A	PK_B	VAL_B
002	BBB	null	null
004	CCC	null	null
005	DDD	null	null
007	EEE	null	null

歯抜けレコードを利用するのがポイントです。
前述のLEFT OUTER JOINの結果からBの歯抜けレコードを指定することで
A・B両方にある001・009のレコードが除外され、
Bと重なり合わないAのレコードを取得できます。

これが「歯抜け」という文字をあえて太字にした理由です。

じゃあ、こんなのも

理屈が分かっちゃえば、こんなのも簡単ですね。

「重なり合わない部分の`テーブルA`と`テーブルB`のレコードを取ってくる」 同じように**歯抜け**レコードをうまく使いましょう。

SELECT
    PK_A,
    VAL_A,
    PK_B,
    VAL_B
FROM
    A
    FULL OUTER JOIN B ON
        A.PK_A = B.PK_B
WHERE
    A.PK_A IS NULL
OR  B.PK_B IS NULL

結果は

PK_A	VAL_A	PK_B	VAL_B
002	BBB	null	null
null	null	003	HHH
004	CCC	null	null
005	DDD	null	null
null	null	006	III
007	EEE	null	null
null	null	008	JJJ

前述のFULL OUTER JOINの結果からAまたはBの歯抜けレコードを指定することによって
A・B両方にある001・009のレコードが除外され、
お互い重なり合わないA・Bのレコードを取得できます。

さいごに

最近主流のアーキテクチャでは（バッチ処理を除いて）複雑なクエリを書くのはアンチパターンです。
なので上記のようなクエリを組む事も稀かと思いますが、テーブル同士を比較してアンマッチリストを抽出したい時や、ゴミデータ・不正データを調査したい時などにJOINを使いこなせると色々と便利です。

あと気をつけなくてならないのはSELECT文はF・W・G・H・S・Oの順番に評価されます。
何かというとFROM・WHERE・GROUP BY・HAVING・SELECT・ORDER BYの頭文字です。
JOINはFROM句中で書くため、一番最初に評価されます。
なので大量データを保持したテーブル同士をJOINさせるとパフォーマンスの劣化に繋がったりします。
サブクエリやWITH句を使って比較に必要なデータに絞ってからJOINさせる等のパフォーマンスの考慮は必要です。

ただし、最近はOracle・MySQLのオプティマイザによって実行計画が最適化されるので、トンチキなSQLを書いても大体は動いてくれます。
自分が走り出しの頃は変なSQL書いたら幾度となく先輩にブッ◯されそうになりました（笑）

で、自分が言いたいのはテーブル同士のリレーションを視覚的にイメージできれば、どんなSQLがやって来ても怖くない、
何千行というSQLも結局はテーブルのJOINの組み合わせに過ぎないということです。

最後までお読み頂きありがとうございました。