totn Oracle Functions

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:

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.