NVLとNVL2の使い方【ORACLE MASTER Silver SQL試験勉強】

Ping-tで問題集解いてたら初見の関数があったため、使い方をまとめました。

NVL・NVL2はMySQLなどでは使えない関数なので、ブラウザでOracleのSQLを実行できるlivesql.oracle.comで試しています。

この記事で分かること

簡潔にまとめると、NVLとNVL2は以下の役割りがあります。

NVL	「NULLのときだけ」別の値に変える ※1パターン分岐式：NVL(対象の列または値, NULLのときの置き換え値)
NVL2	「NULLじゃないとき」と「NULLのとき」の両方の戻り値を自由に指定する ※2パターン分岐式：NVL2(対象の列または値, NULLじゃないときに返す値, NULLのときに返す値)

使用するデータ

前提として、前述のlivesql.oracle.comに元々あったHR.DEPARTMENTSテーブルを活用します。

いい感じに「MANAGER_ID」にnullが入ってるので、これで検証していきます。

それにしてもMANAGER_IDがnullってことは、その部署はマネージャーいないんでしょうか。

NVLは、「指定した値がNULLの場合に、別の代替値に置き換える」関数です。

式は以下。

NVL(対象の列または値, NULLのときの置き換え値)

「MANAGER_IDがnullなら’未設定’の文字列を入れたいな～」となった場合に、NVL関数が使えます。

実際に書いたクエリは以下です。

SELECT 
    department_name AS 部署名,
    manager_id AS 元のID,
    NVL(TO_CHAR(manager_id), '未設定') AS NVL結果
FROM 
    hr.departments;

結果は以下。

元のIDで(null)になっていたところが、ちゃんと「未設定」になっています。

TO_CHAR()は、数値型などを文字型に変換する関数です。

今回、MANAGER_IDがNUMBER型（数値型）なので、それを文字列に変換しています。

なんでわざわざ変換してるのってところですが、これは

NVL関数には「第1引数と第2引数のデータ型を同じにしなければならない」という厳格なルールがある

からです。

つまり、TO_CHAR()で型を変換しないと、「未設定」は文字型なのにMANAGER_IDは数値型だ！エラー！という結果になってしまいます。

NVL2は、「指定した値がNULLじゃない場合、NULLの場合でそれぞれ値を返す」関数です。

式は以下。

NVL2(対象の列または値, NULLじゃないときに返す値, NULLのときに返す値)

「MANAGER_IDがnullじゃないなら’配置済み’、nullなら’不在’の文字列を入れたいな～」となった場合に、NVL2関数が使えます。

実際に書いたクエリは以下です。

SELECT 
    department_name AS 部署名,
    manager_id AS 元のID,
    NVL2(manager_id, '配置済み', '不在') AS マネージャー設置状況
FROM 
    hr.departments;

結果は以下。

元のIDに値が入っていたレコードは「配置済み」、(null)だったところは「不在」になっていますね！

NVLではTO_CHAR()使ったのにこっちは使わないの？と思った人用の解説です。

NVL2では、「第2引数と第3引数のデータ型を同じにしなければならない」というルールがあります。第1引数の型は何でもいいです。

つまり、MANAGER_IDが数値型であってもNVL2関数の動作に影響はありません。大切なのは、第2引数と第3引数の型が合っているかどうかです！

例題では第2引数は’配置済み’、そして第3引数は’不在’でした。どちらも文字型です。

仮にこれがNVL2(manager_id, 999, ‘不在’)とかだとエラーになります。

NVL2は第3引数の型を、第2引数の型に合わせようとします。
この例だと、第2引数が「999（数値）」なので、第3引数の「’不在’（文字）」も数値に変換しようとして失敗し、invalid string value（＝数値に変換できない無効な文字列だよ！）と怒られてしまいます。

リード文で記述した表の再掲です！

NVL	「NULLのときだけ」別の値に変える ※1パターン分岐式：NVL(対象の列または値, NULLのときの置き換え値)
NVL2	「NULLじゃないとき」と「NULLのとき」の両方の戻り値を自由に指定する ※2パターン分岐式：NVL2(対象の列または値, NULLじゃないときに返す値, NULLのときに返す値)

以上、「NVL = Null Valueの略」の通り、null値をどう扱うか？という機能の解説でした。