新人SEのためのSQL 外部結合とNULL
NULLがややこしいという記事を書きました。
単体でもNULLを扱う事はもちろんありますが、出会う場面は外部結合の際が多いように思います。
今回もOracleを使って説明します。
まず、以下のテーブルを準備します。
create table student (
id char(3) ,
name varchar2(20)
) ;
create table result (
id char(3) ,
season char(1),
score number(3)
) ;
insert into student values('001','山田');
insert into student values('002','田中');
insert into student values('003','鈴木');
insert into student values('004','佐藤');
insert into result values('001',1,100);
insert into result values('002',1,80);
insert into result values('003',1,70);
insert into result values('001',2,100);
insert into result values('002',2,85);
insert into result values('004',2,80);
commit;
以下の図の様なシチュエーションを想定します。
5人の生徒がいて、1学期2学期それぞれテストを受けました。
ただし、佐藤さんは1学期、鈴木さんは2学期のテストを、高橋さんは1、2学期共にテストを受けることができませんでした。
テーブル間の関係が1:nの場合
まず、70点以下(未受験含む)の人を追試対象として抽出してみます。
resultテーブルには受験しなかった場合にデータはありません。内部結合だと、未受験のデータが取れませんので、外部結合を使います。
/* 外部結合にしただけでは駄目な例 */
SELECT s.id,s.name,r.season,r.score
FROM student s
LEFT Join result r on s.id = r.id
WHERE r.score IS NULL OR r.score <= 70
ORDER BY 1,3
;
(結果)
ID NAME SE SCORE
----- --------- ------- -----------
003 鈴木 1 70
005 高橋
高橋さんは抽出されましたが、学期の情報がありません。また、1学期未受験の佐藤さんも出て来ませんし、鈴木さんも2学期未受験の情報が出て来ません。
これは、whereを外して検索すると分かりやすくなります。
(whereを外して実行した結果)
ID NAME SE SCORE
----- --------- ------- -----------
001 山田 1 100
001 山田 2 100
002 田中 2 85
002 田中 1 80
003 鈴木 1 70
004 佐藤 2 80
005 高橋
7行が選択されました。
Left joinによって取り出された上記のデータから、70点以下と、NULLのデータを抽出しているという事になります。基準となる表と外部結合先の関係が、1:1ならいいのですが、1:nの場合にはうまく取り出せません。
1:1の関係にする
この様なケースでは、1学期と2学期を分けてSQLを書く必要があります。
resultテーブルからひとつの学期のデータのみ取り出し、studentテーブルと1:1の関係にします。
SELECT s.id,s.name,r.season,r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 1
UNION
SELECT s.id,s.name,r.season,r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 2
ORDER BY 1,3;
別々に取り出してUNIONしてみました。
(結果)
ID NAME SE SCORE
----- ------- ------- -----------
001 山田 1 100
001 山田 2 100
002 田中 1 80
002 田中 2 85
003 鈴木 1 70
003 鈴木
004 佐藤 2 80
004 佐藤
005 高橋
9行が選択されました。
なんとなくよさそうには見えますが、まだ問題があります。
高橋さんのデータが1件しかありません。さらに、鈴木さん佐藤さんの未受験の学期も空白になっています。
高橋さんが1件なのは、UNION ALLを使えば解消しますが、ここでは別の手を使います。
1学期、2学期と、それぞれ狙い撃ちで取得していますから、結果に固定値として置いても問題ありません。
SELECT s.id,s.name,1 AS gakki,r.season ,r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 1
UNION
SELECT s.id,s.name,2 AS gakki,r.season, r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 2
ORDER BY 1,3 ;
ID NAME GA SE SCORE
----- -------- --- ---- -----------
001 山田 1 1 100
001 山田 2 2 100
002 田中 1 1 80
002 田中 2 2 85
003 鈴木 1 1 70
003 鈴木 2
004 佐藤 1
004 佐藤 2 2 80
005 高橋 1
005 高橋 2
10行が選択されました。
これで5人×2学期=10行のデータを取り出すことが出来ました。
後は、where 句を追加すれば完成です。
この10行の中から、必要なデータを取り出すというイメージです。
SELECT s.id,s.name,1 AS gakki,r.season ,r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 1
WHERE r.score IS NULL OR r.score <= 70
UNION
SELECT s.id,s.name,2 AS gakki,r.season, r.score
FROM student s
LEFT JOIN result r ON s.id = r.id AND r.season = 2
WHERE r.score IS NULL OR r.score <= 70
ORDER BY 1,3 ;
(検索結果)
ID NAME GA SE SCORE
----- --------- --- ---- -----------
003 鈴木 1 1 70
003 鈴木 2
004 佐藤 1
005 高橋 1
005 高橋 2
これで完成です。
各学期を横に並べてみる
次に、学期を横に並べた表を作ってみましょう。
studentテーブルに、1学期だけ、2学期だけをそれぞれ抽出したresultテーブルと外部結合します。
SELECT s.id,s.name,r1.score AS gakki1 ,r2.score AS gakki2
FROM student s
LEFT JOIN result r1 ON s.id = r1.id AND r1.season = 1
LEFT JOIN result r2 ON s.id = r2.id AND r2.season = 2
ORDER BY 1;
(検索結果)
ID NAME GAKKI1 GAKKI2
------ --------------- ------- ---------
001 山田 100 100
002 田中 80 85
003 鈴木 70
004 佐藤 80
005 高橋
ここまでは簡単です。
次に、1学期と2学期の合計を求めてみましょう。
SELECT s.id,s.name,
r1.score AS gakki1,
r2.score AS gakki2,
r1.score+r2.score AS gokei1
FROM student s
LEFT JOIN result r1 ON s.id = r1.id AND r1.season = 1
LEFT JOIN result r2 ON s.id = r2.id AND r2.season = 2
ORDER BY 1;
ID NAME GAKKI1 GAKKI2 GOKEI1
----- --------- -------- --------- --------
001 山田 100 100 200
002 田中 80 85 165
003 鈴木 70
004 佐藤 80
005 高橋
山田さんと田中さん以外のGOKEI1は空欄になっています。
NULLは何を足しても何を引いても何を掛けても何で割ってもNULLなのです。
DECODEとCASE
という事で、2種類の方法を試してみます。一つはdecode、もう一つはcaseです。それぞれnullの場合に0への変換を試みます。
SELECT s.id,s.name,
r1.score AS gakki1,
r2.score AS gakki2,
r1.score+r2.score AS gokei1,
DECODE(r1.score,NULL,0,r1.score) +
DECODE(r2.score,NULL,0,r2.score) AS gokei2,
CASE WHEN r1.score IS NULL THEN 0 ELSE r1.score END +
CASE WHEN r2.score IS NULL THEN 0 ELSE r2.score END AS gakki3
FROM student s
LEFT JOIN result r1 ON s.id = r1.id AND r1.season = 1
LEFT JOIN result r2 ON s.id = r2.id AND r2.season = 2
ORDER BY 1;
ID NAME GAKKI1 GAKKI2 GOKEI1 GOKEI2 GAKKI3
---- ------- -------- --------- --------- -------- ---------
001 山田 100 100 200 200 200
002 田中 80 85 165 165 165
003 鈴木 70 70
004 佐藤 80 80
005 高橋 0
いかがでしょうか?
nullに何かを足しても答えはnullなので、鈴木さん佐藤さん高橋さんのGOKEI1は空白になっています。一方、decodeで0に置き換えたGOKEI2は期待する値になっています。そしてGOKEI3もnullの影響を受けています。
CASE文の書き方に注意してください。
CASE の後にカラム名を書く方法ではNULLは判別できないし、かといってエラーにもなりません。
NULLは奥が深いですね。
