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 |
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.