Oracle NULL Functions

Following are the impoertant oracle sql functions to handle NULLs.
1> NVL
The NVL function lets you substitute a value when a null value is encountered.

NVL( string, replace_with )

If String is NULL then return replace_with.

select NVL('a','b') from dual;
-- Return 'a'

select NVL(NULL,'b') from dual;
-- Return 'b'


2> NVL2
NVL2 extends functionality of NVL.

NVL2( string1, value_if_NOT_null, value_if_null )


3> NULLIF
NULLIF( val1, val2 )

IF val1 = val2 then return NULL
IF val1 != val2 then return val1

4> COALESCE

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

coalesce( expr1, expr2, ... expr_n )

Return first non NULL expression.

5> LNNVL

LNNVL function is used in the WHERE clause of an SQL statement to evaluate a condition when one of the operands may contain a NULL value.

select name, comm from emp
where LNNVL(comm >0)

name1 100
name2 200
name3 NULL

Same statement can be written as follows:

select name, comm from emp
where NVL(comm,100) > 0