NULL置換(NVL)のまとめ ― Access・PostgreSQL・SQL Server・Oracleの書き方比較
NVLは、NULLの値を別の値に置き換える関数の名前として知られている。もとはOracleの関数名で、他のデータベースには同名の関数がない。各データベースには同じ目的の関数が別名で用意されている。データベースごとの書き方をまとめる。
データベースごとの書き方一覧
| データベース | 関数 | 例 |
|---|---|---|
| Oracle | NVL | NVL(field, 0) |
| Access | Nz | Nz(field, 0) |
| PostgreSQL | COALESCE | COALESCE(target_col, ‘ ‘) |
| SQL Server | ISNULL / COALESCE | ISNULL(field, 0) |
OracleのNVL
OracleにはNVL関数がそのまま存在する。第1引数がNULLの場合に第2引数を返し、NULLでない場合は第1引数をそのまま返す。引数は2つだけで、3つ以上は指定できない。
1 | SELECT NVL(field, 0) FROM table; |
上記は列の内容がNULLの場合に「0」に置き換えている。第1引数と第2引数のデータ型が異なる場合、Oracleは暗黙の型変換を行う。
AccessのNz
Accessでは、NVL関数は存在しない。代わりにNz関数を利用する。
1 | SELECT Nz(field, 0) FROM table; |
上記は列の内容がNULLの場合に「0」に置き換えている。尚、第2引数を省略すると長さ0の文字列「””」を返す。
ODBC経由でSQLを発行する場合は、NzではなくIIfとIsNullを利用する。
1 | SELECT IIf(IsNull([フィールド名]), '', [フィールド名]) FROM table; |
PostgreSQLのCOALESCE
PostgreSQLでは、NVL関数は存在しない。代わりにCOALESCE(コアレス)関数が用意されている。
1 | SELECT COALESCE(target_col, ' ') FROM target_table; |
正確にはNVL関数は「該当する値がNULLの場合の代替値を返す」仕様に対して、COALESCEは「NULLでない自身の最初の引数を返す」仕様であり引数を列挙することができる。
1 | SELECT COALESCE(target_col1, target_col2, target_col3, ' ') FROM target_table; |
上記の場合は、target_col1がNULLならtarget_col2、それもNULLならtarget_col3。それでもNULLなら’ ‘、といった「IF-ELSE構文」のような挙動をする。
たとえば、表示名をニックネーム、本名、「ゲスト」の順に決める場合は、次のように書くこともできる。
1 | SELECT COALESCE(nickname, real_name, 'ゲスト') AS display_name FROM users; |
SQL ServerのISNULLとCOALESCE
SQL Serverでは、NVL関数は存在しない。代わりにISNULL関数を利用する。
1 | SELECT ISNULL(field, 0) FROM table; |
上記は列の内容がNULLの場合に「0」に置き換えている。
なお、NULL置換にはCOALESCEも使える。COALESCEは標準SQLであり、他のデータベースでも同じ書き方ができる。
1 | SELECT COALESCE(field, 0) FROM table; |
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