5.テーブルの結合

前章では、データの登録された一つのテーブルからデータを参照する方法について説明しました。 ここでは、複数のテーブルの間に定義されたリレーションシップをもとに、複数のテーブルを結合してデータを引き出すことを説明します。テーブルの結合とは、実際に新しいテーブルを作成するわけではなく、既存のテーブル間のリレーションシップから、データをよりわかりやすい状態で参照できるようにすることをいいます。

この章では次の 3つのテーブルを想定して説明を進めます。用意したテーブルは受注表、顧客表及び商品表です。テーブル定義もデータ入力も終わっています。

5.1. 基本的な結合

最も基本的な結合は自然結合と呼ばれるもので、データベース構造に既に存在しているリレーションシップを表現するところからきています。ちなみに受注表、顧客表及び商品表のリレーションは次の図のようになっています。

受注表、顧客表及び商品表のリレーション 受注表からは受注番号に対応する顧客コードと商品コードを知ることができます。そして、これらの列は外部キーとなっているため、リンクされる顧客表と商品表からコードに対する名称を知ることが可能です。よって、受注番号、顧客名、商品名、受注個数及び納品日の列を持つ表を、表の結合により作成することができるのです。ちなみに、受注表における受注番号、顧客表における顧客コード及び商品表における商品コードは各テーブルの主キーです。

図のようにテーブルを結合するには、これまでのテーブルのデータを参照する時と同様に SELECT 文を用います。SELECT 文に続けて、表示する列名を指定し、FROM のあとに指定した列の属するテーブル名を指定します。また、どのテーブルのどの列で結合するのかも条件として指定します。図の例の場合 sample-17-1 ように SQL を記述します。 sample-05-1:

1
2
3
4
5
6
/* テーブルの結合 */
SELECT 受注表.受注番号,顧客表.顧客名,
       商品表.商品名,受注表.受注個数,受注表.納品日
FROM 受注表,顧客表,商品表
WHERE 受注表.顧客コード = 顧客表.顧客コード
AND 受注表.商品コード = 商品表.商品コード ;

実行結果

sample-05-1 では、列の指定の仕方がこれまでの説明と異なっています。テーブルの結合では、複数のテーブルから列を参照するため、列名を指定する際には列が属するテーブル名も明記しなければなりません。

列名の指定　→　属するテーブル名.列名

これまで列の属するテーブルを明記してこなかったのは、参照する列が FROM 句で指定したテーブルに属しているのが明らかだったからです。ですから、FROM 句で複数のテーブルを参照する場合には、どのテーブルのどの列なのかということを明記しなければならないのです。しかし、全ての列名が異なる場合、たとえば受注表の顧客コードが顧客番号、受注表の商品コードが商品番号という名前であるような場合は、テーブル名を明記する必要はありません。しかし、一般的にわかりやすさという観点から、きちんとテーブル名を明記することがよいとされているようです。

SQL の基本的な結合作業は、結合されたテーブルを取り出して、考えられる限りの行の組み合わせを調べ、結合された 2つ以上のテーブルから 1 行ずつ取り出すというものです。この操作は、デカルト積と呼ばれています。

先ほどのサンプルでは、DBMS はまず受注表の顧客コードと顧客コードを照らし合わせて、その組み合わせが TRUE を形成する時、すなわちその値が一致する時、その組み合わせから出力する値を選択します。同時に受注表の商品コードと商品表の商品コードを照らし合わせて、その値が一致する時、出力する値を選択します。そして、SELECT に続く列名についてのデータを表示します。

このように、外部キーを親キーに結び付けている結合を自然結合と言います。しかし、SQL では設計されたリレーションシップによるデータの抽出だけでなく、データの内容に基づくリレーションシップによるデータの抽出も行えます。たとえば、納品日が '20010401' の日に納品する商品名を抽出するには次のように SQL を書きます。

sample-05-2:

1
2
3
4
/* テーブルの結合 */
SELECT 受注表.納品日,商品表.商品名
FROM 受注表,商品表
WHERE 受注表.納品日 = '20010401' AND 受注表.商品コード = 商品表.商品コード ;

