Oracle / PLSQL: LNNVL Function

This Oracle tutorial explains how to use the Oracle/PLSQL LNNVL function with syntax and examples.

Description

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

Syntax

The syntax for the LNNVL function in Oracle/PLSQL is:

LNNVL( condition )

Returns

The LNNVL function will return the following:

So if we had two columns called qty and reorder_level where qty = 20 and reorder_level IS NULL, the LNNVL function would return the following:

Applies To

The LNNVL function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g

Example

The LNNVL function can be used in Oracle/PLSQL.

Let's look at an example. If we had an products table that contained the following data:

And we wanted to find all of the products whose qty was below their respective reorder levels, we would run the following SQL statement:

select * from products
where qty < reorder_level;

This would return the following result:

However, if we wanted to see the products that were below their reorder levels as well as NULL reorder levels, we would use the LNNVL function as follows:

select * from products
where LNNVL(qty >= reorder_level);

This would return the following result:

In this example, the result set also contains the product_id of 1000 which has a NULL reorder level.