Oracle LNNVL Function: A Simple Guide

The LNNVL function in Oracle SQL is a special logical function. Its name stands for "Logical Not NULL-Value Logic," and it's used to simplify conditions that involve NULL values, which can be tricky.

The LNNVL function provides a concise way to evaluate a condition when one or both of the operands might be NULL. It returns TRUE if a condition is FALSE or UNKNOWN (which is what NULL comparisons evaluate to). It returns FALSE only if the condition is TRUE.

This is most useful in a WHERE clause to include rows where the condition is not TRUE.

What is the LNNVL Function in Oracle?

In Oracle, a comparison with NULL (like commission_pct < 0.2) doesn't return TRUE or FALSE; it returns UNKNOWN. This means a standard WHERE clause will filter out all those NULL rows.

LNNVL helps you "flip" this logic to include the NULLs.

The logic is:

• If the condition is TRUE, LNNVL returns FALSE.
• If the condition is FALSE, LNNVL returns TRUE.
• If the condition is UNKNOWN (i.e., NULL), LNNVL returns TRUE.

This is equivalent to (condition = FALSE) OR (condition = UNKNOWN).

LNNVL Function Syntax

The syntax for LNNVL is:

LNNVL(condition)

Let's break that down:

• condition: A single logical condition (e.g., salary > 50000, commission_pct >= .2). It cannot be a compound condition containing AND or OR.

Oracle LNNVL Function Examples

Here are two practical examples of how to use LNNVL.

Example 1: Understanding LNNVL Logic (Truth Table)

This example shows how LNNVL evaluates different conditions, especially those involving NULL.

Let's assume we have two variables: a = 2 and b = NULL.

Notice that LNNVL returns TRUE for both FALSE and UNKNOWN results.

Example 2: Including NULLs in a Query with LNNVL

This is the most common use case. You want to find all employees whose commission is less than 20% (< 0.2). This should also include employees who get no commission (where commission_pct is NULL).

A standard WHERE clause fails to include the NULLs:

-- This query is WRONG, it misses the NULLs
SELECT COUNT(*)
FROM employees
WHERE commission_pct < .2;

-- Result: 11

We can fix this by using LNNVL to find all rows that are not "greater than or equal to .2". This will include NULLs (which are UNKNOWN) and those that are FALSE (less than .2).

Query:

-- This query is CORRECT and includes NULLs
SELECT COUNT(*)
FROM employees
WHERE LNNVL(commission_pct >= .2);

Result: (This result correctly includes all employees.)

  COUNT(*)
----------
        83