The Sixwish project
公開日：2018/5/31 0:00:00

SQL 教育用資料

問合わせ（結合テーブル）

外部結合(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）を結合した結果から確認します。

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode
   ...> FROM materielmaster
   ...>  INNER JOIN usagehistory On materielmaster.materielcode=usagehistory.materielcode
   ...> ORDER BY materielmaster.materielcode;
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
sqlite>

さて、前回も説明しましたが、usagehistoryテーブルに materielcode=5 のデータが無いので、条件が合わずに表示されない事が確認できます。 とはいえ、以下のような結果が欲しい場合もあるわけです。

sqlite> 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

materielcodeが 5 の部分ですね。usagehistory テーブルに情報が無いものです。 この結果を表示させるものが、今回のLEFT OUTER JOINです。 早速実行してみましょう。

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode
   ...> FROM materielmaster
   ...>  LEFT OUTER JOIN usagehistory On materielmaster.materielcode=usagehistory.materielcode
   ...> ORDER BY materielmaster.materielcode;
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
sqlite>

というわけで、期待通りの結果になったと思います。

それでは、この結果に対して、前回同様にmaterieldivmaster（資材区分）テーブルを結合してみましょう。 ここでは、INNER JOINを使います。

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materieldivmaster.materieldivname,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode
   ...> FROM materielmaster
   ...>  LEFT OUTER JOIN usagehistory On materielmaster.materielcode=usagehistory.materielcode
   ...>  INNER JOIN materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...> ORDER BY materielmaster.materielcode;
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
sqlite>

ここではすべてそろっているので、あまり変わらないと思います。 では、ここに前回の演習と同じように、employeemaster を結合して employeesurname を表示するようにして見ましょう。

まずは、INNER JOINを使って結合してみます。

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materieldivmaster.materieldivname,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode,
   ...>  employeemaster.employeesurname,employeemaster.employeename
   ...> FROM materielmaster
   ...>  LEFT OUTER JOIN usagehistory On materielmaster.materielcode=usagehistory.materielcode
   ...>  INNER JOIN materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...>  INNER JOIN employeemaster On usagehistory.employeecode=employeemaster.employeecode
   ...> ORDER BY materielmaster.materielcode;
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|河本|哲平
sqlite>

ここまでの結果に対して、usagehistory.employeecode=employeemaster.employeecodeの条件に一致するもののみに絞られていると思います。

それでは、外部結合に切り替えてみましょう。

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materieldivmaster.materieldivname,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode,
   ...>  employeemaster.employeesurname,employeemaster.employeename
   ...> FROM materielmaster
   ...>  Left Outer Join usagehistory On materielmaster.materielcode=usagehistory.materielcode
   ...>  INNER JOIN materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...>  Left Outer Join employeemaster On usagehistory.employeecode=employeemaster.employeecode
   ...> ORDER BY materielmaster.materielcode;
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|河本|哲平
sqlite>

今度はemployeemasterにデータの無いものも表示されるようになりました。

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

RIGHT OUTER JOIN と FULL OUTER JOIN について

SQLiteでサポートしていないのでそういうものもあるよという程度の理解でかまいません。 どういうものかを知るには、きちんとしたDBを使わないと実行すらできないのでここまでという事にします。