SQL NULLIF関数は同じ値の場合にNULLを返す IFNULLとの違いに注意

SQLのNULLIF関数についてまとめています。

なお、データベースはMySQLのサンプルデータベースEmployeesを使用しています。

SQLのNULLIF関数は、引数を比較して同値の場合にNULLを返す

NULLIF関数は、値を比較する関数です。第一引数と第二引数を比較して、同じ値ならNULLを異なる値なら第一引数の値を返します。例えば、Oracleでは以下のように使います。

select nullif(1,1) from DUAL;
NULLIF(1,1)
------
 - 

select nullif(1,2) from DUAL;
NULLIF(1,2)
-----
1

NULLIF関数は、MySQLでも使えます。以下はdepartments（部署）テーブルから、dept_no（部署番号）、dept_name（部署名）、nullif（dept_no、’d003’）を取得するSQLです。dept_no=d003のレコードのみ、nullif関数の返り値がNULLになっています。

mysql> select dept_no, dept_name, nullif( dept_no, 'd003') from departments;
+---------+--------------------+--------------------------+
| dept_no | dept_name          | nullif( dept_no, 'd003') |
+---------+--------------------+--------------------------+
| d009    | Customer Service   | d009                     |
| d005    | Development        | d005                     |
| d002    | Finance            | d002                     |
| d003    | Human Resources    | NULL                     |
| d001    | Marketing          | d001                     |
| d004    | Production         | d004                     |
| d006    | Quality Management | d006                     |
| d008    | Research           | d008                     |
| d007    | Sales              | d007                     |
+---------+--------------------+--------------------------+
9 rows in set (0.00 sec)

値がNULLの場合に代替値を返すNVLやCOALESCEを使って指定した値を別の値に置き換えることが可能です。以下のSQLは、departmentsテーブル内のdept_no=d003の場合にXXXを返すサンプルです。

mysql> select dept_no, dept_name, coalesce(nullif( dept_no, 'd003'), 'XXX' ) from departments;
+---------+--------------------+--------------------------------------------+
| dept_no | dept_name          | coalesce(nullif( dept_no, 'd003'), 'XXX' ) |
+---------+--------------------+--------------------------------------------+
| d009    | Customer Service   | d009                                       |
| d005    | Development        | d005                                       |
| d002    | Finance            | d002                                       |
| d003    | Human Resources    | XXX                                        |
| d001    | Marketing          | d001                                       |
| d004    | Production         | d004                                       |
| d006    | Quality Management | d006                                       |
| d008    | Research           | d008                                       |
| d007    | Sales              | d007                                       |
+---------+--------------------+--------------------------------------------+
9 rows in set (0.01 sec)

【関連記事】
▶SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり

なお、複数の値を置き換える必要がある場合は、CASE文を使います。

【関連記事】
▶SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ

MYSQLにはNULLIFとIFNULL関数があり　混同に注意

例えばmysqlにはIFNULLという似た名前の関数があります。IFNULLは、第一引数がNULLだった場合に第二引数の値を返す関数です。

mysql> select ifnull( null, 'xxx' );
+-----------------------+
| ifnull( null, 'xxx' ) |
+-----------------------+
| xxx                   |
+-----------------------+
1 row in set (0.00 sec)

mysql> select nullif( 'a', 'a' );
+--------------------+
| nullif( 'a', 'a' ) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

引数の数も同じで間違いやすく、パッと見て何が間違いかわからないバグを生む可能性あり。やっかいなバグを避けるために、どちらかの関数を使わないようにコーディング規約で定めると良いかも知れません。

まとめ

ポテパンダの一言メモ

• NULLIF関数は、与えられた2つの値が一致したときNULLを返す
• IFNULLは、第一引数がNULLの場合、第二引数を返す
• NULLIFとNVMもしくはCOALESCEを組み合わせて、特定の値の置き換えが可能