LNNVL Function in Oracle SQL – PLSQL

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.


Tagged , , , , , , , , , , , . Bookmark the permalink.