外部結合

データベース

期末試験の得点を格納するテーブルを以下のように定義します。

SQL
CREATE TABLE score(
    student_id  char(4),
    subject_id  char(3),
    score        INT,
    PRIMARY KEY(student_id,subject_id),
    FOREIGN KEY(student_id,subject_id) 
       REFERENCES registration_list(student_id,subject_id)
);

レコードを以下のように挿入します。
新たな科目『Javaプログラミング（suject_id=’D01’）』について、
4人の学生全員がそれを履修している
学生の高橋君（student_id=’1004’）は試験を病欠してしまったために成績のレコードがない
という設定です。

SQL
INSERT INTO subject_list(subject_id,subject_name,teacher_id)
VALUES('D01','Javaプログラミング','T01');

INSERT INTO registration_list(subject_id,student_id)
VALUES('D01','1001'),('D01','1002'),('D01','1003'),('D01','1004');

INSERT INTO score(student_id,subject_id,score)
VALUES('1001','D01',70),('1002','D01',50),('1003','D01',80);

では、必要なテーブルを結合して、subject_id=’D01’についての成績を出力するSQLを考えてみましょう。
NATURAL JOIN句とWHERE句を使って

SQL
SELECT subject_id,subject_name,student_id,student_name,score FROM score
    NATURAL JOIN subject_list
    NATURAL JOIN student_list
    WHERE subject_id='D01';

とするか、またはWHERE句だけで

SQL
SELECT subject_list.subject_id,subject_name,
       student_list.student_id,student_name,score 
    FROM score,subject_list,student_list
    WHERE score.student_id=student_list.student_id AND
          score.subject_id=subject_list.subject_id AND
          score.subject_id='D01';

とすればよさそうに思えます。
これらのSQLの結果は

となります。
一見これは何の問題もないように思えますが、たとえば

『再試験の実施対象となる学生を抽出する』

という用途だったらどうでしょうか？
『再試験対象となるのは60点未満または未受験』だとすると、未受験者が表示されないのは不便です。

このようなときに使うのが『外部結合』です。
等結合や自然結合では、2つのテーブルから対応するカラムの値の等しいもの同士を結合しましたが、外部結合では基準となるテーブルのレコードそれぞれに対し、条件に合う他のテーブルのレコードを結合します。
基準となるテーぶルのレコードは、対応する他のテーブルのレコードがなくても抽出されます。このとき、結果として表示される他のテーブルのカラムの部分はNULLとなります。

外部結合の構文は以下の通りです。

構文1

SELECT カラム FROM テーブル1 LEFT OUTER JOIN テーブル2 ON 結合条件

構文2

SELECT カラム FROM テーブル1 RIGHT OUTER JOIN テーブル2 ON 結合条件

構文1ではテーブル1が基準、構文2ではテーブル2が基準となります。

簡単な例として【student_list】と【score】を結合することを考えます。
まず自然結合ではこのようになります。

SQL
SELECT score.subject_id,
       student_list.student_id,student_list.student_name,score.score 
    FROM student_list
    NATURAL JOIN score;

やはり高橋君（student_id=’1004’）は表示されません。
外部結合では以下のようになります。

SQL
SELECT score.subject_id,
       student_list.student_id,student_list.student_name,score.score 
    FROM student_list
    LEFT OUTER JOIN score ON score.student_id=student_list.student_id;

高橋君（student_id=’1004’）のレコードが出力されています。
ただし、【score】テーブルに対応するレコードがないため、【score】テーブルのカラムである『subject_id』と『score』の値はNULLになっています。

【student_list】を基準とし、そのすべてのレコードについて、それぞれ【score】テーブルに対応するレコードがあれば結合しているのです。

では、必要なテーブルを結合して、subject_id=’D01’について『subject_id』『subject_name』『student_id』『student_name』『score』を表示するSQLを出す方法について考えてみましょう。
今度は『学生すべて』ではなく『＜履修している＞学生すべて』なので、履修状況を表す【registration_list】を基準とします。【student_list】および【subject_list】を自然結合し、さらに【score】を外部結合します。

SQL
SELECT registration_list.subject_id,subject_list.subject_name,
       student_list.student_id,student_list.student_name,score.score 
    FROM registration_list
    NATURAL JOIN student_list
    NATURAL JOIN subject_list
    LEFT OUTER JOIN score ON score.student_id=registration_list.student_id AND
                             score.subject_id=registration_list.subject_id
    WHERE registration_list.subject_id='D01';

または、基準となる【registation_list】以外すべてを外部結合として記述します。

SQL
ELECT registration_list.subject_id,subject_list.subject_name,
       student_list.student_id,student_list.student_name,score.score 
    FROM registration_list
    LEFT OUTER JOIN student_list ON student_list.student_id=registration_list.student_id
    LEFT OUTER JOIN subject_list ON subject_list.subject_id=registration_list.subject_id
    LEFT OUTER JOIN score ON score.student_id=registration_list.student_id AND
                             score.subject_id=registration_list.subject_id
    WHERE registration_list.subject_id='D01';

『subject_id』カラムや『student_id』カラムは【score】テーブルにもありますが、【score】テーブルにはsubject_id=’D01’に対応するレコードがないため、subject_idやstudent_idまでNULLと表示されてしまいます。

『再試験対象者』を抽出するには、再試験となる条件を追加します。
『得点60点未満または未受験者』なら『score.scoreが60より小さいか、またはNULL』なので、
『 score.score < 60 』または『 score.score IS NULL 』となります。

よって、

SQL
SELECT registration_list.subject_id,subject_list.subject_name,
       student_list.student_id,student_list.student_name,score.score 
    FROM registration_list
    NATURAL JOIN student_list
    NATURAL JOIN subject_list
    LEFT OUTER JOIN score ON score.student_id=registration_list.student_id AND
                             score.subject_id=registration_list.subject_id
    WHERE registration_list.subject_id='D01' AND
          (score.score<60  OR  score.score IS NULL);

または、

SQL
SELECT registration_list.subject_id,subject_list.subject_name,
       student_list.student_id,student_list.student_name,score.score 
    FROM registration_list
    LEFT OUTER JOIN student_list ON student_list.student_id=registration_list.student_id
    LEFT OUTER JOIN subject_list ON subject_list.subject_id=registration_list.subject_id
    LEFT OUTER JOIN score ON score.student_id=registration_list.student_id AND
                             score.subject_id=registration_list.subject_id
    WHERE registration_list.subject_id='D01' AND
          (score.score<60  OR  score.score IS NULL);

コメント

タイトルとURLをコピーしました