実行結果

'BEER' が二つ表示されますが、SELECT の後に「DISTINCT」を置けば、重複するものを表示させないようにできます。

sample-05-3:

1
2
3
4
/* テーブルの結合 */
SELECT DISTINCT 受注表.納品日,商品表.商品名
FROM 受注表,商品表
WHERE 受注表.納品日 = '20010401' AND 受注表.商品コード = 商品表.商品コード ;

実行結果

5.2. 自己結合

自己結合とは同じテーブル同士の結合のことをいいます。結合は 2つ以上のテーブルに対して行われますが、それらが別のテーブルである必要はありません。自己結合は 2つのまったく同じテーブルの結合として実行されます。

テーブルの自己結合を行う際に問題なのは、テーブルの名前も列の名前も全て同じだという点です。これを解決するために、テーブルに一時的な名前を付けます。この一時的な名前のことをエイリアスといいます。範囲変数または相関変数とも呼びます。sample-05-4 は、商品表の単価が同じである商品名と単価を表示するものです。

sample-05-4:

1
2
3
4
/* テーブルの結合 */
SELECT A.商品名,A.単価 
FROM 商品表 A,商品表 B
WHERE A.単価 = B.単価 AND A.商品コード <> B.商品コード ;

実行結果

sample-05-5:

1
2
3
4
/* テーブルの結合 */
SELECT A.商品名,A.単価 
FROM 商品表 A,商品表 B
WHERE A.単価 = B.単価 ;

実行結果

自己結合の最も一般的な用途は循環リレーションシップへの対応といわれています。自分のテーブルに存在する列を外部キーとして指定するような、テーブルに循環型の外部キーが含まれる場合があるときに、自己結合はよく用いられます。

ここで説明用のテーブルを一つ定義します。次の従業員表では、上司列が従業員ナンバーを参照する外部キーになっています。どの社員も上司を持っていて、上司自身も社員であるため、従業員表に定義されるというものです。社員と上司の組み合わせを参照する時は、 sample-05-6 のように SQL を記述します。

従業員表

sample-05-6:

1
2
3
4
/* テーブルの結合 */
SELECT A.従業員ナンバー,A.従業員名,B.従業員名
FROM 従業員表 A,従業員表 B
WHERE A.上司 = B.従業員ナンバー ;

実行結果

5.3. 特殊な結合演算子

SQL92 はある種の共通する結合操作を簡単に実行できるように、特別な構文を用意しています。SQL のビルトイン結合演算子には、「CROSS JOIN」、「NATURAL JOIN」、「指定結合」及び「UNION JOIN」があります。これらは次のように定義されています。

■ CROSS JOIN

結合述語のない結合に相当します。すなわち、ストレートなデカルト積です。

■ NATURAL JOIN 自然結合は一般に、外部キー値から親キー値への照合に基づいた結合を指します。ただし、SQL92 規格ではこの用語が少し違った意味で使用されています。SQL92 規格では、同じ名前を持つ 2つの列が親キーと外部キーであるかのように照合されます。つまり、そのデータベース設計が列名の一致は実際に親キーと外部キーをあらわすと取り決めている場合に、NATURAL JOIN 演算子を使用すべきだと、いう考え方です。

■ 指定結合

クエリに直接指定された条件に基づいた結合です。クエリの WHERE 句の代わりに、FROM 句で結合を行うための機能を提供します。

■ UNION JOIN

明示的または暗黙的に指定された述語を指定せず、照合やデカルト積の抽出は行われません。<テーブル A> のすべての行を取り込み、<テーブル B> の列に NULL を設定し、さらに <テーブル B> の行をすべて取り込み、<テーブル A> の列に NULL を設定した出力テーブルを生成します。

5.3.1.CROSS JOIN

