All Products
Search
Document Center

LNNVL

Last Updated: Jun 18, 2021

The LNNVL function determines whether one or two operands in a condition are NULL. You can use this function in a WHERE clause or use the function as the WHEN condition in a CASE expression. A condition is used as a parameter. If the condition is FALSE or UNKNOWN, TRUE is returned. If the condition is TRUE, FALSE is returned.

Syntax

LNNVL(condition)

Parameters

Parameter

Description

condition

The condition.

Assume that a is equal to 2 and the value of b is NULL. The following table lists the return values of the LNNVL function.

Condition

Check result of the condition

Return value of LNNVL

a = 1

FALSE

TRUE

a = 2

TRUE

FALSE

a IS NULL

FALSE

TRUE

b = 1

UNKNOWN

TRUE

b IS NULL

TRUE

FALSE

a = b

UNKNOWN

TRUE

Return type

TRUE or FALSE of the BOOLEAN type is returned.

Examples

Assume that data is inserted into the employee name column name and the commission column commission_pct in an EMPLOYEES table. Execute the following statements:

CREATE TABLE EMPLOYEES (name VARCHAR(20), commission_pct numeric);
INSERT INTO EMPLOYEEs VALUES ('Baer', null);
INSERT INTO EMPLOYEEs VALUES ('Bada', null);
INSERT INTO EMPLOYEEs VALUES ('Boll', 0.1);
INSERT INTO EMPLOYEEs VALUES ('Bates', 0.15);
INSERT INTO EMPLOYEEs VALUES ('Eros', null);
INSERT INTO EMPLOYEEs VALUES ('Girl', 0.25);

You want to know the number of employees whose commission rates are less than 20%, including the employees who do not receive commissions. You can query only the number of employees whose actual commission rates are less than 20% by executing the following statement:

SELECT COUNT(*) FROM employees WHERE commission_pct < .2;

The following query result is returned:

+------------+
|  COUNT(*)  |
+------------+
|        2   |
+------------+

To include another three employees who do not receive commissions, you must rewrite the query by using the INNVL function. Execute the following statement:

SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

The following query result is returned:

+------------+
|  COUNT(*)  |
+------------+
|      4     |
+------------+