SQL練習室
← SQLガイド

SQLのNULLIFとは?ゼロ除算の回避・空文字をNULLにする使い方とCASEとの違い

NULLIF(a, b)は、aとbが等しければNULLを、等しくなければaをそのまま返す関数です。空文字や「不明」のような意味のない値をNULLに揃えたり、割り算の分母が0のときだけNULLにしてエラーを避けたりするのに使います。COALESCEの逆向きの働きをする関数として、使い方を確認しましょう。

例で使う表

shopsは店舗、contactsは連絡先です。shopsのsalesは売上(円)、visitorsは来店者数で、北野店の来店者数は0です。富士店の売上は0円です。contactsのphoneは電話番号で、北野は空文字('')、中央はNULL、大和は「不明」という文字が入っています。

shops:店舗5件(salesは売上・円、visitorsは来店者数)
idnamesalesvisitors
1青木店12000040
2北野店900000
3中央店6000017
4大和店5000020
5富士店025
contacts:連絡先4件(北野は空文字、中央はNULL)
idnamephone
1青木090-1111-2222
2北野''(空文字)
3中央NULL
4大和不明

NULLIFの基本構文

sql
SELECT id, name, NULLIF(phone, '') AS phone
FROM contacts;

NULLIFで空文字をNULLに変える

NULLIF(phone, '')は、phoneが空文字のときだけNULLを返し、それ以外はphoneをそのまま返します。北野の空文字はNULLになります。中央はもともとNULL、青木の電話番号と大和の「不明」は空文字ではないので変わりません。空文字は「入力済み」として扱われ、IS NULLでは見つからないため、NULLに揃えたいときに使います。

sql
SELECT id, name, NULLIF(phone, '') AS phone
FROM contacts
ORDER BY id;
実行結果
idnamephone
1青木090-1111-2222
2北野NULL
3中央NULL
4大和不明

NULLIFで0除算のエラーを避ける

整数や数値を0で割ると、PostgreSQLはエラーを返して問い合わせ全体を止めます。分母をNULLIF(visitors, 0)にすると、visitorsが0の行だけ分母がNULLになり、結果もNULLになります。来店者1人あたりの売上は、青木店3000、北野店NULL、中央店3529、大和店2500、富士店0です。中央店の60000÷17は、整数どうしの割り算なので小数点以下が切り捨てられます。

sql
SELECT name, sales / NULLIF(visitors, 0) AS per_visitor
FROM shops
ORDER BY id;
実行結果
nameper_visitor
青木店3000
北野店NULL
中央店3529
大和店2500
富士店0

COALESCEと組み合わせて空文字を既定値に置き換える

COALESCEはNULLを別の値に置き換えますが、空文字はNULLではないため置き換わりません。先にNULLIFで空文字をNULLにしてから、COALESCEで「未登録」に置き換えます。北野(空文字)と中央(NULL)は未登録になり、青木の電話番号と大和の「不明」はそのまま残ります。NULLIFが「値をNULLにする」側、COALESCEが「NULLを値にする」側です。

sql
SELECT id, name, COALESCE(NULLIF(phone, ''), '未登録') AS phone
FROM contacts
ORDER BY id;
実行結果
idnamephone
1青木090-1111-2222
2北野未登録
3中央未登録
4大和不明

CASE式で同じ処理を書き換える

NULLIF(a, b)は、CASE WHEN a = b THEN NULL ELSE a ENDと同じ意味です。次のSQLは、来店者数が0のときNULLにする2つの書き方を並べて、結果が一致することを示します。NULLIFのほうが短く、分母を2回書かなくて済みます。条件が「visitorsが0」のように等しいかどうかだけならNULLIF、「visitorsが0または売上が0」のような複数の条件ならCASE式を使います。

sql
SELECT name,
  sales / NULLIF(visitors, 0) AS by_nullif,
  CASE WHEN visitors = 0 THEN NULL ELSE sales / visitors END AS by_case
FROM shops
ORDER BY id;
実行結果
nameby_nullifby_case
青木店30003000
北野店NULLNULL
中央店35293529
大和店25002500
富士店00

COUNTと組み合わせて入力済みの件数を数える

COUNT(列)はNULLを数えないため、空文字は件数に入ります。COUNT(phone)は青木、北野(空文字)、大和の3件です。NULLIF(phone, '')で空文字をNULLにしてから数えると、青木と大和の2件になります。「不明」という文字は空文字ではないので、どちらの数え方でも件数に含まれます。

sql
SELECT COUNT(phone) AS not_null, COUNT(NULLIF(phone, '')) AS not_empty
FROM contacts;
実行結果
not_nullnot_empty
32

NULLIFでよくある間違い

NULLIFで0除算を避けても、整数の割り算は切り捨てのまま

sales / NULLIF(visitors, 0)は、どちらも整数なので結果も整数で、中央店は3529.41…ではなく3529になります。小数で求めたいときは、片方をnumericに変換してからROUNDで丸めます。NULLIFは0除算のエラーを避けるだけで、割り算の型は変えません。

sql
SELECT name, ROUND(sales::numeric / NULLIF(visitors, 0), 1) AS per_visitor
FROM shops
ORDER BY id;

実際に1問解いてみる

NULLIF

contactsのid、name、および電話番号phoneが空文字のときだけNULLにした値を取得してください。idの昇順に表示します。別名は付けません。

テーブル

読み込み中…

実行結果

関連構文

NULLIFについてよくある質問

NULLIFとCOALESCEは何が違いますか?
NULLIFは「ある値のときだけNULLにする」関数で、COALESCEは「NULLのときに別の値にする」関数です。逆向きの働きなので、空文字を未登録に置き換えるときのようにCOALESCE(NULLIF(列, ''), '未登録')と組み合わせます。
ゼロ除算を避けるのにNULLIFとCASEのどちらがよいですか?
分母が0のときだけNULLにするなら、NULLIFのほうが短く書けます。分母が0のとき0を返したいなら、COALESCEを組み合わせます。条件が複数ある場合や、NULL以外の値を返したいときはCASE式にします。
0除算でNULLになった行は、結果から除かれますか?
除かれません。その行は結果に残り、割り算の結果の列だけがNULLになります。NULLの行を除きたいときは、WHEREで分母が0でない行に絞るか、結果がNULLでない行だけを選びます。
NULLIFで比べる値の型が違うとどうなりますか?
=で比べられる組み合わせなら、型が自動的にそろえられて比べられます。比べられない組み合わせ(たとえば整数と、数字ではない文字列)はエラーになります。型を明示したいときはCASTで変換します。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。