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のコマンドと読み方をどうぞ。
| Site Menu |
|---|
| Site Top |
Blog |
| Linux Notes |
| Education |
| ARToolKit総合 |
| ARToolKit |
| for NFT |
| for Unity |
| ARToolKit for Mobile |
| for Android |
| NyARToolKit |
| for Java |
| for Android |
| for C# |
| for Unity |
| FLARToolKit |
| FLARManager |
| About |
