The Sixwish project

SQL - Education

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

内部結合(INNER JOIN / JOIN)

クロス結合(CROSS JOIN)のWhereで条件指定した場合のSQLを、書き直したもの……と考えると理解しやすいかもしれません。
というわけで、前回の一番最後のSQLを INNER JOINを使って書き直してみます。
まず、前回のSQL、それから書き直した場合のSQLと続けて実行してみます。

lain=> Select materielcode,materielmaster.materieldivcode,materieldivname,
lain->  materielmaker,modelno,productno,serialno
lain-> From materielmaster,materieldivmaster
lain-> Where materielmaster.materieldivcode=materieldivmaster.materieldivcode
lain-> Order by materielcode;
 materielcode | materieldivcode |   materieldivname    | materielmaker | modelno  | productno | serialno
--------------+-----------------+----------------------+---------------+----------+-----------+----------
            1 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0001     | 000001
            2 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0002     | 000012
            3 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0003     | 000031
            4 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0004     | 000400
            5 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0005     | 000512
            6 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD001     | 001261
            7 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD002     | 024714
            8 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD003     | 004614
            9 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD004     | 000335
           10 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD005     | 004812
           11 |               3 | ディスプレイ         | oIZO          | S2100    | E0001     | 021341
           12 |               3 | ディスプレイ         | oIZO          | S2100    | E0002     | 002321
           13 |               3 | ディスプレイ         | oIZO          | S2100    | E0003     | 002324
           14 |               3 | ディスプレイ         | oIZO          | S2100    | E0004     | 002325
           15 |               3 | ディスプレイ         | oIZO          | S2100    | E0005     | 002365
(15 rows)

lain=> Select materielcode,materielmaster.materieldivcode,materieldivname,
lain->  materielmaker,modelno,productno,serialno
lain-> From materielmaster
lain->  Inner Join materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode
lain-> Order by materielcode;
 materielcode | materieldivcode |   materieldivname    | materielmaker | modelno  | productno | serialno
--------------+-----------------+----------------------+---------------+----------+-----------+----------
            1 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0001     | 000001
            2 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0002     | 000012
            3 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0003     | 000031
            4 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0004     | 000400
            5 |               2 | ノートパソコン       | DoLL          | INSPIRON | D0005     | 000512
            6 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD001     | 001261
            7 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD002     | 024714
            8 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD003     | 004614
            9 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD004     | 000335
           10 |               1 | デスクトップパソコン | DoLL          | VOSTRO   | DD005     | 004812
           11 |               3 | ディスプレイ         | oIZO          | S2100    | E0001     | 021341
           12 |               3 | ディスプレイ         | oIZO          | S2100    | E0002     | 002321
           13 |               3 | ディスプレイ         | oIZO          | S2100    | E0003     | 002324
           14 |               3 | ディスプレイ         | oIZO          | S2100    | E0004     | 002325
           15 |               3 | ディスプレイ         | oIZO          | S2100    | E0005     | 002365
(15 rows)

lain=> 

まったく同じ結果が表示されたと思います。

それではSQLを見てみましょう。

考え方としては、基本となるテーブル(今回は materielmaster)を普通にSelect、そこに、INNER JOIN で、別のテーブル(materieldivmaster)の中から条件(materieldivcodeが一致)が合うものだけをくっつけています。
CORSS JOINの場合は、すべての組み合わせの中から、一致するものを取り出すという考え方になると思います。
一方、こちらの場合は、元にするテーブルに対して、一つずつ条件を決めて結合するという考え方をしてください。

さて、このままではCROSS JOINとの違いや考え方がわかりにくいので、作っておいたテーブルにデータを追加してそれを例にして見ていきましょう。

lain=> \q
[lain@sixwish ~]$ cd sqlfile
[lain@sixwish sqlfile]$ vi UsageHistory.data.sql
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 1, 1, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 2, 2, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 3, 3, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 4, 4, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 6, 6, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 7, 7, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 8, 8, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values ( 9, 9, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (10,10, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (11,11, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (12,12, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (13,13, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (14,14, '2009-01-01', '2009-01-31', 1, '2009-01-01');
INSERT INTO usagehistory (materielcode,employeecode, startdate, enddate, register, registdate) Values (15,15, '2009-01-01', '2009-01-31', 1, '2009-01-01');
[lain@sixwish sqlfile]$ psql -f UsageHistory.data.sql lain
省略
[lain@sixwish sqlfile]$ psql
省略
lain=> 

さて、機器(materielmaster)とユーザー(employeemaster)を関連付けるためのテーブルにレコードを登録しました。
ここで注意しておくべき点は、materielcode、employeecodeが、 5 のもの無いということです。
データ作成ミスではないので、誤って追加したりしないようにしてください。

それでは結合させてみましょう。

まず、基本とするテーブルを決めます。今回は機器(materielmaster)を基本とします。
ここに、使用履歴(usagehistory)をくっつけます。条件は、materielcodeが二つのテーブルで一致するものです。
わかりにくくなるので、必要なフィールドだけ表示するようにします。

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 のデータが無いので、条件が合わずに表示されない事が確認できます。
ただ、そういう情報も欲しいという場合があるとおもいます。その場合のSQLについては次回説明するとして、次に進めます。

この結果に対して、materieldivmaster(資材区分)テーブルを結合してみましょう。

lain=> Select
lain->  materielmaster.materielcode,
lain->  materielmaster.materieldivcode,
lain->  materieldivmaster.materieldivname,
lain->  materielmaster.materielmaker, materielmaster.modelno,
lain->  usagehistory.employeecode
lain-> From materielmaster
lain->  Inner 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
            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=> 

このように、INNER JOINは後ろに付けていくという感じで考えるととっつきやすく、また、応用が利くと思います。

演習

問題1
一番最後のSQLの結果に対して、employeemaster を結合して employeesurname を表示するようにしてください。
結果的に、下記の図のように表示されることを確認してください。また、なぜこのような結果になるのかを考えてください。
lain=> SQL QUERY
 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=>