SQL 教育用資料

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

問合わせ(SELECT)には、さまざまな構文があります。ここから数回にわたって、さまざまな方法の問合わせを学んでゆきます。

結合テーブル

「テーブル結合」と言う場合もありますが、ここはPostgreSQLのマニュアルの書き方にしたがって統一します。マニュアルに結合テーブルは2つの（実または派生）テーブルから、指定した結合種類の規則に従って派生したテーブルです。内部結合、外部結合、およびクロス結合が使用可能です。とあります。言葉だけ見ても良くわからないと思うので、ひとつずつ簡単なものから確認していきます。

クロス結合(CROSS JOIN)

もっとも簡単なテーブル結合の方法です。クロスとあるように、すべての組み合わせで結合を行います。

百聞は一見にしかずということで、実際にどういうSQL文を書くのかと、実行結果を見てみましょう。

sqlite> SELECT * FROM materielmaster;
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08|
11|3|1|oIZO|S2100|E0001|021341|||2017-07-03 07:01:08|
12|3|1|oIZO|S2100|E0002|002321|||2017-07-03 07:01:08|
13|3|1|oIZO|S2100|E0003|002324|||2017-07-03 07:01:08|
14|3|1|oIZO|S2100|E0004|002325|||2017-07-03 07:01:08|
15|3|1|oIZO|S2100|E0005|002365|||2017-07-03 07:01:08|
sqlite>

sqlite> SELECT * FROM materieldivmaster;
1|デスクトップパソコン|
2|ノートパソコン|
3|ディスプレイ|
4|パッケージソフトウェア|
5|シェアウェア|
sqlite>

この二つのテーブルを結合します。

sqlite> SELECT * FROM materielmaster CROSS JOIN materieldivmaster;
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||1|デスクトップパソコン|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||2|ノートパソコン|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||3|ディスプレイ|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||4|パッケージソフトウェア|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||5|シェアウェア|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||1|デスクトップパソコン|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||2|ノートパソコン|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||3|ディスプレイ|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||4|パッケージソフトウェア|
(以下略)

元テーブルのレコード一つに対して、結合したレコードの数だけのパターンができるので、普通に掛け算を行ったのと同じ数のレコードが結果として戻ってきます。詳しいことは後にして、CROSS JOINのもう一つの書き方を見てみましょう。

sqlite> SELECT * FROM materielmaster,materieldivmaster;
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||1|デスクトップパソコン|
(以下略)

テーブルをカンマでつなげても同じ意味を示します。 CROSS JOINの場合は、このようにカンマつなぎで書いて省略することが多いので覚えておいてください。以降の説明でも基本的に省略方式にします。

結合したとしても、問題なくWHEREやORDER BYを使用することができます。 materielmakerが、DoLLのものだけ表示、並び順もmaterielcodeの順で並べてみます。

sqlite> SELECT * FROM materielmaster,materieldivmaster WHERE materielmaker='DoLL' ORDER BY materielcode;
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||1|デスクトップパソコン|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||2|ノートパソコン|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||3|ディスプレイ|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||4|パッケージソフトウェア|
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||5|シェアウェア|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||1|デスクトップパソコン|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||2|ノートパソコン|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||3|ディスプレイ|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||4|パッケージソフトウェア|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||5|シェアウェア|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||1|デスクトップパソコン|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||2|ノートパソコン|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||3|ディスプレイ|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||4|パッケージソフトウェア|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||5|シェアウェア|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||1|デスクトップパソコン|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||2|ノートパソコン|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||3|ディスプレイ|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||4|パッケージソフトウェア|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||5|シェアウェア|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||1|デスクトップパソコン|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||2|ノートパソコン|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||3|ディスプレイ|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||4|パッケージソフトウェア|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||5|シェアウェア|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||1|デスクトップパソコン|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||2|ノートパソコン|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||3|ディスプレイ|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||4|パッケージソフトウェア|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||5|シェアウェア|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||1|デスクトップパソコン|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||2|ノートパソコン|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||3|ディスプレイ|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||4|パッケージソフトウェア|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||5|シェアウェア|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||1|デスクトップパソコン|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||2|ノートパソコン|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||3|ディスプレイ|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||4|パッケージソフトウェア|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||5|シェアウェア|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||1|デスクトップパソコン|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||2|ノートパソコン|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||3|ディスプレイ|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||4|パッケージソフトウェア|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||5|シェアウェア|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||1|デスクトップパソコン|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||2|ノートパソコン|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||3|ディスプレイ|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||4|パッケージソフトウェア|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||5|シェアウェア|
sqlite>

