超電磁砲でデータベース（複数テーブル 基礎知識編）

はじめに

複数テーブルの結合を学ぶ。動作確認のため次の２つのテーブル TEST1, TEST2を準備する。TEST1 は STATUS の簡易版、TEST2 は後に追加するテーブル AFF の簡易版である。

NAMELEVEL
御坂美琴5
上条当麻0
白井黒子4
TEST1
NAMETEAM
御坂美琴常盤台中
白井黒子常盤台中
白井黒子ジャッジメント
神裂火織天草式十字凄教
TEST2

この２つのテーブルを作成するために、次のコマンドを入力（コピペ）する。

CREATE TABLE TEST1 (
 NAME TEXT PRIMARY KEY,
 LEVEL INTEGER,
 CHECK( 0 <= LEVEL ),
 CHECK( 5 >= LEVEL )
) ;

INSERT INTO TEST1 VALUES ( '御坂美琴', 5 ) ;
INSERT INTO TEST1 VALUES ( '上条当麻', 0 ) ;
INSERT INTO TEST1 VALUES ( '白井黒子', 4 ) ;


CREATE TABLE TEST2 (
 NAME TEXT,
 TEAM TEXT,
 PRIMARY KEY ( NAME, TEAM ),
 FOREIGN KEY ( NAME ) REFERENCES STATUS
) ;

INSERT INTO TEST2 VALUES ( '御坂美琴', '常盤台中' ) ;
INSERT INTO TEST2 VALUES ( '白井黒子', '常盤台中' ) ;
INSERT INTO TEST2 VALUES ( '白井黒子', 'ジャッジメント' ) ;
INSERT INTO TEST2 VALUES ( '神裂火織', '天草式十字凄教' ) ;

直積

テーブル名に２つのテーブルを並べただけであるが、SELECT * FROM TEST1, TEST2 ; と入力してみよう。結果は次のようになる。

NAMELEVELNAMETEAM
御坂美琴5御坂美琴常盤台中
御坂美琴5白井黒子常盤台中
御坂美琴5白井黒子ジャッジメント
御坂美琴5神裂火織天草式十字凄教
上条当麻0御坂美琴常盤台中
上条当麻0白井黒子常盤台中
上条当麻0白井黒子ジャッジメント
上条当麻0神裂火織天草式十字凄教
白井黒子4御坂美琴常盤台中
白井黒子4白井黒子常盤台中
白井黒子4白井黒子ジャッジメント
白井黒子4神裂火織天草式十字凄教
(直積) SELECT * FROM TEST1, TEST2 ;

TEST1 が３データ、TEST2 が４データあり、それらを単純に組み合わせた１２データが表示された。このテーブルを TEST1 と TEST2 の直積または交差結合と言う。２列に現れている NAME の値が異なり、２つのデータの関連性が考慮されていない。このままでは利用価値は低そうで、意味のある行だけを抜き出す必要がある。

内部結合

２つのテーブルは属性 NAME を共有することで関連している。テーブルの関連付けのために WHERE 句を使った問い合わせ文を試してみる。これを旧形式の結合と言う。

SELECT * FROM TEST1, TEST2 WHERE TEST1.NAME = TEST2.NAME ;

WHERE 句内で NAME の指定はどちらのテーブルを意味するか分からないので、TEST1.NAME, TEST2.NAME のように指定する。

NAMELEVELNAMETEAM
御坂美琴5御坂美琴常盤台中
白井黒子4白井黒子常盤台中
白井黒子4白井黒子ジャッジメント
(旧形式) SELECT * FROM TEST1, TEST2 WHERE TEST1.NAME = TEST2.NAME ;

NAME が２つあるが、左が TEST1, 右が TEST2 に対応していると解するのが自然だろう。結果の特徴をまとめる。

  • 共通する属性が重複する。次のように使えば、特に支障はないだろう。
    SELECT TEST1.NAME, LEVEL, TEAM FROM TEST1, TEST2 WHERE TEST1.NAME = TEST2.NAME ;
  • TEST1 にあった「上条当麻」のデータは、対応するデータが TEST2 に無いので失われる。
  • TEST2 にあった「神裂火織」のデータは、対応するデータが TEST1 に無いので失われる。

これらは特徴であって、利用目的次第では問題はない。下の２つの性質が内部結合の特徴である。

NAME の重複が気になるのであれば、自然結合という方法がある。

SELECT * FROM TEST1 NATURAL JOIN TEST2 ;

NAMELEVELTEAM
御坂美琴5常盤台中
白井黒子4常盤台中
白井黒子4ジャッジメント
(自然結合) SELECT * FROM TEST1 NATURAL JOIN TEST2 ;

共通する属性を検出してまとめられているが、勝手に属性を判断されるのは危険かもしれない。結果的には旧形式と同じになるが、WHERE 句を使わない表現として内部結合がある。

SELECT * FROM TEST1 INNER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;

WHERE 句の代わりに ON で表現されただけに見えるが、長くなりがちな WHERE 句が簡潔になる利点はあるだろう。旧形式は内部結合の１つの表現と考えられる。

外部結合

内結合では相手に対応するデータがなければ、データは捨てられていた。対応するデータがない場合に相手を NULL にして保存するのが外部結合である。左側のテーブルを保存する場合を左外部結合、右側を保存する場合を右外部結合、両方残す場合を完全外部結合という。

  • 左外部結合
    SELECT * FROM TEST1 LEFT OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;
  • 右外部結合
    SELECT * FROM TEST1 RIGHT OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;
  • 完全外部結合
    SELECT * FROM TEST1 FULL OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;

完全害結合は MySQL では使えないとか、ソフトウェアによって利用できないものがあるらしい。各外部結合の結果を提示する。対応するデータがない場合は空欄になっていることが分かる。空欄は NULL として扱われる。

NAMELEVELNAMETEAM
御坂美琴5御坂美琴常盤台中
上条当麻0
白井黒子4白井黒子ジャッジメント
白井黒子4白井黒子常盤台中
(左外部結合) SELECT * FROM TEST1 LEFT OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;
NAMELEVELNAMETEAM
御坂美琴5御坂美琴常盤台中
白井黒子4白井黒子ジャッジメント
白井黒子4白井黒子常盤台中
神裂火織天草式十字凄教
(右外部結合) SELECT * FROM TEST1 RIGHT OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;
NAMELEVELNAMETEAM
御坂美琴5御坂美琴常盤台中
上条当麻0
白井黒子4白井黒子ジャッジメント
白井黒子4白井黒子常盤台中
神裂火織天草式十字凄教
(完全外部結合) SELECT * FROM TEST1 FULL OUTER JOIN TEST2 ON TEST1.NAME = TEST2.NAME ;