CROSS JOINとは、結合テーブルの値の比較に基づいた選択を行わないデカルト積です。つまり、すべての行の組み合わせが使用されます。結合を実行し、結合述語を一切使用しない場合と効果は同じです。この結合のサンプルを考える時、出力結果が膨大な量になるので、ここでは簡略化のために次のような模式的なテーブルを考えます。

sample-05-7:

1
2
3
SELECT A.顧客コード,A.顧客名,
    B.商品コード,B.商品名,B.単価
FROM 顧客表 A CROSS JOIN 商品表 B;

実行結果

上記の例は、sample.05-8のように記述した場合と同じです。

sample-05-8: 

1
2
3
4
/* テーブルの結合 */
SELECT  A.顧客コード,A.顧客名,
        B.商品コード,B.商品名,B.単価
FROM 顧客表 A , 商品表 B ;

実行結果

この構文はあまり役に立つものではありませんが、読みやすさを改善するものであり、その目的のほとんどは明瞭化にあるといえます。

5.3.2. 自然結合

自然結合は次のカテゴリに分類されます。規格ではこれを結合の種類と呼んでいます。OUTER というキーワードはオプションで、影響は一切ありません。

●[NATURAL] [INNER] 結合

NATURAL JOIN の前後で指定されたテーブルの同じ名前のカラムの値を比較し、値が一致する行を出力します。同じ名前のカラムが複数ある場合など、明示的にどのカラムの値を指定する場合は、後に説明するON句を使用します。

sample-05-9:

1
2
3
/* NATURAL INNER 結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL JOIN 顧客表 ;

実行結果

●[NATURAL] LEFT [OUTER] 結合

基本的な構文は次の通りです。
<テーブルA> [NATURAL] LEFT [OUTER] JOIN <テーブル B> ;
<テーブル A> のすべての行と、<テーブル B> で一致が検出された行が含まれます。<テーブル B> で一致が検出されなかった行には NULL が設定されます。

顧客表が顧客コードの '002' を参照する場合、[NATURAL] LEFT [OUTER] 結合で受注表と顧客表を結合すると、 sample-05-10 のようになります。

このサンプルでは 第9章 サブクエリ で解説されているサブクエリが使われています。

sample-05-10:

1
2
3
4
/* [NATURAL] LEFT [OUTER] 結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL LEFT OUTER JOIN 顧客表
WHERE 受注表.納品日 ='20010403' ;

実行結果

●[NATURAL] RIGHT [OUTER] 結合

注：SQLiteで実行することができません
基本的な構文は次の通りです。 <テーブルA> [NATURAL] RIGHT [OUTER] JOIN <テーブル B> ; LEFT OUTER 結合の反対です。<テーブル B> のすべての行と、<テーブル A> で一致が検出された行が含まれます。<テーブル A> で一致が検出されなかった行には NULL が設定されます。 顧客コード = '005'、顧客名 = 'DAIKISS' の新しい顧客を顧客表に追加します。現時点では受注表に顧客コードが '005' のデータはありませんが、受注表と顧客表を [NATURAL] RIGHT [OUTER] で結合してみます。 sample-05-11 にその例を示します。

sample-05-11:

1
2
3
/* [NATURAL] RIGHT [OUTER] 結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL RIGHT OUTER JOIN 顧客表 ;

●[NATURAL] FULL [OUTER] 結合

注：SQLiteで実行することができません
LEFT OUTER と RIGHT OUTER の組み合わせです。両方のテーブルからすべての行を追加し、一致する行を含み、一致しないものに NULL を設定します。構文は次の通りです。 <テーブルA> [NATURAL] FULL [OUTER] JOIN <テーブルB> ; [NATURAL] RIGHT [OUTER] 結合と同様に、顧客コード = '005'、顧客名 = 'DAIKISS' の新しい顧客を顧客表に追加し、受注表には、受注番号 = '10013'、商品コード = '104'、受注個数 = '50'、納品日 = '20010405' のデータを入力するとします。この時点では顧客コードは NULL ということにしておきます。この時の受注表と顧客表を [NATURAL] FULL [OUTER] で結合してみます。 sample-05-12 にその例を示します。

sample-05-12:

1
2
3
/* [NATURAL] FULL [OUTER] 結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL FULL OUTER JOIN 顧客表 ;

5.3.3. 指定結合

指定結合とは、結合を行う方法を指定する述語を作成する ON 形式か、等価結合を行う列集合を列挙する USING 形式という方法で、テーブルを結合する方法を直接指定する結合のことです。

sample-05-13 に ON 句を用いて、受注表と顧客表を指定結合した例を示します。

sample-05-13:

1
2
3
4
/* 指定結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
ON 受注表.顧客コード = 顧客表.顧客コード ;

実行結果

sample-05-13 はこれまでに説明してきたように、ON 句を用いた指定結合ではなく WHERE 句を用いて sample-05-14 のように記述できます。

sample-05-14:

1
2
3
4
/* 自然結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
WHERE 受注表.顧客コード = 顧客表.顧客コード ;

実行結果

ON 句と WHERE 句は同時に使用することが可能です。次の sample-05-15: のように、結合述語は ON 句に配置し、WHERE 句は述語のフィルタリングに使用すれば、WHERE 句だけで記述した場合よりもすっきりとします。

sample-05-15:

1
2
3
4
5
/* 指定結合 */
SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
ON  受注表.顧客コード = 顧客表.顧客コード
WHERE 顧客表.顧客名 = 'ONSIDE';

