Oracle/PLSQL: LNNVL Function

In Oracle/PLSQL, the 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.

The syntax for the LNNVL function is:

LNNVL( condition )

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:

Oracle 10g, Oracle 11g

For example:

Let's take a 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.