新人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 r1.score WHEN NULL THEN 0 ELSE r1.score END +
CASE r2.score WHEN 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では期待する結果は得られないという事です。DECODEとCASEは似ているけれどNULLの扱いが異なります。

NULLは奥が深いですね。

知らなさそうなメンバーがいたら、教えてあげてください。

コメントを残す

メールアドレスが公開されることはありません。

thirteen + fifteen =