DECODE, NULLIF, NVL, and NVL2 in Oracle

DECODE, NULLIF, NVL, and NVL2 in Oracle

 

Most of Oracle’s built-in functions are designed to solve a specific problem. Table below shows the syntax and logic equivalent for each of four functions.

 

Syntax	Logic equivalent
DECODE(V1, V2, V3, V4)	IF V1 = V2 THEN V3 ELSE V4
NULLIF(V1, V2)	IF V1 = V2 THEN NULL ELSE V1
NVL(V1, V2)	IF V1 IS NULL THEN V2 ELSE V1
NVL2(V1, V2, V3)	IF V1 IS NULL THEN V3 ELSE V2

 

1. DECODE

 

The DECODE function can be thought of as an inline IF statement. DECODE column, a literal, a function or event a subquery. Take a look with simple example using DECODE

SELECT FRIST_NAME, DECODE(MANAGER_ID, NULL, ‘STAFF’, ‘MANAGER’) AS TITLE

FROM EMPLOYEES;

 

2. NULLIF

 

The NULLIF function compares two ｅxpressions and returns NULL if the ｅxpressions are equivalent, or the first ｅxpression otherwise. The equivalent logic using DECODE as below

NULLIF(V1, V2) = DECODE(V1,V2, NULL, V1)

NULLIF is useful if you want to substitute NULL for a column”s value, as demonstrated by the next query, which shows salary for only those employees earn less than 100$

SELECT FIRST_NAME, NULLIF(SALARY, GREATST(1000, SALARY)) AS SALARY

FROM EMPLOYEES;

 

3. NVL and NVL2

 

Let’s look at the examples using NVL and NVL2

SELECT FRIST_NAME, NVL2(MANAGER_ID, ‘MANAGER‘, ‘STAFF’) AS TITLE

FROM EMPLOYEES;

SELECT FRIST_NAME, NVL(mng.FIRST_NAME, ‘NONE’) AS MANAGER

FROM EMPLOYEES emp LEFT OUTER JOIN EMPLOYEES mgr

ON emp.MANAGER_ID = mgr.EMPLOYEE_ID;

 

http://oratek4u.wordpress.com/2012/04/19/decode-nullif-nvl-and-nvl2-in-oracle/