【豆知識】NULLと空文字との違いは？NULLを扱いを理解しよう！

頼れる人

データベースを取り扱ううえで、どうしても理解しないといけないのがNULLです
NULLとはどういうものなのかを教えるよ

もうすでに、NULL値というものがどういったものかは知ってるよ！という人は、ここは読まなくていいですよ

こけさん

NULLってなに？空文字と何が違うの？

テーブルのカラムには、文字列や数値・日付などが入ります。
データベースでのNULLとは、カラムの中には何も入っていない状態のことを指します。

テーブルの中のデータは、そのカラムに入っている値の長さと実際の値が入っています。
NULLのカラムには何も入っていないので、カラム値の長さにもカラム値自体にも、何にも入っていません。（①参照）
それと似ているのが空文字です。こちらは長さには０が入っており、カラム値には何も入っていません。（②参照）

NULLと空文字は似ていますが、全く違うものです。
NULLはいろいろなデータ型で利用されて何も入っていない状態を表しますが、空文字は文字列型のみで利用されます。

文字列型のカラムには空文字、数値型のカラムには０を入れて、初期状態を表すこともできます。
しかし、日付型のときは最小値が1970/1/1となるため、カラムに値が入っていないことを表すには非常にわかりにくいです。
そのため、値が入っていないことを表すためには、NULLというのが大変わかりやすくなります。

NULLとなっているカラムを扱うには？

NULL値となっているカラムは、特殊な状態となっていますので、＝や＞などを使って、比較したりすることができません。
空文字が入っているときには、＝を使うことができます。
WHERE句で使うには、カラム名 IS NULLという指定をします。

// WHERE句でIS NULLを指定したとき
SELECT id
      ,name
      ,age
FROM players
WHERE teamid IS NULL;

// 3,海籐　一之介,9
// idが4のレコードは取得されません

// WHERE句で空文字を指定したとき
SELECT id
      ,name
      ,age
FROM players
WHERE teamid = '';

// 4,熊田　文人,11
// idが3のレコードは取得されません

SELECT句でNULLのカラムを指定すると、NULLで出力されます。
空文字のカラムを指定すると、空文字で出力されます。
NULLのときには、別の値に置換することができるいろんな関数が用意されていますので、それを利用することも多いです。

NVL関数では、カラム値がNULLのときに置換する値を指定することができます。
そのため、カラム値がNULLとなっていたときには、指定した値に置換されます。NULLでないときには、そのカラムに入っている値が出力されます。
NULLは＝で比較できませんが、NVLで空文字に置換すれば＝で比較することもできます。

NULLを扱う関数を利用するとこんなに便利

NULLを扱う関数以外では、結果はNULLとなってしまいます。
COUNT関数ではNULLのカラムは件数に含まれませんし、MAX関数ではNULLが含まれているカラムでは結果がNULLとなってしまいます。

先に挙げたNVL関数を使って空文字に置換することで、COUNT関数では件数に含まれますし、MAX関数ではすべての値の中での最大値が返ってきます。

// NULLを含むカラムの件数を求める（NVL関数を使わない）
SELECT COUNT(teamid)
FROM players;

// 3件

// NULLを含むカラムの件数を求める（NVL関数を使う）
SELECT COUNT(NVL(teamid),'')
FROM players;

// 4件

SELECT句だけでなく、WHERE句やHAVING句でも、NULLを置換する関数を利用することができます。

// WHERE句でNVL関数を使ったとき
SELECT id
      ,name
      ,age
FROM players
WHERE NVL(teamid, ‘’) = ‘’;

// 3,海籐　一之介,9
// 4,熊田　文人,11

NVL関数のほかにも、NVL2関数やCOALESCE関数などがあり、それぞれの用途によって使い分けていきましょう。
NVL2関数はNVL関数と似ていますが、NULL以外のときにはカラム値ではなく、もう１つの置換する値が返ってきます。

// NVL関数とNVL2関数を使ったとき
SELECT id
      ,name
      ,NVL(teamid, ‘なし’)
      ,NVL2(teamid, ‘あり’,  ‘なし’)
FROM players;

// 1,中島　康太,A,あり
// 2,山本　茂樹,B,あり
// 3,海藤　一之介,なし,なし
// 4,熊田　文人,,あり

COALESCE関数は、最初に見つかったNULL以外の値が返ってきます。

SELECT COALESCE(null, teamid, name, id)
FROM players;

// A
// B
// 海藤　一之介
// （空文字）

カラムにNULLが許可されていないときって？

テーブルの定義をするときに、カラム名やデータ型を指定します。
idというカラムは、データ型は数値型で、長さは最大２バイトまで
nameというカラムは、データ型は文字列型で、長さは最大１００バイトまで
このようにカラム１つずつに対して、どういった内容を入れるかによる定義を行います。

その際、NULLを許容するか否かを指定することもできます。それをNOT NULL制約といいます。
NOT NULL制約がつけられたカラムは、NULLとすることができませんので、何らかの値を設定しなければなりません。
しかし、DEFAULTが指定されていれば、何も値を設定しなかったときにはDEFAULTで指定されている値が自動的に入ります。

まとめ

NULLとは、カラムに何も入っていない状態を表します。
NULLとなっているカラムは、意識して利用しないと想定していない結果が返ってきますので、注意しましょう。
そのカラムにはNULLが入るのか、入らないのかを確認して、NULLとなる可能性があるカラムはNULLを置換する関数を利用するようにしましょう。

▶　一覧に戻る