6.2 表結合の発展

ここでは、テーブル結合の発展的な使い方として、外部結合について解説します。

6.2.1 学習の準備

まずは、外部結合を学習するための準備をしましょう。

復習

* 「bookinfo」テーブルへの新規データ登録
「bookinfo」テーブルに下記のデータを登録してください。

isbn title price
00007 C入門テキスト 1050
00008 C++入門テキスト 2100

INSERT INTO bookinfo(isbn,title,price) VALUES('00007','C入門テキスト',1050);
INSERT INTO bookinfo(isbn,title,price) VALUES('00008','C++入門テキスト',2100);

* 新規テーブルの作成
下表のテーブル「userinfo」を作成してください。

テーブル名「userinfo」

項目 データのタイプ 列名 データ型 属性
ユーザー名 文字列（最大半角20文字/全角10文字） user VARCHAR（20） PRIMARY KEY
パスワード 文字列（最大半角20文字/全角10文字） password VARCHAR（20）  
メールアドレス 文字列（最大半角100文字/全角50文字） email VARCHAR（100）  

CREATE TABLE userinfo (
user VARCHAR(20) PRIMARY KEY,
password VARCHAR(20),
email VARCHAR(100)
)ENGINE=InnoDB;

* 新規テーブルへのデータ登録
「userinfo」下記のデータを登録してください。

user password email
akiba akibapass akiba@kanda-it-school.com
ebisu ebisupass ebisu@kanda-it-school.com
kanda kandapass kanda@kanda-it-school.com
meguro meguropass meguro@kanda-it-school.com
osaki osakipass osaki@kanda-it-school.com
shibuya shibuyapass shibuya@kanda-it-school.com
sugamo sugamopass sugamo@kanda-it-school.com
tamachi tamachipass tamachi@kanda-it-school.com
ueno uenopass ueno@kanda-it-school.com

INSERT INTO userinfo(user,password,email) VALUES('akiba','akibapass','akiba@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('ebisu','ebisupass','ebisu@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('kanda','kandapass','kanda@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('meguro','meguropass','meguro@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('osaki','osakipass','osaki@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('shibuya','shibuyapass','shibuya@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('sugamo','sugamopass','sugamo@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('tamachi','tamachipass','tamachi@kanda-it-school.com');
INSERT INTO userinfo(user,password,email) VALUES('ueno','uenopass','ueno@kanda-it-school.com');

* データの確認
データを確認しておきましょう。

SELECT * FROM userinfo;

* 「orderinfo」テーブルの再作成
「orderinfo」テーブルに「userinfo」テーブルへの外部キー制約を設定するためにデータとテーブルを再作成します。外部キー制約は「orderinfo」テーブルのuser列に設定し、参照先は「userinfo」テーブルのuser列とします。外部キー制約には、「ON UPDATE CASCADE」オプションと「ON DELETE CASCADE」オプションをつけてください。

DROP TABLE orderinfo;
CREATE TABLE orderinfo (
orderno INTEGER AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(20),
isbn VARCHAR(20),
quantity INTEGER,
date date,
FOREIGN KEY (isbn) REFERENCES bookinfo(isbn) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (user) REFERENCES userinfo(user) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;

* 「orderinfo」テーブルのへのデータ再登録
「orderinfo」のデータを再登録しておきましょう。

INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00001',1,'2010-07-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00001',2,'2010-07-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00001',1,'2010-08-02');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00001',3,'2010-07-17');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-08-22');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00002',3,'2010-09-03');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00002',1,'2010-07-25');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00003',4,'2010-07-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00003',1,'2010-08-12');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00003',1,'2010-08-21');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00004',2,'2010-09-14');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00004',3,'2010-07-11');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00004',6,'2010-07-19');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00004',1,'2010-08-19');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00005',4,'2010-09-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00005',2,'2010-08-22');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00005',1,'2010-07-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00006',1,'2010-07-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('osaki','00006',1,'2010-08-20');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00006',3,'2010-07-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00006',2,'2010-09-01');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('tamachi','00006',6,'2010-09-30');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',1,'2010-07-11');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('shibuya','00007',3,'2010-07-21');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('kanda','00007',2,'2010-08-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('meguro','00007',1,'2010-07-02');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('akiba','00007',4,'2010-07-25');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00007',1,'2010-08-15');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00001',1,'2010-08-14');
INSERT INTO orderinfo(user,isbn,quantity,date) VALUES('ueno','00002',1,'2010-07-15');

6.2.2 外部結合の考え方

前節では、内部結合について解説しました。内部結合の考え方は、結合キーとなる列に同じデータがある場合だけデータを抽出するというものでした。これに対して外部結合は、内部結合に加えて、片方のテーブルにしかないデータも取得することができます。

「bookinfo」と「orderinfo」の関係で見ていくと、先程「bookinfo」にISBN番号「00008」のデータを登録しましたが、「orderinfo」にはISBN番号「00008」の注文データが存在しません。
これを内部結合で結合すると、「bookinfo」のISBN番号「00008」のデータは抽出されません。

しかし外部結合を使うと、「bookinfo」のISBN番号「00008」のデータも取得できます。

6.2.3 左外部結合

まずは左外部結合の書式を見てみましょう。

SELECT テーブル名1もしくは2.列名,・・・ FROM テーブル名1 LEFT JOIN テーブル名2 ON テーブル名1.列名1 = テーブル名2.列名2;

