外部結合

外部結合とは

6日目では、内部結合および外部結合について説明しました。ここでは、さらに外部結合について説明します。

内部結合が、データの構造が一致する部分のみの接続であったのに対し、外部結合は、それぞれのテーブルの指定した列の値が一致するデータに加えてどちらかのテーブルにしか存在しないデータについても取得します。MySQLでは、基本となる構文は次の2つが用意されています。

外部結合の構文①
SELECT テーブル名.カラム名, ... FROM テーブル名1
LEFT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
外部結合の構文②
SELECT テーブル名.カラム名, ... FROM テーブル名1
RIGHT OUTER JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

基本的に内部結合の場合と同じですが、外部結合では結合の対象となっているカラムの値が一致しているデータに加えて、カラムの値がどちらかのテーブルにしかなかった場合でもデータとして取得します。この時、どちらのテーブルのデータを取得するかで2つの構文が用意されています。(表7-1.)

表7-1.外部結合の形式
書式 内容
LEFT OUTER JOIN FROMの後に書かれたテーブルのデータだけを取得します。
RIGHT OUTER JOIN JOINの後に書かれたテーブルのデータだけを取得します。

サンプルデータ

外部結合を説明するにあたり、さらに新たなテーブル、購入履歴(purchase_hisotry)を追加しましょう。追加するテーブルの概要は以下の通りです。個々に出てくる、商品コードは、resouceテーブルのものに対応しています。

表7-2.テーブルpurchase_history
項目列名データ型属性
日付dateDATENOT NULL(空白を許さない)
商品コードcode CHAR(6)NOT NULL(空白を許さない)
numintNOT NULL(空白を許さない)

このテーブルには次のようなデータが格納されているとします。

表7-3.テーブルpurchase_historyのデータ
datecodenum
2013/1/13100001100
2013/1/1710000220
2013/2/110010331
2013/2/31001015
2013/3/510020331
2013/3/1210000313
2013/4/3010020124
2013/5/910000150
2013/7/19100C0210
2013/8/251001025

SQL文は以下の通りになります。

Sample701.sql
#購入履歴テーブルの生成
CREATE TABLE purchase_history(
	date DATE NOT NULL,
	code CHAR(6) NOT NULL,
	num INT NOT NULL
);

#データの挿入
INSERT INTO purchase_history VALUES ('2013/1/13','100001',100);
INSERT INTO purchase_history VALUES ('2013/1/17','100002',20);
INSERT INTO purchase_history VALUES ('2013/2/1','100103',31);
INSERT INTO purchase_history VALUES ('2013/2/3','100101',5);
INSERT INTO purchase_history VALUES ('2013/3/5','100203',31);
INSERT INTO purchase_history VALUES ('2013/3/12','100003',13);
INSERT INTO purchase_history VALUES ('2013/4/30','100201',24);
INSERT INTO purchase_history VALUES ('2013/5/9','100001',50);
INSERT INTO purchase_history VALUES ('2013/7/19','100C02',10);
INSERT INTO purchase_history VALUES ('2013/8/25','100102',5);

結果、以下のようなテーブルが完成します。(図7-1.)

図7-1.purchase_historyテーブル
purchase_historyテーブル

外部結合の実行

では、実際に外部結合を行ってみることにしましょう。codeカラムの値をもとに、LEFT,RIGHTの結合を行ってみます。まずは、LEFTの結合から見てみましょう。

Sample702.sql
SELECT * FROM purchase_history LEFT OUTER JOIN resource ON purchase_history.code = resource.code;

実行結果は、以下のようになります。(図7-2.)

図7-2.LEFT OUTER JOINによる結合
LEFT OUTER JOINによる結合

続いて、RIGHT OUTER JOINを用いてみましょう。SQL文は以下のようになります。

Sample703.sql
SELECT * FROM purchase_history RIGHT OUTER JOIN resource ON purchase_history.code = resource.code;

実行結果は、以下のようになります。(図7-3.)

図7-3.RIGHT OUTER JOINによる結合
RIGHT OUTER JOINによる結合

結果が異なる理由

