テーブル結合

本章では、実際に複数のテーブルを連携させるテーブル結合のSQLについて解説します。
本章の目標
  • 内部結合の概念を理解する
  • 内部結合の使い方をおぼえる
  • 外部結合の概念を理解する
  • 外部結合の使い方をおぼえる
ページの先頭へ
ページの先頭へ

1.テーブル結合の基本

まずは基本的なテーブル結合の考え方と使用方法を説明します。

1. 内部結合

前章ではリレーショナルデータベースと外部キー制約の概念を解説しましたが、テーブルを分割しただけで実際にデータとして連携して使用できなければ意味がありません。
このような場合、SQLを使ってテーブル同士を結合し、擬似的にひとつの表のようにして取り出すことができます。
これを表(テーブル)の結合(またはジョイン)といいます。

「bookinfo」と「orderinfo」を例に説明します。前章では表の正規化に触れましたが、テーブルの結合はこの逆をイメージすると分かりやすいでしょう。
現在「bookinfo」と「orderinfo」はそれぞれのisbn列で関連付けることができます。
つまり「bookinfo」のisbn列と「orderinfo」のisbn列で、同じデータを持つもの同士を結合することができるわけです。 そして結合されたテーブルを、あたかもひとつのテーブルのように扱えるのです。


テーブルの結合にはSELECT文を使います。
書式は下記のようになります。

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

「FROM」句で連携させたいテーブル名1とテーブル名2の名前を「INNER JOIN」で結びます。
その後「ON」に続けて、テーブル1のどの列とテーブル2のどの列を基準に結合するかを指定します。
「SELECT」句では、表示させたいテーブルの列名を、「テーブル名.列名」という記述で表現します。
「bookinfo」と「orderinfo」を結合して表示してみましょう。


まず、両方のテーブルをisbn列を基準に結合し、全列を表示してみます。

SELECT * FROM bookinfo INNER JOIN orderinfo ON bookinfo.isbn=orderinfo.isbn;



両方のテーブルのデータが結合され、「bookinfo」テーブルのデータが左側に、「orderinfo」テーブルのデータが右側に表示されます。
このように、「SELECT」句に列を指定しない場合、左側に「INNER JOIN」で指定したはじめのテーブルの内容が表示され、右側に2番目に指定したテーブルの内容が表示されます。

では、下の表のようなイメージ(列の並び順を変更)で、データを取得してみましょう。

ordernouserisbn titlepricequantity date
1kanda00001 SQL入門テキスト10501 2010-07-01
5kanda00002 SQL基礎テキスト23001 2010-08-22

これには、「SELECT」句で列の並びを指定する必要があります。

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

表記のように「テーブル名.列名」のように表記します。
isbn列は「orderinfo」にも「bookinfo」にもありますが、今回の場合はどちらの列を使っても同じデータが取得
できるので、どちらを指定しても同じです。例では「bookinfo」のisbn列を使用しています。


この形が基本形ですが、テーブル名に別名をつけることで、SQL文をもう少し短くすることもできます。

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

この例では、「bookinfo」に「A」、「orderinfo」に「B」という別名を付けています。


今回は「bookinfo」のisbn列と「orderinfo」のisbn列で、データが一致するものだけを結合しました。 このように、結合対象のテーブル同士の結合のキーとなる列で、データが同じものだけを結合することを特に「内部結合(もしくはINNER JOIN)」と呼びます。


テーブルを結合する場合の「SELECT」句では、「テーブル名.列名」という指定がルールになっていますが、片方のテーブルにしか存在しない列はテーブル名を省略することができます。
例えば「bookinfo」のtitle列とprice列、「orderinfo」のorderno列・user列・quantity列・date列は片方の列にしか存在しませんので、DDL文は下記のように書き換えることができます。

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



ページの先頭へ

2.表結合の発展

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

1. 学習の準備

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


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

isbn title price
00007C入門テキスト1050
00008C++入門テキスト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文字)
userVARCHAR(20)PRIMARY KEY
パスワード文字列
(最大半角20文字/全角10文字)
passwordVARCHAR(20)
メール
アドレス
文字列
(最大半角100文字/全角50文字)
emailVARCHAR(100)


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

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

userpasswordemail
akibaakibapassakiba@kanda-it-school.com
ebisuebisupassebisu@kanda-it-school.com
kandakandapasskanda@kanda-it-school.com
meguromeguropassmeguro@kanda-it-school.com
osakiosakipassosaki@kanda-it-school.com
shibuyashibuyapassshibuya@kanda-it-school.com
sugamosugamopasssugamo@kanda-it-school.com
tamachitamachipasstamachi@kanda-it-school.com
uenouenopassueno@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;

DROP TABLE orderinfo; * 「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');



2. 外部結合の考え方

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


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


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



3. 左外部結合

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

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

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

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」と表示されます。

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

4. 右外部結合

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

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

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

表(bookinfo)
ordernouserisbnquantitydate
3akiba0000112010-08-02
 
5kanda0000212010-08-22
8meguro0000342010-07-30
19osaki0000612010-08-20
10shibuya0000312010-08-21
 
22tamachi0000662010-09-30
30ueno0000212010-07-15

表(orderinfo)
userpasswordemail
akibaakibapassakiba@kanda-it-school.com
ebisuebisupassebisu@kanda-it-school.com
kandakandapasskanda@kanda-it-school.com
meguromeguropassmeguro@kanda-it-school.com
osakiosakipassosaki@kanda-it-school.com
shibuyashibuyapassshibuya@kanda-it-school.com
sugamosugamopasssugamo@kanda-it-school.com
tamachitamachipasstamachi@kanda-it-school.com
uenouenopassueno@kanda-it-school.com


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

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

ordernouserisbnquantitydatepasswordemail
3akiba0000112010-08-02akibapassakiba@kanda-it-school.com
 ebisupassebisu@kanda-it-school.com
5kanda0000212010-08-22kandapasskanda@kanda-it-school.com
8meguro0000342010-07-30meguropassmeguro@kanda-it-school.com
19osaki0000612010-08-20osakipassosaki@kanda-it-school.com
10shibuya0000312010-08-21shibuyapassshibuya@kanda-it-school.com
 sugamopasssugamo@kanda-it-school.com
22tamachi0000662010-09-30tamachipasstamachi@kanda-it-school.com
30ueno0000212010-07-15uenopassueno@kanda-it-school.com


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;




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


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冊以上のデータを抽出したい場合は、下記のようなDDL文になります。

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」にしかないデータを抽出したい場合、下記のようなDDL文で可能です。

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. 結合と算術演算子の組み合わせ

「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;




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;





テーブルの結合は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」で結合条件を指定します。それ以上のテーブルを結合する場合もこれの繰り返しになります。





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


ページの先頭へ

3.本章のまとめ

テーブルの結合は、リレーショナルデータベースを扱う上で最も重要な知識で、 これを知っておかないとリレーショナルデータベースを使用する意味が半減すると言っても過言ではないでしょう。
何度も復習して、考え方と使用方法をしっかりと身につけておいてください。

ページの先頭へ