SYSTEM238 / NOTES1 / データベース / null値を置換する

NOTES1 PROG1
null値を置換する
PostgreSQLとMySQLで結果がnull値だった場合に置換する方法をまとめた。

PostgreSQLの場合

null値を置換するには、COALESCE関数を利用します。

COALESCE(value [, ...])

COALESCE関数はnullでない最初の引数を返します。

SELECT COALESCE(col1, col2, 'is null')...
 col1がnullでない場合、col1が返ります。
 col1がnullでcol2がnullでない場合、col2が返ります。
 col1、col2の両方がnullの場合は'is null'が返ります。

COALESCE関数はCASE式の簡略表記です。内部的にはCASE式に変換されて処理されます。したがって、COALESCEを誤って使用すると、CASEに関連したエラーメッセージが表示されることがあります。

MySQLの場合

null値を置換するには、IFNULL、COALESCE関数を利用します。

IFNULL(value1, value2)

IFNULL関数はvalue1がnullでない場合はvalue1を返し、それ以外の場合はvalue2を返します。

SELECT IFNULL(col1, 'is null')...
 col1がnullでない場合はcol1が返ります。
 col1がnullの場合は'is null'が返ります。

COALESCE(value [, ...])

COALESCE関数はnullでない最初の引数を返します。

PostgreSQLのCOALESCEと同じ。

Oracleの場合

null値を置換するには、NVL、COALESCE関数を利用します。

NVL(value1, value2)

NVL関数はvalue1がnullでない場合はvalue1を返し、それ以外の場合はvalue2を返します。

SELECT NVL(col1, 'is null')...
 col1がnullでない場合はcol1が返ります。
 col1がnullの場合は'is null'が返ります。

COALESCE(value [, ...])

COALESCE関数はnullでない最初の引数を返します。

PostgreSQLのCOALESCEと同じ。

まとめ・その他

  • 互換性を重視するならCOALESCE関数を使用する。
  • MySqlのIFNULL関数とOracleのNVL関数は同じ。
  • COALESCE関数はすべての値がnullならnullが返る。null値になってほしくない場合は引数の最後に定数を指定する。
  • IFNULL関数とのNVL関数の引数がnullの場合はnullが返る。
  • 定数を指定する場合は、カラムの型にあった値を指定する。(number → 0、text → '文字列')
  • null値を含んだカラムの並び替えについてはnull値を考慮した並び替えを参照してください。