OracleのNVL2の使い方です。NVLの機能をパワーアップさせた関数です。NVLとの違いも解説します。
NVL2とは？
NVL2はNVLの機能をパワーアップさせた関数です。と言っても、NVLから劇的に変わったわけではありません。表にまとめると以下になります。
| 第１引数がNULL以外の時 | 第１引数がNULLの時 | |
| NVL | 第１引数の値を返す | 第２引数の値を返す |
| NVL2 | 第２引数の値を返す | 第３引数の値を返す |
ちょっとややこしそうな感じがしますが、そんなに難しくはありません。仕組みもNVLとほぼ同じです。NVLの仕組みについては、過去記事をご覧下さい。
NVL2の使い方
NVL2の前に、まずはNVLのおさらいです。NVLは第１引数がNULLの場合は第２引数の値を返します。第１引数がNULL以外の場合は、第１引数の値を返します。例として次の様なSQLを作成しました。
SELECT NVL('NULLじゃないよ','NULLだよ') FROM DUAL
UNION
SELECT NVL(NULL,'NULLだよ') FROM DUAL;
これを実行すると、以下の結果になります。
一方、NVL2は第１引数がNULLの場合は第３引数の値を返します。第１引数がNULL以外の場合は、第２引数の値を返します。ちょっとややこしそうな感じがしますが、実際に実行してみると分かります。NVL2の例のSQLを作りました。
SELECT NVL2('NULLじゃないよ','NOT NULLだよ','NULLだよ') FROM DUAL
UNION
SELECT NVL2(NULL,'NOT NULLだよ','NULLだよ') FROM DUAL;
実行してみましょう。
第１引数に任意の値を渡したNVL2は第２引数の値が返ってきていますね。
実際に使ってみる
まあ、ここまではどこのサイトさんも例文として書いてます。理屈では簡単ですが、「DUALでテスト的にやってるだけで、ようわからん」という人が多いと思います。なので、実際にテーブルを用意して、NVL2を使ってみます。
NVLの時も使った、フルーツのデータが入っているテーブルと、フルーツの色が入っているテーブル（色マスタ）に対してNVL2を使ってみます。テーブルの内容はそれぞれこんな感じ。
２つのテーブルの意味を次の様に定めます。
- TAB_PRODUCT_FRUIT：フルーツの情報
- MST_FRUIT_COLOR：フルーツの色
- TAB_PRODUCT_FRUITのFRUIT_COLOR_CDと、MST_FRUIT_COLORのCOLOR_CODEは紐づいている。
TAB_PRODUCT_FRUITからMST_FRUIT_COLORに対して外部結合して、フルーツの名前とフルーツの色の名前を取得します。
SELECT TAB.NAME AS FRUIT_NAME
,MST.COLOR_NAME AS FRUIT_COLOR_NAME
FROM TAB_PRODUCT_FRUIT TAB
,MST_FRUIT_COLOR MST
WHERE TAB.FRUIT_COLOR_CD = MST.COLOR_CODE(+)
このまま実行すると、TAB_PRODUCT_FRUITとMST_FRUIT_COLORで紐づかない洋ナシの色は取得できません。
そこでNVLとNVL2をそれぞれ使ってみます。まずはNVLから。第１引数の値がNULLなら結果に第２引数の値が表示されます。
SELECT TAB.NAME AS FRUIT_NAME
,NVL(MST.COLOR_NAME,'色が存在しません') AS FRUIT_COLOR_NAME
FROM TAB_PRODUCT_FRUIT TAB
,MST_FRUIT_COLOR MST
WHERE TAB.FRUIT_COLOR_CD = MST.COLOR_CODE(+)
;
次にNVL2を使います。
SELECT TAB.NAME AS FRUIT_NAME
,NVL2(MST.COLOR_NAME,'ありますねえ！','ないです') AS FRUIT_COLOR_NAME
FROM TAB_PRODUCT_FRUIT TAB
,MST_FRUIT_COLOR MST
WHERE TAB.FRUIT_COLOR_CD = MST.COLOR_CODE(+)
;
MST_FRUIT_COLORに値がある場合は「ありますねえ！」、無い場合は「ないです」と返って来ます。実行してみましょう。
NVLでは第１引数がNULL以外の場合、第１引数の値を返してましたが、NVL2では第１引数がNULL以外の場合、第２引数の値を返していることがわかると思います。
関連記事
参考サイト
.
