Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

OracleのSQLのNVL関数についてまとめています。

SQLのNVLの構文

NVLはOracle独自の関数で、NULLを文字列に置換して問い合わせ結果に含めることが可能です。第一引数がNULLの場合、第2引数を戻します。NULLでない場合は、第一引数を戻します。

  1. SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable') commission
  2.   FROM employees
  3.   WHERE last_name LIKE 'B%'
  4.   ORDER BY last_name;
  5.  
  6. LAST_NAME                 COMMISSION
  7. ------------------------- ----------------------------------------
  8. Baer                      Not Applicable
  9. Baida                     Not Applicable
  10. Banda                     .1
  11. Bates                     .15
  12. Bell                      Not Applicable
  13. Bernstein                 .25
  14. Bissot                    Not Applicable
  15. Bloom                     .2
  16. Bull                      Not Applicable

【関連記事】
▶【ハンズオン】SQLのNVL関数とは？概要や使い方を丁寧に説明！ | 「ポテパンスタイル」

値の比較をする場合、対象がNULLだと様々な例外処理が必要になってくるため、NVLで空文字（からもじ）などに変換したほうが、処理をシンプルにできるんですね。

ただし、NVLは主要DBMSの中ではOracleでしか利用できないため、他のDBMSにSQLを流用等する場合は、coalsceやIFNULL（MySQL）、case式等で置き換える必要があります。

OracleのNVLのリファレンスマニュアル

Oracleのnvlの引数は、任意のデータ型を使用できますが、データ型が異なる場合は暗黙的にデータ型変換されます。

関連）Oracle公式ドキュメント：NVL

なお、nvlはCOALESCEやCASE式での代替が可能です。

Oracleにnvlが用意されているのは、引数２つでシンプルにNULL代替を記述できるという点だと思います。スッキリ記述して、SQLコードの可読性をあげることができるんですね。

代替のCOALESCEは単語自体が長く直感的にコードの意味がわかりにくいです。CASE式は都度記述しているとコードが冗長になってしまいます。

NVLは、Null Value Logicの略で、単語も短く、パッと見てわかりやすいのがメリットです。

NVLをcoalesceで代替する例

coalesceでNVLを書き換えてみましょう。

  1. select nvl(VAL_1,'') from table1;

以下のようになります。単にnvlをcoalsceに書き換えるだけで、引数の数も順序も同じです。

  1. select coalsce(VAL_1,'') from table1;

ただし、coalsceは複数の引数を指定することができ、引数を最初からNULL判定して、NULLでないものを返り値として返すんですね。

以下のケースでは、list_price（定価）の0.9倍、min_price（最低価格）ともにNULLだった場合には5を返り値として返します。

  1. SELECT product_id, list_price, min_price,
  2.        COALESCE(0.9*list_price, min_price, 5) "Sale"
  3.   FROM product_information
  4.   WHERE supplier_id = 102050
  5.   ORDER BY product_id;
  6.  
  7. PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
  8. ---------- ---------- ---------- ----------
  9.       1769         48                  43.2
  10.       1770                    73         73
  11.       2378        305        247      274.5
  12.       2382        850        731        765
  13.       3355                                5

【関連記事】
▶SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり 

第一引数がNULLなら第二引数、第二引数がNULLなら第三引数…と複数の値を指定する場合にはNVLでは対応できませんし、case式だと長くなりすぎます。

上記のような場合のNULL代替処理は、coalesceが最適と言えるでしょう。

NVLをcase式で代替する例

NVLをcase式で書き換えてみましょう。以下のSQLは、table1のカラムVAL_1を抽出する例です。VAL_1がNULLの場合は空文字として抽出します。

  1. select nvl(VAL_1,'') from table1;

CASE式で書き換えるとこうなります。

  1. select CASE
  2.  WHEN VAL_1 IS NULL THEN ''
  3.  WHEN VAL_1 IS NOT NULL THEN VAL_1
  4. END
  5. from table1;

関連）SQLのCASE式サンプル集 order byやgroup byとの組み合わせもバッチリ

単にNULL代替させるだけならcase式だとコードが冗長になってしまします。

しかし、case式だとNULL判定以外にも複雑な条件を組み込めるんですね。プログラム的なロジックを組み込むように、複数の値の判定や別の値の参照が可能です。複雑な処理を組み込みたい場合は、NVLよりもcase式が適していると言えるでしょう。

NVLとNVL2の違い

Oracleには、NVL2という関数が用意されています。NVLとの違いは、指定した値がNULL以外のときに、別の値を返すことができる点です。

  1. select nvl2(VAL_1,1,'') from table1;

上記のように記述すると、こうなります。

まとめ

ポテパンダの一言メモ
  • NVLは、NULLの代替値を返すOracle独自の関数
  • Oracle以外では、coalsceやcase式で置き換えることが可能
  • NVL2は、指定した値がNULL以外のとき、別の値を返すことができる

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。