【Oracle】NVLの使い方と仕組み

OracleのNVLの使い方です。

【スポンサーリンク】

NVLとは？
NVLの使い方
NVLの仕組み
参考サイト
鉄道記事も書いてます！

NVLとは？

Oracleの独自関数であるNVLは「Null Value Logic」という名称の略です。

簡単に言うと、「NULLの時に指定した文字列に変換する」という意味です。ただし、実際のカラムの値を変換しているわけではなく、変換した結果を取得しています。

【スポンサーリンク】

NVLの使い方

NVLの使い方は簡単です。

NVL(文字列,変換文字列)

たったこれだけです。判定してくれるのは文字列だけです。数値を文字列にしたい場合は、第１引数にTO_CHARを使用して下さい。

NVL(TO_CHAR(文字列),変換文字列)

こんな感じ。これだけでは少ないと思いますので、使用例を紹介します。

フルーツのデータが入っているテーブルと、フルーツの色が入っているテーブル（色マスタ）を用意しました。データの内容はこんな感じです。

TAB_PRODUCT_FRUITにフルーツの情報、MST_FRUIT_COLORにフルーツの色を保持しています。TAB_PRODUCT_FRUITにMST_FRUIT_COLORをCOLOR_CODEカラムで外部結合して、フルーツの色の名前を取得します。SQLはこの様に書きました。

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(+)

では、このSQLを実行して、フルーツの色を取得してみましょう。

ちゃんと取得できていますね。しかし、「洋ナシ」の色は取得できていません。取得結果がNULLになっています。何故なら、洋ナシのFRUIT_COLOR_CDは「05」で、MST_FRUIT_COLOR上のCOLOR_CODEに「05」が存在しないからです。

もしNULLだった場合に、「色が存在しません」みたいな表示にしたいと思います。そこでNVLを使用します。

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(+)

「MST.COLOR_NAME」だったところを、「NVL(MST.COLOR_NAME,’色が存在しません’)」としました。では再度実行してみます。

「洋ナシ」の色はMST_FRUIT_COLOR上に存在しないので、取得結果が「色が存在しません」になっています。

【スポンサーリンク】

NVLの仕組み

NVLの仕組みとしては、次の様に考えて貰えばわかりやすいかと思います。

CASE
 WHEN VAL_1 IS NULL THEN 'NULLだよ'
 WHEN VAL_1 IS NOT NULL THEN VAL_1
END

とても簡単なCASE文です。VAL_1というカラムがあったとします。上記のCASE文の意味としては、

VAL_1の値がNULLの場合は「NULL」だよという文字列を使う
VAL_1の値がNULLじゃない場合は、VAL_1の値をそのまま使う

ということです。ちょっと強引ですが、次の様なSQLを用意してみました。

--NVL再現クエリ①
SELECT
 CASE
  WHEN VAL_1 IS NULL THEN 'NULLだよ'
  WHEN VAL_1 IS NOT NULL THEN VAL_1
 END AS RESULT
FROM
 (SELECT NULL AS VAL_1
    FROM DUAL) S01
;
--NVL再現クエリ②
SELECT
 CASE
  WHEN VAL_1 IS NULL THEN 'NULLだよ'
  WHEN VAL_1 IS NOT NULL THEN VAL_1
 END AS RESULT
FROM
 (SELECT '値があるよ' AS VAL_1
    FROM DUAL) S01
;

FROM句にサブクエリS01を用意し、VAL_1カラムの値を、NVL再現クエリ①は強制的にNULL、NVL再現クエリ②は文字列にしています。これをそれぞれ実行してみてください。下図の様に、UNION ALLで一度に実行しても大丈夫です。