NVL()

第1引数の値を評価します。
NULLでないなら、第1引数の値のそのまま返します。
NULLなら第2引数の値を返します。

NVL()動作例

下記テーブルに対して…

お名前
たきな
NULL
ちさと

下記SQLの結果は…

SELECT NVL(お名前, 'NULLじゃん') AS NVLかました後
FROM テーブル名

こうなります。

NVLかました後
たきな
NULLじゃん
ちさと

NVL()の注意点

NVL()によって 返される値の型 が 一つに定まらない 時は例外になります。

例えば下記テーブルに対して…

数値系カラム
1
NULL
99

下記SQLを流すと…

SELECT NVL(数値系カラム, 'NULLじゃん') AS NVLかました後
FROM テーブル名

『ORA-01722: 数値が無効です。』の例外になります。

1行目については『1』という数値型が返ります。
ですが2行目については『NULLじゃん』という文字列型が返ってしまいます。
行によって返る値の型が一つに定まりません。

NVL2()

第1引数の値を評価します。
NULLでないなら第2引数の値を返します。
NULLなら第3引数の値を返します。

NVL2()動作例

下記テーブルに対して…

お名前
たきな
NULL
ちさと

下記SQLの結果は…

SELECT NVL2(お名前, 'NULLじゃないですねえ', 'NULLじゃん') AS NVL2かました後
FROM テーブル名

こうなります。

NVL2かました後
NULLじゃないですねえ
NULLじゃん
NULLじゃないですねえ

NVL2()の注意点

NVL2()によって 返される値の型 が 一つに定まらない 時は例外になります。
NVL()の注意点とほぼ同様になります。

下記のようなSQLは『ORA-00932: データ型が一致しません』の例外になります。

NVL2(お名前, 1, SYSDATE)

NVL2()じゃなくてNVL()でいいじゃんという例

NVL2(お名前, お名前, 'NULLじゃん')
は
NVL(お名前, 'NULLじゃん')
と同じ動作をします。

後者の方が記述が少ないので、コーディングミスや改修コストも少なくなります。

COALESCE()

渡された 引数たち を 左から順 に評価していきます。
NULLでない値を見つけたら即座にその値を返して関数の処理終了。

COALESCE()動作例

下記テーブルに対して…

お名前
たきな
NULL
ちさと

下記SQLの結果は…

SELECT COALESCE(お名前, 'NULLじゃん') AS COALESCEかました後
FROM テーブル名

こうなります。

COALESCEかました後
たきな
NULLじゃん
ちさと

COALESCE()は複数の引数を左から順番に判定していく

例えば下記は…

COALESCE(苗字, 名前, 電話番号, 'あーもう3つ全部NULLだ')

『苗字』カラムがNULLでなければ『苗字』カラムの値。
『苗字』カラムがNULLで、『名前』カラムがNULLでなければ、『名前』カラムの値。
『苗字』『名前』カラムが両方NULLで『電話番号』カラムがNULLでなければ、『電話番号』カラムの値。
3カラム全部NULLなら『あーもう3つ全部NULLだ』の文字列。

COALESCE()はあくまで 『引数が二つの時にNVL()と同じ動作をする』 のであって、 『NVL()と同じ関数ではない』 です。

COALESCE()の注意点

COALESCE()によって 返される値の型 が 一つに定まらない 時は例外になります。
NVL()の注意点とほぼ同様になります。

下記のようなSQLは『ORA-00932: データ型が一致しません』の例外になります。

COALESCE(1, 'あ')

蛇足

まず NVL() はOracle独自関数で、MySQLやPostgreにはありません。
逆に COALESCE() はMySQLやPostgreにも同名関数があります。

複数のDBソフトが混在する環境でも『NULL置換は COALESCE() 』という統一化をしやすい意味で COALESCE() がよいかと思っています。
学習コストが単純に半分になりますし、『SQLでNULL置換してる所ってどこ？』みたいな影響範囲調査もしやすくなります。

NVL2() については、この関数自体あまり使ったことがありません。
NVL2(お名前, TRUE, FALSE) は お名前 IS NULL と書いた方が短いですし、何より人が見て分かりやすいです。
また、CASE文で NVL2() と同じことができます。

MySQLやPostgreにも用意されているCASE文を使えば済むので、わざわざ NVL2() を別途覚える必要は無いかなあ…と思っています。

個人的な考えをまとめると

単純なNULL置換は COALESCE() 。
NVL2() ではなくCASE文を使う。

です。

TODO:NVLとCOALESCE、NVL2とCASE文にパフォーマンスの違いがあるかどうか調べる。

参考サイトさん

バージョン

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production