実行結果

USING 形式は、引数としてカンマで区切られた列名リストを取り、同じ名前を持つ列のすべての組みに等価結合を実行し、AND を使って結果を組み合わせます。つまり、同じ名前を持つ列をすべて利用して結合するのではなく、選択したものだけを使用してテーブルを結合します。 sample-05-16 に USING 形式を用いた場合の例を示します。

sample-05-16:

1
2
3
4
5
/* 指定結合 */
SELECT 受注表.受注番号,商品表.商品名
FROM 受注表,商品表
USING(商品コード)
WHERE 商品表.単価 >= 500;

実行結果

sample-05-16 の場合、本当は USING 句を用いて結合の形式を指定する必要がありません。それは、受注表と商品表の列名が同じなのは、商品コード一つだけだからです。もし、二つある場合には明示的に指定しないと、期待したとおりのデータが参照できない場合があります。また同じ名前の列が二つ以上ある場合、USING (列名,列名,....) として複数の列を指定して結合させることもできます。 指定結合には、自然結合で説明した INNER JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN のカテゴリがあります。効果は自然結合の節で説明したのと同じです。指定結合の場合、[NATURAL] は記述しないところに注意が必要です。

5.3.4. UNION JOIN

注：SQLiteで実行することができません
UNION JOIN はデカルト積をベースとせず、述語を使用しない結合です。WHERE 句で述語を指定することはできますが、UNION JOIN は 2つのテーブルのデータを関連付けようとはしません。<テーブル A> と < テーブル B> の UNION JOIN は <テーブル A> の列値をすべて出力し、同じ行の <テーブル B> の列を NULL で埋めます。次に、<テーブル B>の列値をすべて出力し、同じ行の <テーブル A> の列を NULL で埋めます。 sample-05-17 に受注表と商品表の UNION JOIN の例を示します。利用価値はほとんどありません。

sample-05-17:

1
2
3
/* UNION 結合 */
SELECT 受注表.受注番号,商品表.商品名
FROM 受注表 UNION JOIN 商品表 ;

5.4 SQL文

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
DROP TABLE 受注表;
CREATE TABLE 受注表
(
    受注番号     CHAR(5),
    顧客コード   CHAR(4),
    商品コード   CHAR(4),
    受注個数     INTEGER,
    納品日       CHAR(8),
    PRIMARY KEY(受注番号),
    FOREIGN KEY(顧客コード) REFERENCES 顧客表(顧客コード),
    FOREIGN KEY(商品コード) REFERENCES 商品表(商品コード)
);


DROP TABLE 顧客表;
CREATE TABLE 顧客表
(
    顧客コード   CHAR(4),  
    顧客名       CHAR (20),  
    PRIMARY KEY(顧客コード)
);

