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、大和は「不明」という文字が入っています。
| id | name | sales | visitors |
|---|---|---|---|
| 1 | 青木店 | 120000 | 40 |
| 2 | 北野店 | 90000 | 0 |
| 3 | 中央店 | 60000 | 17 |
| 4 | 大和店 | 50000 | 20 |
| 5 | 富士店 | 0 | 25 |
| id | name | phone |
|---|---|---|
| 1 | 青木 | 090-1111-2222 |
| 2 | 北野 | ''(空文字) |
| 3 | 中央 | NULL |
| 4 | 大和 | 不明 |
NULLIFの基本構文
SELECT id, name, NULLIF(phone, '') AS phone
FROM contacts;NULLIFで空文字をNULLに変える
NULLIF(phone, '')は、phoneが空文字のときだけNULLを返し、それ以外はphoneをそのまま返します。北野の空文字はNULLになります。中央はもともとNULL、青木の電話番号と大和の「不明」は空文字ではないので変わりません。空文字は「入力済み」として扱われ、IS NULLでは見つからないため、NULLに揃えたいときに使います。
SELECT id, name, NULLIF(phone, '') AS phone
FROM contacts
ORDER BY id;| id | name | phone |
|---|---|---|
| 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は、整数どうしの割り算なので小数点以下が切り捨てられます。
SELECT name, sales / NULLIF(visitors, 0) AS per_visitor
FROM shops
ORDER BY id;| name | per_visitor |
|---|---|
| 青木店 | 3000 |
| 北野店 | NULL |
| 中央店 | 3529 |
| 大和店 | 2500 |
| 富士店 | 0 |
COALESCEと組み合わせて空文字を既定値に置き換える
COALESCEはNULLを別の値に置き換えますが、空文字はNULLではないため置き換わりません。先にNULLIFで空文字をNULLにしてから、COALESCEで「未登録」に置き換えます。北野(空文字)と中央(NULL)は未登録になり、青木の電話番号と大和の「不明」はそのまま残ります。NULLIFが「値をNULLにする」側、COALESCEが「NULLを値にする」側です。
SELECT id, name, COALESCE(NULLIF(phone, ''), '未登録') AS phone
FROM contacts
ORDER BY id;| id | name | phone |
|---|---|---|
| 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式を使います。
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;| name | by_nullif | by_case |
|---|---|---|
| 青木店 | 3000 | 3000 |
| 北野店 | NULL | NULL |
| 中央店 | 3529 | 3529 |
| 大和店 | 2500 | 2500 |
| 富士店 | 0 | 0 |
COUNTと組み合わせて入力済みの件数を数える
COUNT(列)はNULLを数えないため、空文字は件数に入ります。COUNT(phone)は青木、北野(空文字)、大和の3件です。NULLIF(phone, '')で空文字をNULLにしてから数えると、青木と大和の2件になります。「不明」という文字は空文字ではないので、どちらの数え方でも件数に含まれます。
SELECT COUNT(phone) AS not_null, COUNT(NULLIF(phone, '')) AS not_empty
FROM contacts;| not_null | not_empty |
|---|---|
| 3 | 2 |
NULLIFでよくある間違い
NULLIFで0除算を避けても、整数の割り算は切り捨てのまま
sales / NULLIF(visitors, 0)は、どちらも整数なので結果も整数で、中央店は3529.41…ではなく3529になります。小数で求めたいときは、片方をnumericに変換してからROUNDで丸めます。NULLIFは0除算のエラーを避けるだけで、割り算の型は変えません。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:NULLIF — NULLIFの動作、比較規則と結果の型
- PostgreSQL公式:COALESCE — NULLでない最初の値を返す関数