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

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

NVL2はOracleのNULL比較関数 NULLの場合にNULL以外を返り値として返すことが可能

まずはnvlの実行結果を見てみましょう。第一引数がNULLなら第二引数を、NULL以外なら第一引数を返します。

  1. select nvl(NULL,1) from dual;
  2. NVL(NULL,1)
  3. -----
  4. 1
  5.  
  6. select nvl(2,1) from dual;
  7. NVL(2,1)
  8. -----
  9. 2

NVL2は、指定した値がNULLだった場合とNULL以外の場合で返す値を変えることができる関数です。第一引数がNULL以外なら第二引数を、NULLなら第三引数を返します。

  1. select nvl2(NULL,1,2) from dual;
  2. NVL2(NULL,1,2)
  3. -----
  4. 2
  5.  
  6. select nvl2(1,1,2) from dual;
  7. NVL2(1,1,2)
  8. -----
  9. 1

NVL2はCASE式で置き換えが可能

NVL2はOracleの独自拡張です。MySQLなど他のRDBMSに移植する場合は、CASE式で置き換えが可能です。

  1. //NVL()をcase式で置き換えた例
  2. SELECT CASE WHEN num IS NULL THEN 0 ELSE str END FROM emp;
  3.  
  4. //NVL2()をcase式で置き換えた例
  5. SELECT CASE WHEN num IS NULL THEN 0 ELSE num * 2 END FROM emp

NVL2は比較値以外の値を返すことが可能

以下のSQLは、データベースHR.employeesからmanager_idがNULLの場合は1を、NULL以外なら0を取得しています。

  1. select employee_ID, first_name, last_name, nvl2(manager_id, 0, 1) from HR.employees;
  2.  
  3. EMPLOYEE_ID	FIRST_NAME	LAST_NAME	NVL2(MANAGER_ID,0,1)
  4. ----------------------------
  5. 100	Steven	King	1
  6. 101	Neena	Kochhar	0
  7. 102	Lex	De Haan	0
  8. 103	Alexander	Hunold	0
  9. 104	Bruce	Ernst	0
  10. 105	David	Austin	0
  11. 106	Valli	Pataballa	0
  12. 107	Diana	Lorentz	0
  13. 108	Nancy	Greenberg	0
  14. 109	Daniel	Faviet	0
  15. 110	John	Chen	0
  16. 　：
  17. 　：

outer joinの場合対応する値がなくてNULL値になっているカラムを置き換えるには、以下の様にします。以下は、manager_idがNULLの場合はN/A、NULL以外の場合はavailableを返すサンプルです。

  1. select a.empno, a.deptno, a.ename, a.job, nvl2(b.deptno, 'available', 'N/A')
  2. from SCOTT.emp a, SCOTT.dept b
  3. where a.deptno = b.deptno (+);
  4.  
  5. EMPNO	DEPTNO	ENAME	JOB	NVL2(B.DEPTNO,'AVAILABLE','N/A')
  6. 7001	99	TARO	OBSERVER	N/A
  7. 7839	10	KING	PRESIDENT	available
  8. 7698	30	BLAKE	MANAGER	available
  9. 7782	10	CLARK	MANAGER	available
  10. 7566	20	JONES	MANAGER	available
  11. 7788	20	SCOTT	ANALYST	available
  12. 7902	20	FORD	ANALYST	available
  13. 7369	20	SMITH	CLERK	available
  14. 7499	30	ALLEN	SALESMAN	available
  15. 7521	30	WARD	SALESMAN	available
  16. 7654	30	MARTIN	SALESMAN	available
  17.  :
  18.  :

outer join先のSCOTT.deptテーブルのdeptnoがNULLの場合はN/Aを、NULL以外の場合はavailableを取得します。

NVL2とCOALESCEの違い COALSCEは複数の引数指定が可能

Oracleでは、NVL2の代わりにcoalesce関数を使うことも可能です。COALSCEは複数の引数を指定することができ、引数1がNULLだったら引数2を比較、引数2がNULLだったら、引数3を比較…と指定した引数を順番にNULL比較していきます。

比較した結果、NULL以外が現れた場合はその値を、全てNULLだった場合はNULLを返します。

  1. SELECT COALESCE(NULL,1) from dual;
  2. COALESCE(NULL,1)
  3. ----------
  4. 1
  5.  
  6. SELECT COALESCE(NULL,NULL,NULL) from dual;
  7. COALESCE(NULL,NULL,NULL)
  8. ----------
  9.  - 
  10.  
  11. SELECT COALESCE(NULL,NULL,NULL,99) from dual;
  12. COALESCE(NULL,NULL,NULL,99)
  13. ----------
  14. 99

NVL2の場合は、比較する値は一つであることと、比較した値以外を返すことができる点が異なります。NVL、NVL2はOracleの独自関数ですが、COALSCEはMySQLなどの他のデータベース管理システムでも利用可能です。

下記のSQLは、年ごとの社員の年収を集計するSQLです。年収がNULLの場合はCOALESCEで0に置き換えています。

  1. select Date_format(birth_date, '%Y') as year, 
  2.        Sum(COALESCE(salary,0)) 
  3. from   employees 
  4.        inner join salaries 
  5.                on salaries.emp_no = employees.emp_no 
  6.        inner join dept_emp 
  7.                on employees.emp_no = dept_emp.emp_no 
  8.        inner join departments 
  9.                on dept_emp.dept_no = departments.dept_no 
  10. where  salaries.to_date = '9999-01-01' 
  11.        and dept_name = 'Sales' 
  12. group  by Date_format(birth_date, '%Y')
  13.  
  14. +------+-------------------------+
  15. | year | Sum(COALESCE(salary,0)) |
  16. +------+-------------------------+
  17. | 1952 |               266268511 |
  18. | 1953 |               279889946 |
  19. | 1954 |               284458184 |
  20. | 1955 |               292812067 |
  21. | 1956 |               288620912 |
  22. | 1957 |               284076293 |
  23. | 1958 |               294605154 |
  24. | 1959 |               289944673 |
  25. | 1960 |               281743172 |
  26. | 1961 |               282715210 |
  27. | 1962 |               290871654 |
  28. | 1963 |               289909858 |
  29. | 1964 |               281566480 |
  30. | 1965 |                23888563 |
  31. +------+-------------------------+
  32. 14 rows in set (0.73 sec)

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

まとめ

ポテパンダの一言メモ
  • NVL2は、NULL比較してNULL以外の値を返すOracle独自の関数
  • 他のRDBMSでは、CASE文で置き換えが可能
  • NULL比較をおこなうCOALSCEとは指定できる引数の数と任意の値を返せる点が異なる

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

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

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

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

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

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

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

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

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

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

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