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