LNNVL Function in Oracle SQL

The LNNVL function in Oracle SQL / PLSQL is used in the WHERE Clause of an SQL / PLSQL SELECT Statement. LNNVL Function is used to evaluate a condition where one of the operands of the condition may have NULL value.

Syntax for the LNNVL function in Oracle SQL / PLSQL is:

SELECT column(s)
FROM table_name
WHERE LNNVL (condition);

LNNVL Returns the values based on the table below:

Condition_Evaluation_Value	LNNVL Return
TRUE	FALSE
FALSE	TRUE
UNKNOWN	TRUE

Example:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

If we write our query as:

 SELECT *
FROM employee
WHERE commission < 15;

We will get the following output:

Employee_ID	Employee_Name	Salary	Department	Commission
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	5

Suppose we wish to see the records where commission is less than equal to 15 and also could be NULL, then we can achieve the same using the LNNVL function as:

 SELECT *
FROM employee
WHERE LNNVL(commission >= 15);

We will get the following output:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

In the above fetched results observe that the records for NULL commission are also included.