Purpose
This function provides a method for evaluating conditions when one or both operands may be null. It takes a condition as input and returns TRUE if the condition is FALSE or UNKNOWN, and FALSE if the condition is TRUE.
This function can be used in the WHERE clause or as a WHEN condition in a CASE expression.
Syntax
LNNVL (condition)
Parameters
condition is the condition to evaluate. Assume a is 2 and b is NULL. The table below shows the return values of the LNNVL function.
Condition |
Evaluation Result |
LNNVL Return Value |
|---|---|---|
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
Returns a boolean value of TRUE or FALSE.
Examples
Create a table named tbl1 and insert five rows of data. Query the col2 column for values less than 50, including those that are NULL.
obclient> CREATE TABLE tbl1 (col1 INT,col2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES (1,30),(2,null),(3,50),(4,80),(5,10);
Query OK, 5 rows affected
Records: 5 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM tbl1 WHERE LNNVL(col2 >= 50);
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | 30 |
| 2 | NULL |
| 5 | 10 |
+------+------+
3 rows in set