まず、確実に理解しなければならないのはLEFT OUTER JOINだけです。
というより、もっとも理解しやすい……というより、説明しやすいものです。
憶えておくと良いものとしては、LEFT OUTER JOIN、FULL OUTER JOINだけです。RIGHTは使わないかなーというのが個人的な意見です。
比較しながら見ていくとわかりやすいので、前回のINNER JOINの例を見つつ、LEFT OUTER JOINとの違いを見てみましょう。
前回の例、機器(materielmaster)と使用履歴(usagehistory)を結合した結果から確認します。
lain=> Select lain-> materielmaster.materielcode, lain-> materielmaster.materieldivcode, lain-> materielmaster.materielmaker, materielmaster.modelno, lain-> usagehistory.employeecode lain-> From materielmaster lain-> Inner Join usagehistory On materielmaster.materielcode=usagehistory.materielcode lain-> Order by materielmaster.materielcode; materielcode | materieldivcode | materielmaker | modelno | employeecode --------------+-----------------+---------------+----------+-------------- 1 | 2 | DoLL | INSPIRON | 1 2 | 2 | DoLL | INSPIRON | 2 3 | 2 | DoLL | INSPIRON | 3 4 | 2 | DoLL | INSPIRON | 4 6 | 1 | DoLL | VOSTRO | 6 7 | 1 | DoLL | VOSTRO | 7 8 | 1 | DoLL | VOSTRO | 8 9 | 1 | DoLL | VOSTRO | 9 10 | 1 | DoLL | VOSTRO | 10 11 | 3 | oIZO | S2100 | 11 12 | 3 | oIZO | S2100 | 12 13 | 3 | oIZO | S2100 | 13 14 | 3 | oIZO | S2100 | 14 15 | 3 | oIZO | S2100 | 15 (14 rows) lain=>
さて、前回も説明しましたが、usagehistoryテーブルに materielcode=5 のデータが無いので、条件が合わずに表示されない事が確認できます。
とはいえ、以下のような結果が欲しい場合もあるわけです。
lain=> SQL QUERY materielcode | materieldivcode | materielmaker | modelno | employeecode --------------+-----------------+---------------+----------+-------------- 1 | 2 | DoLL | INSPIRON | 1 2 | 2 | DoLL | INSPIRON | 2 3 | 2 | DoLL | INSPIRON | 3 4 | 2 | DoLL | INSPIRON | 4 5 | 2 | DoLL | INSPIRON | 6 | 1 | DoLL | VOSTRO | 6 7 | 1 | DoLL | VOSTRO | 7 8 | 1 | DoLL | VOSTRO | 8 9 | 1 | DoLL | VOSTRO | 9 10 | 1 | DoLL | VOSTRO | 10 11 | 3 | oIZO | S2100 | 11 12 | 3 | oIZO | S2100 | 12 13 | 3 | oIZO | S2100 | 13 14 | 3 | oIZO | S2100 | 14 15 | 3 | oIZO | S2100 | 15 (15 rows) lain=>
赤く強調した部分ですね。usagehistoryテーブルに情報が無いものです。
この結果を表示させるものが、今回のLEFT OUTER JOINです。
早速実行してみましょう。
lain=> Select lain-> materielmaster.materielcode, lain-> materielmaster.materieldivcode, lain-> materielmaster.materielmaker, materielmaster.modelno, lain-> usagehistory.employeecode lain-> From materielmaster lain-> Left Outer Join usagehistory On materielmaster.materielcode=usagehistory.materielcode lain-> Order by materielmaster.materielcode; materielcode | materieldivcode | materielmaker | modelno | employeecode --------------+-----------------+---------------+----------+-------------- 1 | 2 | DoLL | INSPIRON | 1 2 | 2 | DoLL | INSPIRON | 2 3 | 2 | DoLL | INSPIRON | 3 4 | 2 | DoLL | INSPIRON | 4 5 | 2 | DoLL | INSPIRON | 6 | 1 | DoLL | VOSTRO | 6 7 | 1 | DoLL | VOSTRO | 7 8 | 1 | DoLL | VOSTRO | 8 9 | 1 | DoLL | VOSTRO | 9 10 | 1 | DoLL | VOSTRO | 10 11 | 3 | oIZO | S2100 | 11 12 | 3 | oIZO | S2100 | 12 13 | 3 | oIZO | S2100 | 13 14 | 3 | oIZO | S2100 | 14 15 | 3 | oIZO | S2100 | 15 (15 rows) lain=>
というわけで、期待通りの結果になったと思います。
それでは、この結果に対して、前回同様にmaterieldivmaster(資材区分)テーブルを結合してみましょう。
ここでは、INNER JOINを使います。
lain=> Select lain-> materielmaster.materielcode, lain-> materielmaster.materieldivcode, lain-> materieldivmaster.materieldivname, lain-> materielmaster.materielmaker, materielmaster.modelno, lain-> usagehistory.employeecode lain-> From materielmaster lain-> Left Outer Join usagehistory On materielmaster.materielcode=usagehistory.materielcode lain-> Inner Join materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode lain-> Order by materielmaster.materielcode; materielcode | materieldivcode | materieldivname | materielmaker | modelno | employeecode --------------+-----------------+----------------------+---------------+----------+-------------- 1 | 2 | ノートパソコン | DoLL | INSPIRON | 1 2 | 2 | ノートパソコン | DoLL | INSPIRON | 2 3 | 2 | ノートパソコン | DoLL | INSPIRON | 3 4 | 2 | ノートパソコン | DoLL | INSPIRON | 4 5 | 2 | ノートパソコン | DoLL | INSPIRON | 6 | 1 | デスクトップパソコン | DoLL | VOSTRO | 6 7 | 1 | デスクトップパソコン | DoLL | VOSTRO | 7 8 | 1 | デスクトップパソコン | DoLL | VOSTRO | 8 9 | 1 | デスクトップパソコン | DoLL | VOSTRO | 9 10 | 1 | デスクトップパソコン | DoLL | VOSTRO | 10 11 | 3 | ディスプレイ | oIZO | S2100 | 11 12 | 3 | ディスプレイ | oIZO | S2100 | 12 13 | 3 | ディスプレイ | oIZO | S2100 | 13 14 | 3 | ディスプレイ | oIZO | S2100 | 14 15 | 3 | ディスプレイ | oIZO | S2100 | 15 (15 rows) lain=>
ここではすべてそろっているので、あまり変わらないと思います。 では、ここに前回の演習と同じように、employeemaster を結合して employeesurname を表示するようにして見ましょう。
まずは、INNER JOINを使って結合してみます。
lain=> Select lain-> materielmaster.materielcode, lain-> materielmaster.materieldivcode, lain-> materieldivmaster.materieldivname, lain-> materielmaster.materielmaker, materielmaster.modelno, lain-> usagehistory.employeecode, lain-> employeemaster.employeesurname,employeemaster.employeename lain-> From materielmaster lain-> Left Outer Join usagehistory On materielmaster.materielcode=usagehistory.materielcode lain-> Inner Join materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode lain-> Inner Join employeemaster On usagehistory.employeecode=employeemaster.employeecode lain-> Order by materielmaster.materielcode; materielcode | materieldivcode | materieldivname | materielmaker | modelno | employeecode | employeesurname | employeename --------------+-----------------+----------------------+---------------+----------+--------------+-----------------+-------------- 1 | 2 | ノートパソコン | DoLL | INSPIRON | 1 | 演習 | 一号 2 | 2 | ノートパソコン | DoLL | INSPIRON | 2 | 演習 | 二号 3 | 2 | ノートパソコン | DoLL | INSPIRON | 3 | 演習 | 三号 4 | 2 | ノートパソコン | DoLL | INSPIRON | 4 | 演習 | 四号 11 | 3 | ディスプレイ | oIZO | S2100 | 11 | 宮地 | 勤 12 | 3 | ディスプレイ | oIZO | S2100 | 12 | 鈴木 | ヒカル 13 | 3 | ディスプレイ | oIZO | S2100 | 13 | 佐藤 | 真一 14 | 3 | ディスプレイ | oIZO | S2100 | 14 | 岡部 | 幸平 15 | 3 | ディスプレイ | oIZO | S2100 | 15 | 河本 | 哲平 (9 rows) lain=>
ここまでの結果に対して、usagehistory.employeecode=employeemaster.employeecodeの条件に一致するもののみに絞られていると思います。
それでは、外部結合に切り替えてみましょう。
lain=> Select lain-> materielmaster.materielcode, lain-> materielmaster.materieldivcode, lain-> materieldivmaster.materieldivname, lain-> materielmaster.materielmaker, materielmaster.modelno, lain-> usagehistory.employeecode, lain-> employeemaster.employeesurname,employeemaster.employeename lain-> From materielmaster lain-> Left Outer Join usagehistory On materielmaster.materielcode=usagehistory.materielcode lain-> Inner Join materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode lain-> Left Outer Join employeemaster On usagehistory.employeecode=employeemaster.employeecode lain-> Order by materielmaster.materielcode; materielcode | materieldivcode | materieldivname | materielmaker | modelno | employeecode | employeesurname | employeename --------------+-----------------+----------------------+---------------+----------+--------------+-----------------+-------------- 1 | 2 | ノートパソコン | DoLL | INSPIRON | 1 | 演習 | 一号 2 | 2 | ノートパソコン | DoLL | INSPIRON | 2 | 演習 | 二号 3 | 2 | ノートパソコン | DoLL | INSPIRON | 3 | 演習 | 三号 4 | 2 | ノートパソコン | DoLL | INSPIRON | 4 | 演習 | 四号 5 | 2 | ノートパソコン | DoLL | INSPIRON | | | 6 | 1 | デスクトップパソコン | DoLL | VOSTRO | 6 | | 7 | 1 | デスクトップパソコン | DoLL | VOSTRO | 7 | | 8 | 1 | デスクトップパソコン | DoLL | VOSTRO | 8 | | 9 | 1 | デスクトップパソコン | DoLL | VOSTRO | 9 | | 10 | 1 | デスクトップパソコン | DoLL | VOSTRO | 10 | | 11 | 3 | ディスプレイ | oIZO | S2100 | 11 | 宮地 | 勤 12 | 3 | ディスプレイ | oIZO | S2100 | 12 | 鈴木 | ヒカル 13 | 3 | ディスプレイ | oIZO | S2100 | 13 | 佐藤 | 真一 14 | 3 | ディスプレイ | oIZO | S2100 | 14 | 岡部 | 幸平 15 | 3 | ディスプレイ | oIZO | S2100 | 15 | 河本 | 哲平 (15 rows) lain=>
今度はemployeemasterにデータの無いものも表示されるようになりました。
このような考え方で一覧表などを作成することが多いと思います。
FULLに入る前に何でLEFTなのかということだけちょっと説明。
Select句のフィールド指定を * にしてLeft Outer Joinで結合してみましょう。おそらく、左側に次のテーブルが連結されると思います。一方、RIGHTの場合は右側に来ると思います。この違いなのですが、レコードが存在しないものをRight Outer Joinすると結果が変わります。
個人的にですが、直感的に理解しにくいので別にいいかなーとか……それに、ほとんどはLEFTで書き換えられます。逆もまた然りですが。
こちらはあっさりと。理由は重いのでめったに使わない……と思われるためです。
どちらか片方にレコードが無くても結合され、表示されます。
それでは、FULL OUTER JOINの結果を見てみましょう。
lain=> Select lain-> employeemaster.employeecode, lain-> employeemaster.employeesurname, lain-> employeemaster.employeename, lain-> usagehistory.materielcode, lain-> usagehistory.employeecode lain-> From employeemaster lain-> Full Outer Join usagehistory On employeemaster.employeecode=usagehistory.employeecode lain-> Order by usagehistory.materielcode; employeecode | employeesurname | employeename | materielcode | employeecode --------------+-----------------+--------------+--------------+-------------- 1 | 演習 | 一号 | 1 | 1 2 | 演習 | 二号 | 2 | 2 3 | 演習 | 三号 | 3 | 3 4 | 演習 | 四号 | 4 | 4 | | | 6 | 6 | | | 7 | 7 | | | 8 | 8 | | | 9 | 9 | | | 10 | 10 11 | 宮地 | 勤 | 11 | 11 12 | 鈴木 | ヒカル | 12 | 12 13 | 佐藤 | 真一 | 13 | 13 14 | 岡部 | 幸平 | 14 | 14 15 | 河本 | 哲平 | 15 | 15 16 | 鈴木 | はるか | | 17 | 花田 | 一徳 | | 18 | 野村 | ヒロ | | 19 | 浜崎 | 惇 | | 20 | 梅本 | 真希 | | 101 | 松田 | 麗奈 | | 102 | 森本 | ヒロ | | 103 | 田端 | 直人 | | 104 | 仲村 | 慶太 | | 105 | 佐藤 | 恵梨香 | | 106 | 奥寺 | 豊 | | 107 | 佐藤 | 美幸 | | 108 | 松岡 | 淳子 | | 109 | 鈴木 | 文世 | | 110 | 紺野 | 竜次 | | 111 | 小木 | 憲一 | | 112 | 小杉 | 一哉 | | 113 | 小松 | まなみ | | 114 | 鈴木 | 慎之介 | | 115 | 杉下 | 賢治 | | 116 | 栗林 | 太朗 | | 117 | 手塚 | 敏和 | | 118 | 磯村 | 光臣 | | 119 | 佐藤 | 里奈 | | 120 | 鈴木 | 惇 | | (39 rows) lain=>
最初の3フィールドはemployeemaster、残りはusagehistoryのフィールドです。
このように、片方に無くても結果に表示されます。
便利なのですが、処理が重く、インデックスの有無やレコード数によっては処理が帰ってこないことなどもあるので要注意。
個人的には、業務データの不整合を見つけるのに一度だけ使っただけかも。
結合の中でも外部結合は処理が重いものです。それゆえによくパフォーマンスチューニングの対象になります。
一応、上に書いたものから順序良く結合するので、上位で限りなく件数を減らしたあとに外部結合するとパフォーマンスがアップしたりします。このあたりについては、おいおい書ければよいなーとか。調べるのであれば、EXPLAINのコマンドと読み方をどうぞ。