テーブルの結合は、SELECT文のFROM句の後に複数テーブルを指定し、WHEREで条件を指定することで可能だが、
今回はJOINという演算子を使用して結合を行う。
JOINにはいくつかの種類があり、目的に応じて使い分ける必要がある。
またJOINは演算子なので、基本的に２つのテーブルの結合になる。３つ以上のテーブルの結合は後述
以前定義した、grade, class, studentテーブルを利用し、次のようなテーブルを作成する。
| gnum | color |
|---|---|
| 1 | 青 |
| 2 | 赤 |
| 3 | 緑 |
| gnum | cnum | color |
|---|---|---|
| 1 | 100 | 青 |
| 2 | 100 | 赤 |
| 3 | 100 | 緑 |
| num | color |
|---|---|
| 1 | 青 |
| 2 | 赤 |
| 3 | 緑 |
| gnum | cnum | charge |
|---|---|---|
| 1 | 1 | 田中 |
| 1 | 2 | 鈴木 |
| 1 | 3 | 佐藤 |
| 1 | 4 | 松井 |
| 2 | 1 | 岡田 |
| … | … | … |
| gnum | cnum | snum | charge |
|---|---|---|---|
| 1 | 1 | 200 | 田中 |
| 1 | 2 | 200 | 鈴木 |
| 1 | 3 | 200 | 佐藤 |
| 1 | 4 | 200 | 松井 |
| 2 | 1 | 200 | 岡田 |
| … | … | … | … |
| gnum | num | charge |
|---|---|---|
| 1 | 1 | 田中 |
| 1 | 2 | 鈴木 |
| 1 | 3 | 佐藤 |
| 1 | 4 | 松井 |
| 2 | 1 | 岡田 |
| … | … | … |
| gnum | cnum | snum | name | … |
|---|---|---|---|---|
| 1 | 1 | 1 | 大石友美 | … |
| 1 | 1 | 2 | 中村優作 | … |
| 1 | 1 | 3 | 北川陽子 | … |
| 1 | 1 | 4 | 望月慎太郎 | … |
| 1 | 1 | 5 | 木村葉子 | … |
| 1 | 1 | 6 | 藤田智子 | … |
| 1 | 2 | 1 | 石川大地 | … |
| … | … | … | … |
次のＳＱＬをファイルとして作成。（setup121.sql）
ログイン画面から実行。
oddtbs=> \i setup121.sql DROP TABLE ... SELECT ... oddtbs=> select * from g01; oddtbs=> select * from g01; gnum | color ------+------- 1 | 青 2 | 赤 3 | 緑 (3 rows) oddtbs=> select * from g02; gnum | cnum | color ------+------+------- 1 | 100 | 青 2 | 100 | 赤 3 | 100 | 緑 (3 rows) oddtbs=> select * from g03; num | color -----+------- 1 | 青 2 | 赤 3 | 緑 (3 rows) oddtbs=> select * from c01; gnum | cnum | charge ------+------+-------- 1 | 1 | 田中 1 | 2 | 佐藤 1 | 3 | 鈴木 1 | 4 | 松井 2 | 1 | 岡田 2 | 2 | 山本 2 | 3 | 斎藤 2 | 4 | 吉田 3 | 1 | 村山 3 | 2 | 森田 3 | 3 | 原田 3 | 4 | 島田 (12 rows) oddtbs=> select * from c02; gnum | cnum | snum | charge ------+------+------+-------- 1 | 1 | 200 | 田中 1 | 2 | 200 | 佐藤 1 | 3 | 200 | 鈴木 1 | 4 | 200 | 松井 2 | 1 | 200 | 岡田 2 | 2 | 200 | 山本 2 | 3 | 200 | 斎藤 2 | 4 | 200 | 吉田 3 | 1 | 200 | 村山 3 | 2 | 200 | 森田 3 | 3 | 200 | 原田 3 | 4 | 200 | 島田 (12 rows) oddtbs=> select * from c03; gnum | num | charge ------+-----+-------- 1 | 1 | 田中 1 | 2 | 佐藤 1 | 3 | 鈴木 1 | 4 | 松井 2 | 1 | 岡田 2 | 2 | 山本 2 | 3 | 斎藤 2 | 4 | 吉田 3 | 1 | 村山 3 | 2 | 森田 3 | 3 | 原田 3 | 4 | 島田 (12 rows) oddtbs=> select * from s01; gnum | cnum | snum | name ------+------+------+-------------- 1 | 1 | 1 | 大石友美 1 | 1 | 2 | 中村優作 1 | 1 | 3 | 北川陽子 1 | 1 | 4 | 望月慎太郎 1 | 1 | 5 | 木村葉子 1 | 1 | 6 | 藤田智子 1 | 2 | 1 | 石川大地 ... ... 3 | 4 | 4 | 鷲山綾子 3 | 4 | 5 | 内山絢平 3 | 4 | 6 | 梶間昇平 (72 rows)
CROSS JOINは全ての行の組み合わせを選択する。これを直積という。
目的が直積であることから、条件を指定することはできない。
SELECT 選択リスト FROM テーブル１ CROSS JOIN テーブル２;
oddtbs=> select * from g01 cross join g02;
gnum | color | gnum | cnum | color
------+-------+------+------+-------
1 | 青 | 1 | 100 | 青
1 | 青 | 2 | 100 | 赤
1 | 青 | 3 | 100 | 緑
2 | 赤 | 1 | 100 | 青
2 | 赤 | 2 | 100 | 赤
2 | 赤 | 3 | 100 | 緑
3 | 緑 | 1 | 100 | 青
3 | 緑 | 2 | 100 | 赤
3 | 緑 | 3 | 100 | 緑
(9 rows)
NATURAL JOINは、両テーブルの名前と型が同じである全ての列が等しい組み合わせを選択する。
すなわち、両テーブルの名前と型が同じである全ての列を結合の条件として使用する。
また同じである列は重複して表示しない。
条件が完全に決定されるため、条件の指定はできない。
SELECT 選択リスト FROM テーブル１ NATURAL JOIN テーブル２;
oddtbs=> select * from g01 natural join c01; gnum | color | cnum | charge ------+-------+------+-------- 1 | 青 | 1 | 田中 1 | 青 | 2 | 佐藤 1 | 青 | 3 | 鈴木 1 | 青 | 4 | 松井 2 | 赤 | 1 | 岡田 2 | 赤 | 2 | 山本 2 | 赤 | 3 | 斎藤 2 | 赤 | 4 | 吉田 3 | 緑 | 1 | 村山 3 | 緑 | 2 | 森田 3 | 緑 | 3 | 原田 3 | 緑 | 4 | 島田 (12 rows) ※ 両テーブルのgnum列が結合の条件として使用された。 oddtbs=> select * from g01 natural join s01; gnum | color | cnum | snum | name ------+-------+------+------+-------------- 1 | 青 | 1 | 1 | 大石友美 1 | 青 | 1 | 2 | 中村優作 1 | 青 | 1 | 3 | 北川陽子 1 | 青 | 1 | 4 | 望月慎太郎 1 | 青 | 1 | 5 | 木村葉子 1 | 青 | 1 | 6 | 藤田智子 1 | 青 | 2 | 1 | 石川大地 ... ... 3 | 緑 | 4 | 4 | 鷲山綾子 3 | 緑 | 4 | 5 | 内山絢平 3 | 緑 | 4 | 6 | 梶間昇平 (72 rows) ※ 両テーブルのgnum列が結合の条件として使用された。 oddtbs=> select * from c01 natural join s01; gnum | cnum | charge | snum | name ------+------+--------+------+-------------- 1 | 1 | 田中 | 1 | 大石友美 1 | 1 | 田中 | 2 | 中村優作 1 | 1 | 田中 | 3 | 北川陽子 1 | 1 | 田中 | 4 | 望月慎太郎 1 | 1 | 田中 | 5 | 木村葉子 1 | 1 | 田中 | 6 | 藤田智子 ... ... 3 | 4 | 島田 | 4 | 鷲山綾子 3 | 4 | 島田 | 5 | 内山絢平 3 | 4 | 島田 | 6 | 梶間昇平 (72 rows) ※ 両テーブルのgnumとcnum列が結合の条件として使用された。
NATURAL JOINは同じ名前と型の列を全て結合の条件として使用する。
よって、同じ名前と型の列の中に結合の条件として使用したくない列が含まれている場合、NATURAL JOINは使用できない。
oddtbs=> select * from g02 natural join c01; gnum | cnum | color | charge ------+------+-------+-------- (0 rows) oddtbs=> select * from g02 natural join s01; gnum | cnum | color | snum | name ------+------+-------+------+------ (0 rows) oddtbs=> select * from c02 natural join s01; gnum | cnum | snum | charge | name ------+------+------+--------+------ (0 rows)
そのような場合は、USING句を使用する。
USING句は同じ名前と型のうち、結合の条件として使用する列を指定する。
NATURAL JOINと同様に同じである列は重複して表示しない。
SELECT 選択リスト FROM テーブル１ JOIN テーブル２ USING(列の並び);
oddtbs=> select * from g02 join c01 using(gnum); gnum | cnum | color | cnum | charge ------+------+-------+------+-------- 1 | 100 | 青 | 1 | 田中 1 | 100 | 青 | 2 | 佐藤 1 | 100 | 青 | 3 | 鈴木 1 | 100 | 青 | 4 | 松井 2 | 100 | 赤 | 1 | 岡田 2 | 100 | 赤 | 2 | 山本 2 | 100 | 赤 | 3 | 斎藤 2 | 100 | 赤 | 4 | 吉田 3 | 100 | 緑 | 1 | 村山 3 | 100 | 緑 | 2 | 森田 3 | 100 | 緑 | 3 | 原田 3 | 100 | 緑 | 4 | 島田 (12 rows) oddtbs=> select * from g02 join s01 using(gnum); gnum | cnum | color | cnum | snum | name ------+------+-------+------+------+-------------- 1 | 100 | 青 | 1 | 1 | 大石友美 1 | 100 | 青 | 1 | 2 | 中村優作 1 | 100 | 青 | 1 | 3 | 北川陽子 1 | 100 | 青 | 1 | 4 | 望月慎太郎 1 | 100 | 青 | 1 | 5 | 木村葉子 1 | 100 | 青 | 1 | 6 | 藤田智子 1 | 100 | 青 | 2 | 1 | 石川大地 ... ... 3 | 100 | 緑 | 4 | 4 | 鷲山綾子 3 | 100 | 緑 | 4 | 5 | 内山絢平 3 | 100 | 緑 | 4 | 6 | 梶間昇平 (72 rows) oddtbs=> select * from c02 join s01 using(gnum, cnum); gnum | cnum | snum | charge | snum | name ------+------+------+--------+------+-------------- 1 | 1 | 200 | 田中 | 1 | 大石友美 1 | 1 | 200 | 田中 | 2 | 中村優作 1 | 1 | 200 | 田中 | 3 | 北川陽子 1 | 1 | 200 | 田中 | 4 | 望月慎太郎 1 | 1 | 200 | 田中 | 5 | 木村葉子 1 | 1 | 200 | 田中 | 6 | 藤田智子 1 | 2 | 200 | 佐藤 | 1 | 石川大地 ... ... 3 | 4 | 200 | 島田 | 4 | 鷲山綾子 3 | 4 | 200 | 島田 | 5 | 内山絢平 3 | 4 | 200 | 島田 | 6 | 梶間昇平 (72 rows)
結合の条件として使用したい列の中に、異なる名前のものが含まれる場合は、NATURAL JOINもUSING句も使用できない。
例えば次の結合など。
g03とc01
g03とs01
c03とs01
そのような場合はON句を使用する。
ON句はWHEREと同様に任意の結合条件を指定できる。
ただし、NATURALでもCROSSでもないJOINはUSING句かON句が必要なので、全てをWHEREで代用することはできない。
ON句とWHERE句の併用は可能。通常は結合の条件はON句で、その他の条件はWHEREで指定する。
NATURAL JOINやON句とは異なり、同じである列も全て表示する。
SELECT 選択リスト FROM テーブル１ JOIN テーブル２ ON 結合条件;
oddtbs=> select * from g03 g join c01 c on g.num = c.gnum; num | color | gnum | cnum | charge -----+-------+------+------+-------- 1 | 青 | 1 | 1 | 田中 1 | 青 | 1 | 2 | 佐藤 1 | 青 | 1 | 3 | 鈴木 1 | 青 | 1 | 4 | 松井 2 | 赤 | 2 | 1 | 岡田 2 | 赤 | 2 | 2 | 山本 2 | 赤 | 2 | 3 | 斎藤 2 | 赤 | 2 | 4 | 吉田 3 | 緑 | 3 | 1 | 村山 3 | 緑 | 3 | 2 | 森田 3 | 緑 | 3 | 3 | 原田 3 | 緑 | 3 | 4 | 島田 (12 rows) oddtbs=> select * from g03 g join s01 s on g.num = s.gnum; num | color | gnum | cnum | snum | name ----+-------+------+------+------+-------------- 1 | 青 | 1 | 1 | 1 | 大石友美 1 | 青 | 1 | 1 | 2 | 中村優作 1 | 青 | 1 | 1 | 3 | 北川陽子 1 | 青 | 1 | 1 | 4 | 望月慎太郎 1 | 青 | 1 | 1 | 5 | 木村葉子 1 | 青 | 1 | 1 | 6 | 藤田智子 1 | 青 | 1 | 2 | 1 | 石川大地 ... ... 3 | 緑 | 3 | 4 | 4 | 鷲山綾子 3 | 緑 | 3 | 4 | 5 | 内山絢平 3 | 緑 | 3 | 4 | 6 | 梶間昇平 (72 rows) oddtbs=> select * from c03 c join s01 s on c.gnum = s.gnum and c.num = s.cnum; gnum | num | charge | gnum | cnum | snum | name ------+-----+--------+------+------+------+-------------- 1 | 1 | 田中 | 1 | 1 | 1 | 大石友美 1 | 1 | 田中 | 1 | 1 | 2 | 中村優作 1 | 1 | 田中 | 1 | 1 | 3 | 北川陽子 1 | 1 | 田中 | 1 | 1 | 4 | 望月慎太郎 1 | 1 | 田中 | 1 | 1 | 5 | 木村葉子 1 | 1 | 田中 | 1 | 1 | 6 | 藤田智子 ... ... 3 | 4 | 島田 | 3 | 4 | 4 | 鷲山綾子 3 | 4 | 島田 | 3 | 4 | 5 | 内山絢平 3 | 4 | 島田 | 3 | 4 | 6 | 梶間昇平 (72 rows) oddtbs=> select * from c03 c join s01 s on (c.gnum, c.num) = (s.gnum, s.cnum); 結果は上と同じ。