SQL 教育用資料

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

内部結合(INNER JOIN / JOIN)

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

sqlite> SELECT materielcode,materielmaster.materieldivcode,materieldivname,
   ...> materielmaker,modelno,productno,serialno
   ...> FROM materielmaster,materieldivmaster
   ...> WHERE materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...> ORDER BY materielcode;
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
sqlite>
sqlite> SELECT materielcode,materielmaster.materieldivcode,materieldivname,
   ...> materielmaker,modelno,productno,serialno
   ...> FROM materielmaster
   ...> Inner Join materieldivmaster On materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...> ORDER BY materielcode;
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
sqlite>

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

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

考え方としては、基本となるテーブル(今回は materielmaster)を普通にSELECT。そこに、INNER JOIN を使って別のテーブル(materieldivmaster)の中から条件(materieldivcodeが一致)が合うものだけをくっつけています。

CORSS JOINの場合は、すべての組み合わせの中から、一致するものを取り出すという考え方になります。一方、こちらの場合は、元にするテーブルに対して、一つずつ条件を決めて結合するという考え方をしてください。

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

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');

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

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

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

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

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

sqlite> SELECT
   ...>  materielmaster.materielcode,
   ...>  materielmaster.materieldivcode,
   ...>  materieldivmaster.materieldivname,
   ...>  materielmaster.materielmaker, materielmaster.modelno,
   ...>  usagehistory.employeecode
   ...> FROM materielmaster
   ...>  Inner 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
   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>

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

演習

問題1

一番最後のSQLの結果に対して、employeemaster を結合して employeesurname を表示するようにしてください。 結果的に、下記の図のように表示されることを確認してください。また、なぜこのような結果になるのかを考えてください。

sqlite> '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 | 河本            | 哲平