SQLのNVL2関数についてまとめています。
NVL2はOracleのNULL比較関数 NULLの場合にNULL以外を返り値として返すことが可能
まずはnvlの実行結果を見てみましょう。第一引数がNULLなら第二引数を、NULL以外なら第一引数を返します。
select nvl(NULL,1) from dual; NVL(NULL,1) ----- 1 select nvl(2,1) from dual; NVL(2,1) ----- 2
NVL2は、指定した値がNULLだった場合とNULL以外の場合で返す値を変えることができる関数です。第一引数がNULL以外なら第二引数を、NULLなら第三引数を返します。
select nvl2(NULL,1,2) from dual; NVL2(NULL,1,2) ----- 2 select nvl2(1,1,2) from dual; NVL2(1,1,2) ----- 1
NVL2はCASE式で置き換えが可能
NVL2はOracleの独自拡張です。MySQLなど他のRDBMSに移植する場合は、CASE式で置き換えが可能です。
//NVL()をcase式で置き換えた例 SELECT CASE WHEN num IS NULL THEN 0 ELSE str END FROM emp; //NVL2()をcase式で置き換えた例 SELECT CASE WHEN num IS NULL THEN 0 ELSE num * 2 END FROM emp
NVL2は比較値以外の値を返すことが可能
以下のSQLは、データベースHR.employeesからmanager_idがNULLの場合は1を、NULL以外なら0を取得しています。
select employee_ID, first_name, last_name, nvl2(manager_id, 0, 1) from HR.employees; EMPLOYEE_ID FIRST_NAME LAST_NAME NVL2(MANAGER_ID,0,1) ---------------------------- 100 Steven King 1 101 Neena Kochhar 0 102 Lex De Haan 0 103 Alexander Hunold 0 104 Bruce Ernst 0 105 David Austin 0 106 Valli Pataballa 0 107 Diana Lorentz 0 108 Nancy Greenberg 0 109 Daniel Faviet 0 110 John Chen 0 ： ：
outer joinの場合対応する値がなくてNULL値になっているカラムを置き換えるには、以下の様にします。以下は、manager_idがNULLの場合はN/A、NULL以外の場合はavailableを返すサンプルです。
select a.empno, a.deptno, a.ename, a.job, nvl2(b.deptno, 'available', 'N/A') from SCOTT.emp a, SCOTT.dept b where a.deptno = b.deptno (+); EMPNO DEPTNO ENAME JOB NVL2(B.DEPTNO,'AVAILABLE','N/A') 7001 99 TARO OBSERVER N/A 7839 10 KING PRESIDENT available 7698 30 BLAKE MANAGER available 7782 10 CLARK MANAGER available 7566 20 JONES MANAGER available 7788 20 SCOTT ANALYST available 7902 20 FORD ANALYST available 7369 20 SMITH CLERK available 7499 30 ALLEN SALESMAN available 7521 30 WARD SALESMAN available 7654 30 MARTIN SALESMAN available : :
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を返します。
SELECT COALESCE(NULL,1) from dual; COALESCE(NULL,1) ---------- 1 SELECT COALESCE(NULL,NULL,NULL) from dual; COALESCE(NULL,NULL,NULL) ---------- - SELECT COALESCE(NULL,NULL,NULL,99) from dual; COALESCE(NULL,NULL,NULL,99) ---------- 99
NVL2の場合は、比較する値は一つであることと、比較した値以外を返すことができる点が異なります。NVL、NVL2はOracleの独自関数ですが、COALSCEはMySQLなどの他のデータベース管理システムでも利用可能です。
下記のSQLは、年ごとの社員の年収を集計するSQLです。年収がNULLの場合はCOALESCEで0に置き換えています。
select Date_format(birth_date, '%Y') as year,
Sum(COALESCE(salary,0))
from employees
inner join salaries
on salaries.emp_no = employees.emp_no
inner join dept_emp
on employees.emp_no = dept_emp.emp_no
inner join departments
on dept_emp.dept_no = departments.dept_no
where salaries.to_date = '9999-01-01'
and dept_name = 'Sales'
group by Date_format(birth_date, '%Y')
+------+-------------------------+
| year | Sum(COALESCE(salary,0)) |
+------+-------------------------+
| 1952 | 266268511 |
| 1953 | 279889946 |
| 1954 | 284458184 |
| 1955 | 292812067 |
| 1956 | 288620912 |
| 1957 | 284076293 |
| 1958 | 294605154 |
| 1959 | 289944673 |
| 1960 | 281743172 |
| 1961 | 282715210 |
| 1962 | 290871654 |
| 1963 | 289909858 |
| 1964 | 281566480 |
| 1965 | 23888563 |
+------+-------------------------+
14 rows in set (0.73 sec)
【関連記事】
▶SQL coalesce 値がNULLの場合の代替値を返す 簡易版のNVLやNULLIFが使えるDBもあり
まとめ
- NVL2は、NULL比較してNULL以外の値を返すOracle独自の関数
- 他のRDBMSでは、CASE文で置き換えが可能
- NULL比較をおこなうCOALSCEとは指定できる引数の数と任意の値を返せる点が異なる
