The Sixwish project

SQL - Education

問合わせ(結合テーブル)

外部結合(LEFT|RIGHT|FULL OUTER JOIN / LEFT|RIGHT|FULL JOIN)

まず、確実に理解しなければならないのはLEFT OUTER JOINだけです。
というより、もっとも理解しやすい……というより、説明しやすいものです。
憶えておくと良いものとしては、LEFT OUTER JOIN、FULL OUTER JOINだけです。RIGHTは使わないかなーというのが個人的な意見です。

外部結合(LEFT OUTER JOIN / LEFT JOIN)

比較しながら見ていくとわかりやすいので、前回の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にデータの無いものも表示されるようになりました。

このような考え方で一覧表などを作成することが多いと思います。

RIGHT OUTER JOINについて

FULLに入る前に何でLEFTなのかということだけちょっと説明。

Select句のフィールド指定を * にしてLeft Outer Joinで結合してみましょう。おそらく、左側に次のテーブルが連結されると思います。一方、RIGHTの場合は右側に来ると思います。この違いなのですが、レコードが存在しないものをRight Outer Joinすると結果が変わります。

個人的にですが、直感的に理解しにくいので別にいいかなーとか……それに、ほとんどはLEFTで書き換えられます。逆もまた然りですが。

FULL OUTER JOIN

こちらはあっさりと。理由は重いのでめったに使わない……と思われるためです。
どちらか片方にレコードが無くても結合され、表示されます。

それでは、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のコマンドと読み方をどうぞ。