NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL

 LOGIC:

Function syntax	Logic equivalent
DECODE(E1, E2, E3, E4)	IF E1 = E2 THEN E3 ELSE E4
NULLIF(E1, E2)	IF E1 = E2 THEN NULL ELSE E1
NVL(E1, E2)	IF E1 IS NULL THEN E2 ELSE E1
NVL2(E1, E2, E3)	IF E1 IS NULL THEN E3 ELSE E2

Exaplanation:

NVL : This single row function use for Convert the  null value to an actual value.
NVL2 : If first expression is not null, return second expression. If first expression is null, return third expression. the first expression can have any data type.
COALESCE : Return first not null expression in the expression list.
NULLIF : Compares two expressions and returns null if they are equal,returns the first expression if they are not equal.



Create a test table :

CREATE TABLE T1_NULL_TEST (
  IDNO   NUMBER,
  NAME VARCHAR2(10),
  CNAME VARCHAR2(10), GNAME VARCHAR2(10),
  PNAME VARCHAR2(10)
);


INSERT INTO  T1_NULL_TEST values (1, 'ONE', 'TWO', 'THREE', 'FOUR');
INSERT INTO  T1_NULL_TEST values (2, NULL, 'TWO', 'THREE', 'FOUR');
INSERT INTO  T1_NULL_TEST values (3, NULL, NULL, 'THREE', 'FOUR');
INSERT INTO  T1_NULL_TEST values (4, NULL, NULL, 'THREE', 'THREE');
COMMIT;

SELECT * FROM  T1_NULL_TEST ORDER BY idno;








comparisons against null always result in null, so queries can't use regular comparison operators like "=" or "!=".
 SELECT * FROM  T1_NULL_TEST WHERE name = NULL ORDER BY idno;



SELECT * FROM  T1_NULL_TEST WHERE name IS NULL ORDER BY idno;

 NVL

The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
We know that CNAME in the test table contains null in all rows except the first. Using the NVL function we replace the null values with 'ZERO'.



SQL> select idno, nvl(cname, 'zero') as output from t1_null_test;

      IDNO OUTPUT
---------- ----------
         1 TWO
         2 TWO
         3 zero
         4 zero

DECODE

The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example.

SQL> select idno, decode(cname, null, 'zero', cname) as decode_output from t1_nu
ll_test;

      IDNO DECODE_OUT
---------- ----------
         1 TWO
         2 TWO
         3 zero
         4 zero

NVL2

The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.
The following query shows NVL2 in action.

SQL> select idno, NVL2(name,cname, pname) as NVL2_output from t1_null_test;

      IDNO NVL2_OUTPU
---------- ----------
         1 TWO
         2 FOUR
         3 FOUR
         4 THREE

COALESCE

The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

SQL> select idno, COALESCE(name,cname, pname) as COALESE_output from t1_null_tes
t;

      IDNO COALESE_OU
---------- ----------
         1 ONE
         2 TWO
         3 FOUR
         4 THREE

NULLIF

The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.
In our test table the values of CNAME and GNAME are equal in row 4, so we would only expect null returned for that row using the following query.

 SQL> select idno, NULLIF(cname, pname) as NVLif_output from t1_null_test;

      IDNO NVLIF_OUTP
---------- ----------
         1 TWO
         2 TWO
         3

LNNVL

The LNNVL function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.


SQL> select idno, cname from t1_null_test where LNNVL(name is null) order by IDN
O;

      IDNO CNAME
---------- ----------
         1 TWO

SQL> select idno, cname from t1_null_test where LNNVL(name='two') order by IDNO;


      IDNO CNAME
---------- ----------
         1 TWO
         2 TWO
         3
         4

NANVL

The NANVL function was introduced in Oracle 10g for use with the BINARY_FLOAT and BINARY_DOUBLE datatypes, which can contain a special "Not a Number" or "NaN" value. The function is similar to NVL, but rather than testing for null it tests for "NaN" values. The following table will be used to demonstrate it.



SQL> select idno, cname, nanvl(idno,0) from t1_null_test where LNNVL(name='two')
 order by IDNO;

      IDNO CNAME      NANVL(IDNO,0)
---------- ---------- -------------
         1 TWO                    1
         2 TWO                    2
         3                        3
         4                        4

SYS_OP_MAP_NONNULL

We have seen that a comparison of "NULL = NULL" will always return false, but sometimes you want it to return true. It is possible to make this happen using the NVL and DECODE functions, but this relies on you converting the null value to another value that you hope will never be present in the column or variable.

SQL> select IDNO, 'cname=pname' from t1_null_test where SYS_OP_MAP_NONNULL(name)
 = SYS_OP_MAP_NONNULL(cname);

      IDNO 'CNAME=PNAM
---------- -----------
         3 cname=pname
         4 cname=pname