はじめに
この仕事に携わっていると、結構な経験者でも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の組み合わせに過ぎないということです。
最後までお読み頂きありがとうございました。