NULLについて

ITコーディネータのシュウです。

会社からの帰宅途中で撮った札幌行き寝台特急「北斗星」の写真です。北斗星はブルートレインとも言われるので、通常は青色だと思いますが、写真はカシオペア色の牽引機ですね。
見かけるたびに、一度は乗ってみたいと思っていたのですが、来年中には北斗星が廃止される方針というニュースが流れています。残念ですね。
自分も、田舎（北海道ではないですが）から東京に初めて大学受験に来たとき、寝台特急で来たことを今も覚えています。
一度乗ってみたかったな～！

＜本日の題材＞
NULLについて

NULLは、データが存在しない（空である）ということを意味しますが、データを扱う上で、NULLというのはときに非常にやっかいな存在ですね。（NULLは「不明」という言葉で表現する場合もあります）誤って使用すると、データが正しく抽出されないということを何度も経験した方もいらっしゃると思います。
今日は、NULLを扱う際の注意点について取り上げたいと思います。

以下のような社員マスタを使用します。歩合給のデータがある人もいれば、NULLの人もいる状態です。

SELECT * FROM 社員マスタ
ORDER BY 社員番号;

ここで、「固定給」+「歩合給」がその人の給与となるので、給与が300000以上の方を出す場合に、以下のように計算してしまうと、

SELECT * FROM 社員マスタ
WHERE 固定給+歩合給 > 300000
ORDER BY 社員番号;

となってしまい、求めたい人数より少ない結果が出ました。固定給が 300000 以上だけれども、歩合給が NULLの方が抽出されていません。
試しに、歩合給がNULLの方を抽出するのに、=NULLとした場合、

SELECT * FROM 社員マスタ
WHERE歩合給 = NULL;

結果は1件も抽出されません。

これは、NULLに対しては、以下のような比較を行う式は全て unknown と評価されてしまい、trueとならないために、結果が抽出されてこないのです。

項目 = NULL
項目> NULL
項目< NULL
項目<> NULL
NULL = NULL

さらに、項目+NULL、項目-NULL、項目*NULL、項目/NULL など、NULLを含む算術演算はすべて結果も NULL になります。そのため、最初の例の、給与+歩合給の場合、歩合給が NULL のデータは足し算の結果も NULL となり、給与+歩合給>300000 という式は unknown となって、抽出結果に出て来なかったということです。

それでは、NULLのものを抽出するためにはどうするかというと、
「IS NULL」という句を使います。

SELECT * FROM 社員マスタ
WHERE 歩合給 IS NULL;

反対に、NULLでないものを抽出するのは、「IS NOT NULL」を使います。

SELECT * FROM 社員マスタ
WHERE 歩合給 IS NOT NULL;

結果としては、全て拠点がシカゴの人です。

それでは、最初の例に戻って、固定給+歩合給が300000以上の方を抽出したい場合、Oracleであれば、NVL関数などを使用して、NULLであれば指定した値を戻すという関数をよく使います。
NULLの場合は0とみなせば、この比較は成立するので、
NVL(歩合給,0) と記述して

SELECT * FROM 社員マスタ
WHERE 固定給+NVL(歩合給,0) > 300000
ORDER BY 社員番号;

求める結果が出ました。

ちなみに、SQL Serverの場合には、NVL関数ではなく、ISNULL関数を使います。

SELECT * FROM 社員マスタ
WHERE 固定給+ISNULL(歩合給,0) > 300000
ORDER BY 社員番号;

算術演算ということで、+, - *, / などを上げましたが、SUMやAVGなどの集計関数でも同様に影響があります。例えば、各拠点毎の、
固定給+歩合給の合計・平均などを求めたい場合に、NULLがあると通常のSQLでは正しい結果が出ません。

SELECT 拠点, SUM(固定給) 固定給計, SUM(歩合給) 歩合給計
, SUM(固定給+歩合給) 給与計, AVG(固定給+歩合給) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;

固定給+歩合給のSUM、AVGを計算している箇所の結果が正しくありません。正しい値を取得するためには、先ほどの NVL関数を使用して、以下のようになります。

SELECT 拠点, SUM(固定給) 固定給計
, SUM(NVL(歩合給,0)) 歩合給計
, SUM(固定給+NVL(歩合給,0)) 給与計
, AVG(固定給+NVL(歩合給,0)) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;

先ほどと結果が変わりました。

SQL Serverでは以下のようになります。

SELECT 拠点, SUM(固定給) 固定給計
, SUM(ISNULL(歩合給,0)) 歩合給計
, SUM(固定給+ISNULL(歩合給,0)) 給与計
, AVG(固定給+ISNULL(歩合給,0)) 平均給与
, COUNT(歩合給) 歩合給人数, COUNT(*) 人数
FROM 社員マスタ
GROUP BY 拠点
ORDER BY 拠点;