Oracle NVL function

NVL function can be used to substitute a value when null in encountered. It has the following syntax:


If string has a value, it will be displayed. If the string is null, the replacement string will be displayed.

select NVL(salary,'unpaid volunteer') from staff

This query prints staff salaries. Volunteers do not have a salary so the value for their salary is null. In the query, when the salary is null, replacement string 'unpaid volunteer' is printed.

select NVL(salary,monthly_invoice) from worker

A paid worker can be an employee or a contractor. An employee receives a monthly salary and a contractor invoices his hours of work at the end of the month. In this query, if the salary is not null, the salary is printed. If the salary is null, the monthly_invoice is printed.

select distinct id, NVL((select distinct 'Y' from table1 where x = y), 'N') active where c = d      

NVL function can also be used in a subquery as in the example above