Oracle LNNVL() Function

Oracle LNNVL() is a built-in function that provides a concise way to evaluate a condition when one or both operands are NULL. It returns FALSE if the condition is TRUE, and TRUE if the condition is FALSE or UNKNOWN.

LNNVL() can be used in the WHERE clause or WHEN condition of a CASE expression.

LNNVL() can be used anywhere a scalar expression can appear, especially in cases where IS [NOT] NULL, AND, or OR conditions are invalid but potential null values need to be considered.

Oracle LNNVL() Syntax

Here is the syntax for the Oracle LNNVL() function:

LNNVL(condition)

Parameters

condition

Required. A conditional expression.

Return Value

The Oracle LNNVL() function returns a Boolean value. It returns FALSE if the condition is TRUE, and TRUE if the condition is FALSE or UNKNOWN.

Oracle LNNVL() Examples

Here are some examples that demonstrate the usage of the Oracle LNNVL() function.

Basic Usage

Here, we construct temporary data using the UNION operator:

SELECT 1 n FROM dual
UNION ALL
SELECT 2 FROM dual
UNION ALL
SELECT 3 FROM dual
UNION ALL
SELECT NULL FROM dual;

Output:

      N
_______
      1
      2
      3
   NULL

Suppose you want to find numbers less than 3, including NULL. The following query only returns non-NULL numbers less than 3:

SELECT
  *
FROM (
  SELECT 1 n FROM dual
  UNION ALL
  SELECT 2 FROM dual
  UNION ALL
  SELECT 3 FROM dual
  UNION ALL
  SELECT NULL FROM dual
) t
WHERE n < 3;

Output:

   N
____
   1
   2

The Oracle LNNVL() function can help you achieve this:

SELECT
  *
FROM (
  SELECT 1 n FROM dual
  UNION ALL
  SELECT 2 FROM dual
  UNION ALL
  SELECT 3 FROM dual
  UNION ALL
  SELECT NULL FROM dual
) t
WHERE LNNVL(n >= 3);

Output:

      N
_______
      1
      2
   NULL

The following statement uses a CASE expression to explain how the above statement works:

SELECT
    t.n,
    CASE
        WHEN LNNVL(t.n >= 3) THEN 'TRUE'
        ELSE 'FALSE'
    END Result
FROM (
  SELECT 1 n FROM dual
  UNION ALL
  SELECT 2 FROM dual
  UNION ALL
  SELECT 3 FROM dual
  UNION ALL
  SELECT NULL FROM dual
) t;

Output:

      N RESULT
_______ _________
      1 TRUE
      2 TRUE
      3 FALSE
   NULL TRUE

Conclusion

The Oracle LNNVL() function is a built-in function that provides a simple way to evaluate a condition when one or both operands are NULL. It returns FALSE if the condition is TRUE, and TRUE if the condition is FALSE or UNKNOWN.