Purpose
LNNVL() provides a way to evaluate a condition when one or both operands of the condition may be NULL. The function takes a condition as an argument and returns TRUE if the condition is FALSE or UNKNOWN and returns FALSE if the condition is TRUE.
It is used in a WHERE clause or as a WHEN condition in a CASE expression.
Syntax
LNNVL(condition)
Parameters
condition specifies the judgment condition. Assume that a = 2 and b is NULL, the following table shows the values returned by the LNNVL() function.
| Condition | True/False 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
The function returns a Boolean type value: TRUE or FALSE.
Examples
Execute the following statement to create table tbl1 and insert five data entries into it. Query for values smaller than 50 and NULLs in the col2 column.
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