書式は内部結合と似ていますが、「INNER JOIN」が「LEFT JOIN」に変わっています。このように指定すると、はじめに指定したテーブルの全データと2番目に指定したテーブルの結合キーで結合できるデータが取得されます。このような外部結合を「左外部結合」と呼びます。
前節の「bookinfo」と「orderinfo」の例で見ると、DML文は下記のようになります。

SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date FROM bookinfo A LEFT JOIN orderinfo B ON A.isbn=B.isbn;

この場合は、「bookinfo」の全データと、「orderinfo」のisbn列のデータが「bookinfo」のisbn列に含まれるデータを取得できます。

※外部結合で片方のテーブルにしかデータが存在しない場合、もう片方のテーブルのデータは、データ自体がないので「NULL」と表示されます。

内部結合では注文のあるデータしか表示されませんでしたが、外部結合を使うと注文のないデータもチェックできるようになります。

6.2.4 右外部結合

右外部結合は、左外部結合の逆です。まず右外部結合の書式を見てみます。

　　　SELECT テーブル名1もしくは2.列名,・・・ FROM テーブル名1 RIGHT JOIN テーブル名2
　　　ON テーブル名1.列名1 = テーブル名2.列名2;

2番目に指定したテーブルの全データとはじめに指定したテーブルの結合キーで結合できるデータが取得されます。
今度は「orderinfo」と「userinfo」を例にとって説明していきます。「orderinfo」と「userinfo」はそれぞれのuser列で結合することができますが、「userinfo」のユーザー名「ebisu」と「sugamo」の注文データは「orderinfo」にはありません。

※簡略のために、データを一部だけ表示しています。

この2つのテーブルを、下表のように「userinfo」のユーザー名「ebisu」と「sugamo」のデータを含めて取得したい場合に右外部結合を使います。

SELECT A.orderno,A.user,A.isbn,A.quantity,A.date,B.password,B.email
FROM orderinfo A RIGHT JOIN userinfo B ON A.user=B.user;

左外部結合と右外部結合はどちらのテーブルを中心に取り出すかだけで本質的な違いはありません。できるだけ統一して使うほうが、混乱が少ないでしょう。

6.2.5 結合と「WHERE」句の組み合わせ

テーブルを結合した場合でも、WHERE句で抽出条件を付け加えることができます。書式は下記の通りです。

　　　SELECT テーブル名1もしくは2.列名,・・・
　　　FROM テーブル名1 INNER JOIN または LEFT JOIN　または RIGHT JOIN テーブル名2
　　　ON テーブル名1.列名1 = テーブル名2.列名2
　　　WHERE テーブル名.列名 演算子 値;

例えば「bookinfo」と「orderinfo」を結合し、「orderinfo」の注文数が3冊以上のデータを抽出したい場合は、下記のようなDML文になります。

SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
WHERE B.quantity >=3;

また外部結合時に「IS NULL」演算子を使うと、片方のテーブルにしかないデータを特定できるような便利な使い方ができます。「bookinfo」と「orderinfo」で、「bookinfo」にしかないデータを抽出したい場合、下記のようなDML文で可能です。

SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A LEFT JOIN orderinfo B ON A.isbn=B.isbn
WHERE B.isbn IS NULL;

これは、外部結合時に片方のテーブルにしかデータが存在しない場合、もう片方のテーブルのデータは、データ自体がないので「NULL」になるためです。

6.2.6 結合と算術演算子の組み合わせ

「WHERE」句と同じように、テーブルを結合した場合でも算術演算子が利用できます。
「bookinfo」と「orderinfo」を結合して「bookinfo」の価格と「orderinfo」の注文数を乗算し、注文金額を取得することもできます。

SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date,A.price*B.quantity
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn;

6.2.7 結合と「ORDER BY」句の組み合わせ

今度は結合したデータの並べ替えをしてみましょう。基本書式は下記の通りです。

　　　SELECT テーブル名1もしくは2.列名,・・・
　　　FROM テーブル名1 INNER JOIN または LEFT JOIN　または RIGHT JOIN テーブル名2
　　　ON テーブル名1.列名1 = テーブル名2.列名2
　　　ORDER BY テーブル名.列名

「bookinfo」と「orderinfo」を結合し、「orderinfo」の注文番号順に並び替えて見ましょう。

SELECT A.isbn,A.title,A.price,B.orderno,B.user,B.quantity,B.date
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
ORDER BY B.orderno;

TIPS

テーブルの結合は2つだけに限定されるわけではありません。3つ以上のテーブルを結合することもできます。「bookinfo」テーブル、「orderinfo」テーブル、「userinfo」テーブルの3テーブルを結合する場合を見てみましょう。

SELECT * FROM
bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
INNER JOIN userinfo C ON B.user=C.user
;

上記のように2番目のテーブル名に続けて「INNER JOIN」（「LEFT JOIN」「RIGHT JOIN」も可）を指定し、 さらに結合したいテーブル名と「ON」で結合条件を指定します。それ以上のテーブルを結合する場合もこれの繰り返しになります。

TIPS

テーブルの結合はいままで解説した結合方法の他にも全結合、自己結合、非等価結合、クロス結合等様々な結合方法があります。
これらの結合方法は、本テキストの範囲を超えるため解説しませんが、興味のある方は専門の書籍等を参照してください。

NEXT>> 6.3 本章のまとめ