JOIN---SQL
結合(JOIN)
リレーショナルデータベースがリレーショナルデータベースたる所以は柔軟に結合(JOIN)が出来るということだと言えます。
その結合について見ていきましょう。
概要
リレーショナルデータベースでは、同一データベース内に複数のテーブルを持つことが出来ますが、そのテーブルとテーブルを結びつけることもできます。これによって、複数のテーブルを関連づけることができるので、うまく複数のテーブルにデータを分散させて管理したり、参照したりできるようになります。
また、例えば、ユーザーとその人の趣味を管理するのにhobby1とかhobby2というようなフィールドを持って、下の表のようなテーブルにすると
| user_id | name | gender | age | hobby1 | hobby2 |
|---|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | 53 | 読書 | 映画鑑賞 |
| 2 | 坂井 友子 | 女 | 45 | バラ栽培 | |
| 3 | 伝法谷 千代春 | 男 | 30 | 飲酒 | 因数分解 |
| 4 | 舛井 幸輔 | 男 | 62 |
趣味が3つ以上ある場合には、フィールドを追加しなくてはなりません。これを別テーブルで管理すると、趣味がいくつあっても大丈夫なようにできます(後述)。
この結合には、その分類のカテゴリーにより色々分類できますが、その1つとして、結合条件で次のような分けることができます。
- 等価結合
- 非等価結合
です。これはテーブルとテーブルとを結合する時の条件が、(1)結合させる条件にそれぞれのテーブル内のフィールドで同じ値を持っているレコード同士を結合するものと、(2)同じ値ではなく、別の条件(例えば「4月1日〜4月30日の範囲内」など)で結合させる場合です。
また、上記2種類の結合方法それぞれで、(1)条件に合うものだけを結合させる場合と(2)片方は条件に合わない場合でもレコードを拾ってくる場合(片方じゃなく両方という場合も)、そして(3)条件無しに総当たりでレコードを拾う、というもので分けるというカテゴリーで
- 内部結合
- 外部結合(LEFT OUTER、RIGHT OUTER、FULL OUTERとさらに3つに分けられます)
- クロス結合
です。
その他分類として
- 自己結合
- 自然結合
というものがあります。
これは、(1)結合するテーブルがそのテーブル自身というちょっと特殊な「自己結合」と(2)内部結合・外部結合の両方に使えるのですが、その際のON句を書かないで、同じフィールド名や型を探して、それを使って等価結合してくれる「自然結合」というものもありますが、これは内部結合・外部結合の省略した書き方と理解できます。
とりあえず、具体例がないとイメージしづらいでしょうから、ここではそのような結合があるんだ、とだけ覚えておいてください。この後、じっくり解説していきます。
具体的な例(これから使う3つのテーブルの説明)
実際のデータを取り扱わないとイメージがしにくいので、データベース名をtest、このtestに以下のような3つのテーブルを考えていきます。このテーブルはある会社が顧客情報から、CMのためのメールを「趣味や年齢ごとに内容を変える」というような目的で利用すると考えて下さい。
usersテーブル
名前、性別、年齢、メールアドレス、結婚相手(users内のidで表現、男性側に入れるルール)、住所1、住所2という情報を持っています。
SQL的には以下のようなテーブルで、サンプルとして4レコードをインサートします。
CREATE TABLE users ( user_id serial PRIMARY KEY, name varchar(50), gender varchar(2), age integer, mail varchar(128), match integer, address1 varchar(128), address2 varchar(128) ); INSERT INTO users (name, gender, age, mail, match, address1, address2) VALUES ('知久 和郎', '男', '53', 'sssss@yyy.com', '2', '東京都町田市', 'うんたら'); INSERT INTO users (name, gender, age, mail, match, address1, address2) VALUES ('坂井 友子', '女', '45', 'ttttt@yyy.com', null, '東京都町田市', 'かんたら'); INSERT INTO users (name, gender, age, mail, match, address1, address2) VALUES ('伝法谷 千代春', '男', '30', 'uuuuu@xxx.com', NULL, '神奈川県相模原市', 'すたこら'); INSERT INTO users (name, gender, age, mail, match, address1, address2) VALUES ('舛井 幸輔', '男', '62', 'vvvvv@zzz.com', NULL, '熊本県熊本市', 'さっさ');
実際のデータを表にすると以下のようになります。
| user_id | name | gender | age | match | address1 | address2 | |
|---|---|---|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | 53 | sssss@yyy.com | 2 | 東京都町田市 | うんたら |
| 2 | 坂井 友子 | 女 | 45 | ttttt@yyy.com | 東京都町田市 | かんたら | |
| 3 | 伝法谷 千代春 | 男 | 30 | uuuuu@xxx.com | 神奈川県相模原市 | すたこら | |
| 4 | 舛井 幸輔 | 男 | 62 | vvvvv@zzz.com | 熊本県熊本市 | さっさ |
hobbiesテーブル
2.1のusersテーブルそれぞれの人の趣味の情報を持っていて、0個以上で何個でも持てます。つまり、hobbiesのuser_idは必ずusersテーブルのidのいずれかになります(そうでなければ、誰の趣味だかわからなくなってしまいます。しかし今回は敢えて説明のために、usersのIDにはないものをレコードに入れてあります)。
SQL的には以下のようなテーブルで、サンプルとして5レコードをインサートします。
CREATE TABLE hobbies ( id serial PRIMARY KEY, user_id integer, hobby varchar(128) ); insert into hobbies(user_id, hobby) values ('1', '読書'); insert into hobbies(user_id, hobby) values ('1', '映画鑑賞'); insert into hobbies(user_id, hobby) values ('2', 'バラ栽培'); insert into hobbies(user_id, hobby) values ('3', '飲酒'); insert into hobbies(user_id, hobby) values ('3', '因数分解'); insert into hobbies(user_id, hobby) values ('5', 'アニメ鑑賞');
実際のデータを表にすると以下のようになります。
| id | user_id | hobby |
|---|---|---|
| 1 | 1 | 読書 |
| 2 | 1 | 映画鑑賞 |
| 3 | 2 | バラ栽培 |
| 4 | 3 | 飲酒 |
| 5 | 3 | 因数分解 |
| 6 | 5 | アニメ鑑賞 | |
age_gradeテーブル
年齢を1歳〜19歳、20歳〜39歳、その後は10年刻みで、100歳以上は全て8固定という会社独自の年齢のグレードを定義しています(0〜8)。
SQL的には以下のようなテーブルで、サンプルとして11レコードをインサートします。
CREATE TABLE age_grade ( id serial PRIMARY KEY, grade integer, hi integer, low integer ); insert into age_grade(grade, low, hi) values ('0', '1', '9'); insert into age_grade(grade, low, hi) values ('0', '10', '19'); insert into age_grade(grade, low, hi) values ('1', '20', '29'); insert into age_grade(grade, low, hi) values ('1', '30', '39'); insert into age_grade(grade, low, hi) values ('2', '40', '49'); insert into age_grade(grade, low, hi) values ('3', '50', '59'); insert into age_grade(grade, low, hi) values ('4', '60', '69'); insert into age_grade(grade, low, hi) values ('5', '70', '79'); insert into age_grade(grade, low, hi) values ('6', '80', '89'); insert into age_grade(grade, low, hi) values ('7', '90', '99'); insert into age_grade(grade, low, hi) values ('8', '100', '200');
実際のデータを表にすると以下のようになります。
| id | grade | hi | low |
|---|---|---|---|
| 1 | 0 | 1 | 9 |
| 2 | 0 | 10 | 19 |
| 3 | 1 | 20 | 29 |
| 4 | 1 | 30 | 39 |
| 5 | 2 | 40 | 49 |
| 6 | 3 | 50 | 59 |
| 7 | 4 | 60 | 69 |
| 8 | 5 | 70 | 79 |
| 9 | 6 | 80 | 89 |
| 10 | 7 | 90 | 99 |
| 11 | 8 | 100 | 200 |
さて、これらを使って、さっそく結合を見ていきましょう。
内部結合
usersテーブルとhobbiesテーブルは
- usersテーブルのid
- hobbiesテーブルのuser_id
が共通しているフィールドになります。つまり、「usersのuser_idが1の知久さん」のデータは「hobbiesテーブルのuser_idが1」のレコードが対応します。
この例ではuser_idが1のレコードが2つあるので、知久さんは趣味が2つあるということになります(前述したように、idが6のhobbiesのレコードは妙なレコードですが、説明のために入れてあります)。
数が少ないと、2つのテーブルを別々に見てもある程度わかりますが、数が多くなったりするとバラバラに見るのは難しくなります。そこでこれら2つのテーブルを結合 しましょう。結合するにはINNER JOINを使います。また、結合の条件はON句で行います。したがって、以下のような一般式で表されます。
SELECT 〜 from 左テーブル INNER JOIN 右テーブル ON 結合条件 WHERE句(必要に応じて)
ちなみに右・左というのは次のようになっていますので、覚えてください。
これを今回の例にあてはめると、次のようになります。フィールドは指定して「左のテーブル.id、左のテーブル.name、左のテーブル.gender、右のテーブル.mail」とし(住所などは省略)、結合条件としては「ON 左のテーブル.id = 右のテーブル.user_id」とします。結果、下のようなSQLを実行します。
SELECT u.user_id as id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u INNER JOIN hobbies AS h ON u.user_id = h.user_id;
これを実行すると、以下のようなレコード群が拾えます。まるで、usersテーブル(左)の一部と、hobbiesテーブル(右)の一部が結合しているテーブルを見ているようです。
| id | name | gender | hobby | |
|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 読書 |
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 映画鑑賞 |
| 2 | 坂井 友子 | 女 | ttttt@yyy.com | バラ栽培 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 飲酒 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 因数分解 |
内部結合のポイント
- 結合条件である「ON u.user_id = h.user_id」が合致するレコードだけが選択されます。
- 1.のため、usersの「舛井さん」とhobbiesの「アニメ鑑賞」が除外されています。
- 2つ趣味のある「知久さん」と「伝法谷さん」はそれぞれ2行分出力されています。
- 内部結合の場合(後述する外部結合ではダメですが)、「ON句」の代わりに「WHERE句」で、WHERE u.user_id = h.user_idと書いても結果は同じです。
- 内部結合の「INNER」は省略できます。
- 下の図のような集合でイメージすると、積集合になります。
外部結合
内部結合では、「舛井さん」が結合条件にあてはまらなかったので選択されていませんでした。しかし用途によっては積極的に「舛井さんには趣味がない」というような情報を得たい場合もあり得ます。
そんな時、外部結合を使うと便利です。内部結合と違い、結合条件に合わない場合でも左右どちらかのテーブル(或いは両方)のレコードを拾ってくるところが違います。ちなみに、左右のテーブルという言い方は内部結合と同じですが、再掲しておきます(外部結合の方が重要なので)。
結合条件にあわなくても左のテーブルのレコードを出す場合をLEFT OUTER JOINを使い、右のテーブルのレコードを出す場合をRIGHT OUTER JOINを使い、両方のテーブルのレコードを出したい場合はFULL OUTER JOINを使います。
一般式としては
SELECT 〜 from 左テーブル (LEFT|RIGHT|FULL) OUTER JOIN 右テーブル ON 結合条件 WHERE句(必要に応じて)
のようになります。
LEFT OUTER JOIN
さて、先ほどの「舛井さんには趣味がない」という情報を取得するために、このusersテーブル右テーブルとしてSQLを書くと次のようになります。
SELECT u.user_id as id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u LEFT OUTER JOIN hobbies AS h ON u.user_id = h.user_id;
上記のSQLを実行すると、次のようなレコードが選択されます。
| id | name | gender | hobby | |
|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 映画鑑賞 |
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 読書 |
| 2 | 坂井 友子 | 女 | ttttt@yyy.com | バラ栽培 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 因数分解 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 飲酒 |
| 4 | 舛井 幸輔 | 男 | vvvvv@zzz.com | |
※舛井さんのhobbyには値がありませんが、これは対応する値がないからです。
また「RIGHT OUTER JOIN」はLEFTと反対に結合条件にあてはまらなくても右側のテーブルのレコードが拾われてきます(FROM句とJOIN句のあとのテーブル名を反対にすれば、LEFT OUTER JOINでも同じ結果になります)。次のSQLがRIGHT OUTER JOINのSQLの例です。
SELECT u.user_id as id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u RIGHT OUTER JOIN hobbies AS h ON u.user_id = h.user_id;
結果は以下のようになります。
| id | name | gender | hobby | |
|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 読書 |
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 映画鑑賞 |
| 2 | 坂井 友子 | 女 | ttttt@yyy.com | バラ栽培 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 飲酒 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 因数分解 |
| |
|
|
|
アニメ鑑賞 |
次に「FULL OUTER JOIN」は結合条件にあてはまらなくても左右両テーブルのレコードが表示されます。次はそのSQLの例です。
SELECT u.user_id as id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u FULL OUTER JOIN hobbies AS h ON u.user_id = h.user_id;
結果は以下のようになります。
| id | name | gender | hobby | |
|---|---|---|---|---|
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 読書 |
| 1 | 知久 和郎 | 男 | sssss@yyy.com | 映画鑑賞 |
| 2 | 坂井 友子 | 女 | ttttt@yyy.com | バラ栽培 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 飲酒 |
| 3 | 伝法谷 千代春 | 男 | uuuuu@xxx.com | 因数分解 |
| 4 | 舛井 幸輔 | 男 | vvvvv@zzz.com | |
| |
|
|
|
アニメ鑑賞 |
外部結合のポイント
- LEFT OUTER JOINの場合は、「ON u.user_id = h.user_id」に合致しないレコードでも左側のレコードは表示されます(舛井さん)。
- RIGHT OUTER JOINの場合は、「ON u.user_id = h.user_id」に合致しないレコードでも右側のレコードは表示されます(アニメ鑑賞)。
- FULL OUTER JOINの場合は、「ON u.user_id = h.user_id」に合致しないレコードでも左右両側のレコードが表示されます(舛井さんとアニメ鑑賞)。
- 内部結合同様、2つ趣味のある「知久さん」と「伝法谷さん」はそれぞれ2行分選択されています。
- WHERE句では表現できません。
非等価結合
ここまで全て「等価結合」を見てきましたが、次のSQLは非等価結合の例です。結合条件にBETWEEN(「値が以上・以下」になっている)を使い、その年齢のGRADEを判断して表示させています。
SELECT u.user_id ,u.name ,u.age ,a.grade FROM users AS u INNER JOIN age_grade AS a ON u.age BETWEEN a.low AND a.hi;
結果は次のようになります。
| id | name | age | grade |
|---|---|---|---|
| 3 | 伝法谷 千代春 | 30 | 1 |
| 2 | 坂井 友子 | 45 | 2 |
| 1 | 知久 和郎 | 53 | 3 |
| 4 | 舛井 幸輔 | 62 | 4 |
自己結合
自己結合とは、結合する対象のテーブルが自分自身というものです。今回の例で考えるならば、usersテーブルで結婚している人をみつけ、その人達のメールアドレスを拾いたいような場合です。下の例は
SELECT u.name AS 名前 ,u.mail AS メール ,u2.name AS 結婚相手 ,u2.mail AS 結婚相手のメール FROM users u INNER JOIN users u2 ON u.user_id = u2.match;
これを実行すると、以下のようになります。
| 名前 | メールアドレス | 結婚相手 | 結婚相手のメールアドレス |
|---|---|---|---|
| 知久 和郎 | sssss@yyy.com | 坂井 友子 | ttttt@yyy.com |
クロス結合、自然結合
これらはあまり使われる結合ではないのですが、簡単に触れておきます。
まず、クロス結合は結合条件を指定しない場合で、例えば、次のようにSQLがそうです。
SELECT u.name ,h.hobby FROM users AS u, hobbies AS h ORDER BY u.name ,h.hobby;
これを実行すると下のように、総当たりで結合が実行されます。usersテーブルに4つのレコード、hobbiesテーブルには6つのレコードがあるので
4 × 6 = 24レコード
が作成されました。ほとんどこんなレコードが必要になることはなさそうなので、知識だけで十分でしょう。
| name | hobby |
|---|---|
| 坂井 友子 | アニメ鑑賞 |
| 坂井 友子 | バラ栽培 |
| 坂井 友子 | 因数分解 |
| 坂井 友子 | 飲酒 |
| 坂井 友子 | 映画鑑賞 |
| 坂井 友子 | 読書 |
| 舛井 幸輔 | アニメ鑑賞 |
| 舛井 幸輔 | バラ栽培 |
| 舛井 幸輔 | 因数分解 |
| 舛井 幸輔 | 飲酒 |
| 舛井 幸輔 | 映画鑑賞 |
| 舛井 幸輔 | 読書 |
| 知久 和郎 | アニメ鑑賞 |
| 知久 和郎 | バラ栽培 |
| 知久 和郎 | 因数分解 |
| 知久 和郎 | 飲酒 |
| 知久 和郎 | 映画鑑賞 |
| 知久 和郎 | 読書 |
| 伝法谷 千代春 | アニメ鑑賞 |
| 伝法谷 千代春 | バラ栽培 |
| 伝法谷 千代春 | 因数分解 |
| 伝法谷 千代春 | 飲酒 |
| 伝法谷 千代春 | 映画鑑賞 |
| 伝法谷 千代春 | 読書 |
また、自然結合は同じフィールド名で型も同じだと明示しないでもそのフィールドでJOINしてくれうというもの。
SELECT u.user_id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u NATURAL JOIN hobbies AS h
で
SELECT u.user_id ,u.name ,u.gender ,u.mail ,h.hobby FROM users AS u INNER JOIN hobbies AS h ON u.user_id = h.user_id;
と全く同じ結果になります。しかし、これはテーブルのフィールド名などがわかっていないとどういう結果になるかがわからないので、通常は結合条件を明示した方が良さそうです。
