Variants of NVL and Branching techniques in SQL

NVL

NVL gives you the flexibility to choose an alternate path if your current value is null.

For Example
Select NVL(dept,'Bench') from employees;

The above query will returns Bench if the dept is null for an employee

NVL2

NVL2 give you extended flexibility to choose your path if the value is null or not null

For Example
Select NVL2(dept,'Existing','New') from employees;

The above query returns Existing if dept is not null and New if the dept is null.

DECODE

Decode can be thought as a mapping/lookup. If the value matches to a code, a corresponding meaning is return.

For Example
Select DECODE(dept,
     'SALES','Department of Sales',
     'MKT','Department of Marketing',
     'MFG','Manufacturing',
     'Bench') from employees;

In the above query if dept is equal to SALES, Department of Sales if returned. If none of them matches, Bench is returned.

COALESCE

COALESCE function returns the first non null operand.

For Example
select COALESCE(NULL,last_name,first_name) from employee;

The above function will check the fisrt operand, Since it is null it will move to second operand.
If last_name is null,  first_name is returned
If last_name is not null, first_name will be returned.

NULLIF

Nullif returns null if two operands are equivalent,If they are not equivalent you can return a custom messge.

For example
select NULLIF(first_name,last_name) from employee;

If first_name is equal to last_name null is returned, else fist_name is returned.
It can be thought of as
select decode(first_name,last_name,NULL,first_name) from employee;