DROP TABLE 商品表;
CREATE TABLE 商品表
(
    商品コード   CHAR(4),
    商品名       CHAR(20),
    単価         INTEGER,
    PRIMARY KEY(商品コード)
);

insert into 受注表 values('10001','001','102',300,'20010401');
insert into 受注表 values('10002','002','101',200,'20010401');
insert into 受注表 values('10003','004','101',150,'20010401');
insert into 受注表 values('10004','004','102',80,'20010402');
insert into 受注表 values('10005','002','104',110,'20010402');
insert into 受注表 values('10006','001','103',250,'20010402');
insert into 受注表 values('10007','003','102',45,'20010402');
insert into 受注表 values('10008','004','101',135,'20010403');
insert into 受注表 values('10009','003','103',65,'20010403');
insert into 受注表 values('10010','001','101',290,'20010403');
insert into 受注表 values('10011','003','103',60,'20010404');
insert into 受注表 values('10012','004','101',175,'20010404');

insert into 顧客表 values('001','KUROKIYA');
insert into 顧客表 values('002','ONSIDE');
insert into 顧客表 values('003','FIRST HOUSE');
insert into 顧客表 values('004','KUSHINANA');


insert into 商品表 values('101','BEER',500);
insert into 商品表 values('102','JUICE',400);
insert into 商品表 values('103','TEA',400);
insert into 商品表 values('104','WINE',650);


SELECT 受注表.受注番号,顧客表.顧客名,
       商品表.商品名,受注表.受注個数,受注表.納品日
FROM 受注表,顧客表,商品表
WHERE 受注表.顧客コード = 顧客表.顧客コード
AND 受注表.商品コード = 商品表.商品コード ;

SELECT 受注表.納品日,商品表.商品名
FROM 受注表,商品表
WHERE 受注表.納品日 = '20010401'
AND 受注表.商品コード = 商品表.商品コード ;  

SELECT DISTINCT 受注表.納品日,商品表.商品名
FROM 受注表,商品表
WHERE 受注表.納品日 = '20010401'
AND 受注表.商品コード = 商品表.商品コード ;  

SELECT A.商品名,A.単価  
FROM 商品表 A,商品表 B
WHERE A.単価 = B.単価
AND A.商品コード <> B.商品コード ;

SELECT A.商品名,A.単価  
FROM 商品表 A,商品表 B
WHERE A.単価 = B.単価 ;


CREATE TABLE 従業員表
(
  従業員ナンバー,  
  従業員名,
  上司
);

insert into 従業員表 values('135','INAGAKI','240');
insert into 従業員表 values('240','KIMURA',NULL);
insert into 従業員表 values('184','KUSANAGI','135');
insert into 従業員表 values('235','KATORI','240');

SELECT A.従業員ナンバー,A.従業員名,B.従業員名
FROM 従業員表 A,従業員表 B
WHERE A.上司 = B.従業員ナンバー ;

SELECT A.顧客コード,A.顧客名,B.商品コード,B.商品名,B.単価
FROM 顧客表 A CROSS JOIN 商品表 B;


SELECT  A.顧客コード,A.顧客名,
        B.商品コード,B.商品名,B.単価
FROM 顧客表 A , 商品表 B ;

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL JOIN 顧客表 ;

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL LEFT OUTER JOIN 顧客表
WHERE 受注表.納品日 ='20010403' ;

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表 NATURAL RIGHT OUTER JOIN 顧客表 ;

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
ON 受注表.顧客コード = 顧客表.顧客コード ;  

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
WHERE 受注表.顧客コード = 顧客表.顧客コード ;  

SELECT 受注表.受注番号,顧客表.顧客名
FROM 受注表,顧客表
ON  受注表.顧客コード = 顧客表.顧客コード
WHERE 顧客表.顧客名 = 'ONSIDE';  

SELECT 受注表.受注番号,商品表.商品名
FROM 受注表,商品表
USING(商品コード)
WHERE 商品表.単価 >= 500;