さて、このような表示をされても困ってしまいます。普通は二つのテーブルに存在する materieldivcode が同じものだけ表示されれば十分だと思います。「一致する場合だけ表示」というのは、WHEREで条件を与えればできるのではないか？と言うことで試してみましょう。

sqlite> SELECT * FROM materielmaster,materieldivmaster WHERE materieldivcode=materieldivcode ORDER BY materielcode;
Error: ambiguous column name: materieldivcode
sqlite>

エラーになります。WHEREでフィールドが一致するものという意味で書いているのですが、どのテーブルのフィールドかわからないためです。というわけで、きちんとしたSQLの書き方にしたがって、テーブル名を付けて書き直してみます。

sqlite> SELECT * FROM materielmaster,materieldivmaster WHERE materielmaster.materieldivcode=materieldivmaster.materieldivcode ORDER BY materielcode;
1|2|1|DoLL|INSPIRON|D0001|000001|||2017-07-03 07:01:07||2|ノートパソコン|
2|2|1|DoLL|INSPIRON|D0002|000012|||2017-07-03 07:01:07||2|ノートパソコン|
3|2|1|DoLL|INSPIRON|D0003|000031|||2017-07-03 07:01:07||2|ノートパソコン|
4|2|1|DoLL|INSPIRON|D0004|000400|||2017-07-03 07:01:07||2|ノートパソコン|
5|2|1|DoLL|INSPIRON|D0005|000512|||2017-07-03 07:01:07||2|ノートパソコン|
6|1|1|DoLL|VOSTRO|DD001|001261|||2017-07-03 07:01:07||1|デスクトップパソコン|
7|1|1|DoLL|VOSTRO|DD002|024714|||2017-07-03 07:01:07||1|デスクトップパソコン|
8|1|1|DoLL|VOSTRO|DD003|004614|||2017-07-03 07:01:07||1|デスクトップパソコン|
9|1|1|DoLL|VOSTRO|DD004|000335|||2017-07-03 07:01:08||1|デスクトップパソコン|
10|1|1|DoLL|VOSTRO|DD005|004812|||2017-07-03 07:01:08||1|デスクトップパソコン|
11|3|1|oIZO|S2100|E0001|021341|||2017-07-03 07:01:08||3|ディスプレイ|
12|3|1|oIZO|S2100|E0002|002321|||2017-07-03 07:01:08||3|ディスプレイ|
13|3|1|oIZO|S2100|E0003|002324|||2017-07-03 07:01:08||3|ディスプレイ|
14|3|1|oIZO|S2100|E0004|002325|||2017-07-03 07:01:08||3|ディスプレイ|
15|3|1|oIZO|S2100|E0005|002365|||2017-07-03 07:01:08||3|ディスプレイ|
sqlite>

今度は表示されました。とはいえ、横に長くて何がなんだかわからないので、必要な情報だけ表示するように変えてみましょう。とりあえず、materielcode、materieldivcode、materieldivname、materielmaker、modelno、productno、serialnoがあればよいでしょうか。

sqlite> SELECT materielcode,materieldivcode,materieldivname,materielmaker,modelno,productno,serialno
   ...> FROM materielmaster,materieldivmaster
   ...> WHERE materielmaster.materieldivcode=materieldivmaster.materieldivcode
   ...> ORDER BY materielcode;
ERROR:  column reference "materieldivcode" is ambiguous
sqlite>

先ほどのエラーと同じです。SELECT句で選択したフィールド名が一致しているので、どちらのを表示すればいい？って事になっています。なので、WHEREでやったように、SELECTのほうにもテーブル指定を追加してみます。

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>

本当は全てのフィールドに対してテーブル指定を付けるのが正しいのですが、大変なので必要な部分だけに限定しています。

さて、今回はここまでとします。なお、一番最後の例の場合、この次の説明で使用する、Inner Joinを使用するのが一般的だと思います。理由なども含めて次回へ。