実行結果からわかるとおり、同じテーブル同士を、同じ条件で結合しても、結果は異なります。では、いったいどうしてこのような違いがでるのでしょうか。

LEFT OUTER JOINの場合、purchase_historyテーブルにあるデータはすべて表示されます。しかし、codeカラムの中の、「100C02」に該当するデータが、resourceコラムのcodeコラムの中に存在しません。そのような場合は、「該当する行がない」ことを表すために、右側、つまりresourceテーブルの側には、NULLが表示されます。(図7-4.)

図7-4.LEFT OUTER JOINの内容
LEFT OUTER JOINの内容

同様に、RIGHT OUTER JOINのケースをみてみると、こんどは右側、つまりresourceテーブルのデータはすべて表示されますが、ここのcodeカラムに出てくるに出てくる「100202」および「100C01」は、purchase_historyテーブルには存在しません。そのため、この場合は、purchase_history側の該当する行のデータがNULLになります。(図7-5.)

図7-5.RIGHT OUTER JOINの内容
RIGHT OUTER JOINの内容

USINGによる結合

なお、これらの結合の条件は、内部結合と同様、列名が共通の場合は、USINGを使用することができます。したがって、Sample702.sql、Sample703.sqlは、それぞれ以下の通りに書いてもかまいません。

Sample704.sql(Sample702.sqlと同じもの)
SELECT * FROM purchase_history LEFT OUTER JOIN resource USING(code);

実行結果は、以下の通りになります。(図7-6.)

図7-6.HAVINGによるLEFT OUTER JOINの内容
HAVINGによるLEFT OUTER JOINの内容
Sample705.sql(Sample703.sqlと同じもの)
SELECT * FROM purchase_history RIGHT OUTER JOIN resource USING(code);

実行結果は、以下の通りになります。(図7-7.)

図7-7.HAVINGによるRIGHT OUTER JOINの内容
HAVINGによるRIGHT OUTER JOINの内容

内部結合の場合と同様、共通のカラムになる「code」が先頭に来て、それ以外のそれぞれのテーブルのカラムが表示されていることがわかります。出力結果は異なりますが、内容はONで接続した場合と変わりません。

内部結合との違い

では、外部結合と内部結合には、一体どのような違いがあるのでしょうか。参考のために、同じテーブル同士を、内部結合で結合させてみましょう。

Sample706.sql
SELECT * FROM purchase_history INNER JOIN resource USING(code);

実行結果は、以下の折になります。(図7-8.)

図7-8.purchase_historyテーブルとresourceテーブルの内部結合
purchase_historyテーブルとresourceテーブルの内部結合

実行結果からわかる通り、共通していないcodeの部分である、「100C02」、「100202」、「100C01」のある行は出力されていません。つまり、両方のテーブルに共通するcodeのある行しか出力されていないことがわかります。

つまり、内部結合が、共通部分があるものしか結合しないのに対して、外部結合は、共通部分がない行も結合し、該当するデータがないカラムには、NULLを入れて出力するという違いがあります。

3つ以上のテーブルの結合

複数の結合の利用

ここまで、内部結合、外部結合、交差結合といった、さまざまな結合について説明してきましたが、これらを複数回用いて3つ以上のテーブルを結合することも可能です。ここでは、その例をいくつか見てみましょう。

Sample707.sql
SELECT date,code,resource.name,class_name.name,num,price 
FROM purchase_history INNER JOIN resource USING(code) 
INNER JOIN class_name USING(class);

この実行結果は、以下の通りになります。(図7-9.)

図7-9.3つのテーブルの内部結合
3つのテーブルの内部結合

この結合は、以下のようなプロセスを経て行われます。(図7-10.)まず、purchase_historyとresourceの結合を行い、その結果得られた検索結果に、さらにclass_nameを結合させます。

図7-10.複数のテーブルの結合の手順
複数のテーブルの結合の手順

これ以上の数の組み合わせでも、基本的に考え方は一緒です。また、内部結合、交差結合、外部結合といった違った種類の結合でも同じ手順になります。


練習問題 : 問題6.