SQLにおける内部結合を用いた外部結合（join on や join usingを用いた外部結合）

Question

前提・実現したいこと

create table 学生(
学籍番号 varchar(3) not null,
氏名 varchar(12),
出身校 varchar(10),
primary key (学籍番号)
);

create table 修得単位(
学籍番号 varchar(3) not null,
学年 int,
修得単位数 int,
primary key (学籍番号, 学年)
);

create table 履修(
学籍番号 varchar(3) not null,
科目名 varchar(16),
単位数 int,
primary key (学籍番号, 科目名)
);

insert into 学生(学籍番号, 氏名, 出身校) values('501', '吉水　小百合', '情報学園');
insert into 学生(学籍番号, 氏名, 出身校) values('601', '木村　拓弥', '先端学院');
insert into 学生(学籍番号, 氏名, 出身校) values('602', '坂東　太郎', '実学高校');
insert into 学生(学籍番号, 氏名, 出身校) values('701', '大沢　たつお', '両道高校');
insert into 学生(学籍番号, 氏名, 出身校) values('702', '綾瀬　なつか', '情報学園');

insert into 修得単位(学籍番号, 学年, 修得単位数) values('501', 1, 48);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('501', 2, 40);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('501', 3, 38);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('601', 1, 39);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('601', 2, 44);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('701', 1, 37);
insert into 修得単位(学籍番号, 学年, 修得単位数) values('702', 1, 45);

insert into 履修(学籍番号, 科目名, 単位数) values('501', '卒業研究', 8);
insert into 履修(学籍番号, 科目名, 単位数) values('601', 'Webデザイン', 2);
insert into 履修(学籍番号, 科目名, 単位数) values('601', '画像処理', 4);
insert into 履修(学籍番号, 科目名, 単位数) values('601', 'CG表現', 2);
insert into 履修(学籍番号, 科目名, 単位数) values('603', 'データベース', 6);
insert into 履修(学籍番号, 科目名, 単位数) values('603', 'Webデザイン', 2);
insert into 履修(学籍番号, 科目名, 単位数) values('701', 'データベース', 6);
insert into 履修(学籍番号, 科目名, 単位数) values('701', '情報検索', 2);
insert into 履修(学籍番号, 科目名, 単位数) values('701', 'Java言語', 4);
insert into 履修(学籍番号, 科目名, 単位数) values('702', 'Java言語', 4);
insert into 履修(学籍番号, 科目名, 単位数) values('702', 'Webデザイン', 2);

-- select * from 学生;
-- select * from 修得単位;
-- select * from 履修;

上記SQLに対して、以下の問いに答えなさい
(1)「履修」テーブルから, 単位数が3以上である行を抽出するビューを作成する命令を示せ. ただし, ビューの名称は「長期間講義」とする.

(2)join on文を用いて, 「学生」テーブルと「履修」テーブルを左外部結合するための命令を示せ. ただし, 結合条件は「両テーブルの学籍番号が等しい」であるとする.

(3)join using文を用いて, 「学生」テーブルと「履修」テーブルを完全外部結合するための命令を示せ. ただし, 結合条件は「両テーブルの学籍番号が等しい」とする.

(2)、(3)が全く分かりません。外部結合にどのようにして内部結合を利用するのでしょうか？
内部結合を利用すれば、Null値は削られてしまうのではないですか？

Comments

[yambejp]

ご利用のSQLは間違いなくMySQLで間違いないですか？
完全外部結合サポートしていないと思うのですが・・

[Tomatomat]

MySQLで間違いないと思います。full outer joinではなくleft outer joinの結果とright outer joinの結果をunionするという解説はされたのですが、join usingを用いた方法を問われたため、やり方がわからず質問させていただきました。

[yambejp]

> left outer joinの結果とright outer joinの結果をunionする

結合するキーの一覧をつくってleft joinする方法が楽だと思います
（回答はそういう方法です）

Answer 1

(1)create view 長期間講義 as select * from 履修 where 単位数>=3

(2)select * from 学生 as t1 left join 履修 as t2 on t1.学籍番号=t2.学籍番号

(3)select * from (select 学籍番号 from 学生 union select 学籍番号 from 履修) as t1
left join 学生 using(学籍番号)
left join 履修 using(学籍番号)

Comments

[yambejp]

そもそもが履修テーブルの学籍番号で学生テーブルに存在しないという運用方法に
問題があると思います

[Tomatomat]

結局無理やり使うしかないということですね...
ありがとうございました。助かりました。