外部結合
外部結合はそれぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。基本となる構文は次の3つが用意されています。
SELECT テーブル名.カラム名, ... FROM テーブル名1 LEFT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
SELECT テーブル名.カラム名, ... FROM テーブル名1 RIGHT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
SELECT テーブル名.カラム名, ... FROM テーブル名1 FULL OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
基本的に内部結合の場合と同じですが、外部結合では結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がどちらかのテーブルにしかなかった場合でもデータとして取得します。この時、どちらのテーブルのデータを取得するかで3つの構文が用意されています。
LEFT OUTER JOINではFROMの後に書かれたテーブルのデータだけを取得します。RIGHT OUTER JOINではJOINの後に書かれたテーブルのデータだけを取得します。FULL OUTER JOINでは両方のテーブルのデータを取得します。
テーブルの結合
具体的な例で見てみます。次のような2つのテーブルを用意しました。
goods :
| code | cateid | name |
|---|---|---|
| B001 | 1 | 1Q84 |
| D001 | 2 | ドロップ |
| B002 | 1 | 植物図鑑 |
| F001 | 4 | 抹茶ロール |
| D002 | 2 | ポニョ |
| B003 | 1 | 武士道 |
| S001 | 5 | ワープロ |
| F002 | 4 | 栗きんとん |
cate :
| id | name |
|---|---|
| 1 | 和書 |
| 2 | DVD |
| 3 | スポーツ |
| 4 | 食品 |
外部結合(LEFT OUTER JOIN)を行うには、2つのテーブルで結合の対象となるカラムを指定します。例えば次のように指定します。
select * from goods inner left outer join cate on goods.cateid = cate.id;
上記の場合、「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを使って結合します。
データの取得
データの取得は次のように行われます。まず「goods」テーブルの最初のデータの「cateid」カラムの値に対して、「cate」テーブルの「id」カラムの中に同じ値があるかどうかを調べます。存在した場合は「goods」テーブルのデータと「cate」テーブルの一致したデータと結合させてまとめて一つのデータとして取得します。
| goods.code | goods.cateid | goods.name | cate.id | cate.name |
|---|---|---|---|---|
| B001 | 1 | 1Q84 | 1 | 和書 |
同じように「goods」テーブルのデータを順に結合を行っていきます。もし「goods」テーブルの「cateid」カラムの値が「cate」テーブルの「id」カラムの値の中に一致するものがなかった場合、LEFT OUTER JOINでは「goods」テーブルのデータを取得します。このデータでは対応する「cate」テーブルのデータが存在しないため、NULLが代わりに取得されます。
結果的に次のようなデータが取得されます。
| goods.code | goods.cateid | goods.name | cate.id | cate.name |
|---|---|---|---|---|
| B001 | 1 | 1Q84 | 1 | 和書 |
| D001 | 2 | ドロップ | 2 | DVD |
| B002 | 1 | 植物図鑑 | 1 | 和書 |
| F001 | 4 | 抹茶ロール | 4 | 食品 |
| D002 | 2 | ポニョ | 2 | DVD |
| B003 | 1 | 武士道 | 1 | 和書 |
| S001 | 5 | ワープロ | NULL | NULL |
| F002 | 4 | 栗きんとん | 4 | 食品 |
結合されたデータには各テーブルに存在していたカラムが全て含まれることになります。
取得するカラムの指定方法
結合された全データの中で、どのカラムを取得するかはSELECT文の後ろで指定します。カラムは「テーブル名.カラム名」の形式で指定して下さい。例えば「goods」テーブルの「code」カラムと「cate」テーブルの「name」カラムの値を取得するには次のように記述します。
select goods.code, cate.name from goods
left outer join cate on goods.cateid = cate.id;
どちらかのテーブルにしか存在しないカラムを指定する場合はテーブル名を省略できます。例えば「goods」テーブルの「code」カラムを取得したい場合、「cate」テーブルには「code」カラムは存在しませんので単に「code」と記載できます。
select code, cate.name from goods
left outer join cate on goods.cateid = cate.id;
それに対して「goods」テーブルの「name」カラムを取得したい場合には「cate」テーブルにも「name」カラムが存在するので必ずテーブル名を付けて「goods.name」と指定する必要があります。
サンプル
では実際に試してみます。
次のようなテーブルを対象とします。
「goods」テーブルの「cateid」カラムと「cate」テーブルの「id」カラムを外部結合(LEFT OUTER JOIN)してデータを取得します。
select * from goods left outer join cate on goods.cateid = cate.id;
内部結合の場合と異なり、対象のカラムの値が「cate」テーブルの「id」カラムに存在しないような「goods」テーブルのデータも取得していることが確認できます。
なおLEFT OUTER JOINの代わりにRIGHT OUTER JOINを使ってみます。
「SQL error: RIGHT and FULL OUTER JOINs are not currently supported」とエラー表示が行われます。RIGHT OUTER JOINとFULL OUTER JOINは現時点では利用できません。