LNNVL

Purpose

You can call this function to provide 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 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.

Return types

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 (0.03 sec)

obclient> INSERT INTO tbl1 VALUES (1,30),(2,null),(3,50),(4,80),(5,10);
Query OK, 5 rows affected (0.00 sec)
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