Oracle / PLSQL: LNNVL Function

This article is written about 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:

Condition Evaluates To LNNVL Return Value

TRUE FALSE

FALSE TRUE

UNKNOWN TRUE

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:

Condition Condition Evaluates To LNNVL Return Value

qty = reorder_level UNKNOWN TRUE

qty IS NULL FALSE TRUE

reorder_level IS NULL TRUE FALSE

qty = 20 TRUE FALSE

reorder_level = 20 UNKNOWN TRUE

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:

PRODUCT_ID QTY REORDER_LEVEL

1000 20 NULL

2000 15 8

3000 8 10

4000 12 6

5000 2 2

6000 4 5

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:

PRODUCT_ID QTY REORDER_LEVEL

3000 8 10

6000 4 5

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:

PRODUCT_ID QTY REORDER_LEVEL

1000 20 NULL

3000 8 10

6000 4 5

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