NULL置換(NVL)のまとめ ― Access・PostgreSQL・SQL Server・Oracleの書き方比較

NVLは、NULLの値を別の値に置き換える関数の名前として知られている。もとはOracleの関数名で、他のデータベースには同名の関数がない。各データベースには同じ目的の関数が別名で用意されている。データベースごとの書き方をまとめる。

データベースごとの書き方一覧

データベース関数例
OracleNVLNVL(field, 0)
AccessNzNz(field, 0)
PostgreSQLCOALESCECOALESCE(target_col, ‘ ‘)
SQL ServerISNULL / COALESCEISNULL(field, 0)

OracleのNVL

OracleにはNVL関数がそのまま存在する。第1引数がNULLの場合に第2引数を返し、NULLでない場合は第1引数をそのまま返す。引数は2つだけで、3つ以上は指定できない。

上記は列の内容がNULLの場合に「0」に置き換えている。第1引数と第2引数のデータ型が異なる場合、Oracleは暗黙の型変換を行う。

AccessのNz

Accessでは、NVL関数は存在しない。代わりにNz関数を利用する。

上記は列の内容がNULLの場合に「0」に置き換えている。尚、第2引数を省略すると長さ0の文字列「””」を返す。

ODBC経由でSQLを発行する場合は、NzではなくIIfとIsNullを利用する。

PostgreSQLのCOALESCE

PostgreSQLでは、NVL関数は存在しない。代わりにCOALESCE(コアレス)関数が用意されている。

正確にはNVL関数は「該当する値がNULLの場合の代替値を返す」仕様に対して、COALESCEは「NULLでない自身の最初の引数を返す」仕様であり引数を列挙することができる。

上記の場合は、target_col1がNULLならtarget_col2、それもNULLならtarget_col3。それでもNULLなら’ ‘、といった「IF-ELSE構文」のような挙動をする。

たとえば、表示名をニックネーム、本名、「ゲスト」の順に決める場合は、次のように書くこともできる。

SQL ServerのISNULLとCOALESCE

SQL Serverでは、NVL関数は存在しない。代わりにISNULL関数を利用する。

上記は列の内容がNULLの場合に「0」に置き換えている。

なお、NULL置換にはCOALESCEも使える。COALESCEは標準SQLであり、他のデータベースでも同じ書き方ができる。

ISNULLとCOALESCEの違いは、戻り値の型の決まり方にある。ISNULLは第1引数の型を返し、COALESCEは型の優先順位で決まる。COALESCEは複数の引数を取って最初にNULLでない値を返すこともできる。

どれを使うか

同じデータベースの中で書く場合は、そのデータベースの流儀の関数を使う。複数のデータベースをまたいで同じ書き方をしたい場合や、Oracleから移行する場合は、標準SQLであるCOALESCEを選ぶ。複数の候補から順に選びたい場合も、COALESCEが向いている。

まとめ

  • NVLという名前の関数はOracleのもので、Access・PostgreSQL・SQL Serverには存在しない。
  • AccessはNz、PostgreSQLはCOALESCE、SQL ServerはISNULLを使う。
  • COALESCEは標準SQLで、PostgreSQLとSQL Serverの両方で使える。複数の引数を指定できる。
  • 移植性を重視する場合はCOALESCEを選ぶ。

関連記事

  • AccessのクエリでNVL(NULL置換)
    https://knowledge.reontosanta.com/archives/596
  • PostgresqlでNVL(NULL置換)
    https://knowledge.reontosanta.com/archives/169
  • SQLServerでNVL(NULL置換)
    https://knowledge.reontosanta.com/archives/446

おすすめ

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。