外部結合

土曜の夜だというのに、仕事をしておりました。さほど重いタスクじゃないんだけど、ろくに要件定義資料もまとまっていない追加開発を水曜の午後に振られて月曜納期はちょっとつらい。

この場合、基本設計書を仕上げてからプログラムしてテスト・・・なんて馬鹿正直にやってたら、コードの誤り見つける度に設計書修正しなきゃならなくて到底間に合わない。先にテストしながらコード組んで最後に設計書に落とし込んだ方が早い。

というわけで、週末デートの予定もあらばこそ、長い夜を1人カタカタSQLを叩いて過ごしていたのだが、ちょっとした学びというか教訓を得たので備忘録として書いておきたい。テーブルの内部結合、外部結合の注意点について。

　

まずは内部結合と外部結合のおさらいから。

下の名簿テーブルと所属科マスタテーブルを結合させて、名前と所属科名を対応させたビューを作りたいとする。

name	dept_id
沖田十三
真田志郎	3
古代進	1
南部康雄	1
島大介	2
森雪	4

　

dept_id	dept_name
1	戦術科
2	航海科
3	技術科

２つのテーブルはdept_idで紐づいているので、これを結合条件にすればいいだけだが、単純に下のようなSQLを書いた場合・・・

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  JOIN
    t_dept.dept_name
  ON
    t_crew.dept_id = t_dept.dept_id;

結果はこうなる。

name	dept_name
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科
島大介	航海科

沖田艦長と森雪がどっか行ってもうた。

これは、さっきのSQLでは２つのテーブルを内部結合（INNER JOIN）させているため。内部結合をした場合、結合条件を満たすレコードだけが取得対象になる。沖田艦長の場合、dept_idがNULLなので、所属科マスタのデータと結合できない。森さんはdept_id = 4だが、所属科マスタの不備でdept_id = 4のレコードが存在しない。よってこちらも除外されてしまう。

イメージはこう。

しかし、このビューだと不便。所属仮名が取得できなかった場合でもとりあえず名前は全員分表示させたい・・・

と、いう時に使うのが外部結合。

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  LEFT OUTER JOIN
    t_dept.dept_name
  ON
    t_crew.dept_id = t_dept.dept_id;

これが左外部結合（LEFT OUTER JOIN）のSQL。結果はこう。

name	dept_name
沖田十三
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科
島大介	航海科
森雪

元テーブル（左）のデータは結合条件への合致いかんに関わらず全て取得される。結合条件に合致しなかった場合、結合先の値はNULLになる。

これが内部結合と外部結合の初歩。まぁ、こんな単純なビューなら問題ないんだけど・・・実際に仕事でビュー設計してると列項目がめちゃくちゃ多かったり、テーブルのリレーションが複雑だったりして、外部結合すべきところを内部結合にしたせいで想定外の取得結果になることは多々ある。

左外部結合の他にも右外部結合とか完全外部結合があるけど、使う機会は少ない。特に右外部結合は記述の順番の問題であって、やってることは左外部と同じだし。

さて、ここからが本題というか、今日遭遇したことなんだけど、

まずさっきの名簿と

name	dept_id
沖田十三
真田志郎	3
古代進	1
南部康雄	1
島大介	2
森雪	4

新たにflagという列項目が追加された所属科マスタがあったとして

dept_id	dept_name	flag
1	戦術科	1
2	航海科	0
3	技術科	1

所属科のフラグが「1」になっている人だけを絞り込んで、会議の参加者リストを作るとする。

この場合、さっき触れたように、沖田艦長や森さんみたいに結合先からflagの値を取得できない人の扱いをどうするかによってSQLの作りが変わる。

まず、あくまでflag = 1の人だけを参加者として絞り込む場合、もちろん内部結合になる。

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  JOIN
    t_dept.dept_name
  ON
        t_crew.dept_id = t_dept.dept_id
    AND t_dept.flag = 1;

結果はこう

name	dept_name
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科

結合条件 t_dept.flag = 1 を追加したことによって、航海科の島はもちろん、結合できない沖田艦長、森さんもリストには乗らない。

そして、この場合のSQLは下のような書き方もできる。

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  JOIN
    t_dept.dept_name
  ON
    t_crew.dept_id = t_dept.dept_id
WHERE
  t_dept.flag = 1;

t_dept.flag = 1 を結合条件ではなく抽出条件と捉えて、WHERE句の下に記載したパターン。これでも取得結果は全く同じ。

name	dept_name
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科

では次に、所属科マスタと結合できなかった場合はひとまず参加者リストに乗せておくことにする場合。これは左外部結合。

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  LEFT OUTER JOIN
    t_dept.dept_name
  ON
        t_crew.dept_id = t_dept.dept_id
    AND t_dept.flag = 1;

(INNER) JOIN をLEFT OUTER JOIN に変更しただけ。結果はこう。

name	dept_name
沖田十三
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科
森雪

島は明らかに参加者リストから外れるが、所属科マスタと結合できなかった２人はリストに乗せられる。

ではこれも、WHERE句を使った書き方にしてみる。

SELECT
    t_crew.name
  , dept.name
FROM
  t_crew
  LEFT OUTER JOIN
    t_dept.dept_name
  ON
    t_crew.dept_id = t_dept.dept_id
WHERE
  t_dept.flag = 1;

これでもさっきのSQLと結果は同じ・・・

・・・ではない！！

WHERE句を使った場合、こうなる。

name	dept_name
真田志郎	技術科
古代進	戦術科
南部康雄	戦術科

LEFT OUTER JOINを使ってるのに、内部結合のSQL結果と同じだにぇ。

t_dept.flag = 1 の条件をWHERE句に移動させたことで、一旦外部結合されたリストから、 t_dept.flag = 1のレコードのみが抽出された状態、と考えるのがいいだろう。

つまり何が言いたいかというと、内部結合の時は t_dept.flag = 1 が結合条件なのか抽出条件なのかなんて深く考える必要はないのだけれど、外部結合の時は注意しなきゃいけないよ、ということ。

これをやらかしまして、自分が書いたコードと１時間くらい睨めっこする羽目になった・・・。構文エラーとかなら、コンパイルした時にGUIの方で問題箇所をある程度指摘してくれるから楽なんだけど、こういうミスは自力で原因突き止めるしかないからな・・・。列項目が多かったり鬼長いコード書いちゃった後だと心が折れそうになる。

まぁ、書いたコードが一発でコンパイル通ることなんてまずないけどな。

その分、うまく行ったときにはあらゆる脳内物質が駆け巡る。

β-エンドドルフィン・・・チロシン・・・エンケファリン・・・バリン、リジン、ロイシン、イソロイシン・・・！