FB

SQL NVL, ISNULL, and Other Logical Function Guide, FAQ, and Examples

There are several functions in SQL, such as NVL and ISNULL, that perform some kind of logic on the values that are provided. Learn what they all do and see some examples in this article.

SQL Logical Functions in This Guide

In this article, we’ll look at a few logical functions in Oracle:

  • NVL
  • NVL2
  • LNNVL
  • NANVL
  • ISNULL

 

Purpose of the SQL Logical Functions

Each of these functions has a different purpose and exists in different databases.

NVL (Oracle)

The Oracle NVL function allows you to use a different value when a NULL or empty value is found.

Why would this be useful?

It’s good for translating data back to the users or systems that access it. It’s also good for aggregate functions and grouping, so you can actually see what a NULL value is.

ISNULL (SQL Server, MySQL)

This function is the same as NVL: it allows you to use a different value if NULL or an empty value is found.

ISNULL exists in SQL Server and MySQL, where NVL is only in Oracle.

NVL2 (Oracle)

The NVL2 function is similar to the NVL function. NVL2 allows you to specify a value to check, and then specify a value to use if it is null, as well as a value to use if it is not null.

It’s similar to NVL because NVL allows you to check a value and return something else if it is null. NVL2 has three parameters, while NVL has two.

LNNVL (Oracle)

The purpose of the LNNVL is to perform a “logical not null” on the expression that has been provided.

It allows you to evaluate data that contains a NULL value, which is something you can’t easily do within a single WHERE condition.

NANVL (Oracle)

The NANVL function is used to return a different value if the specified value is NaN (not a number). It’s useful for floating-point numbers such as BINARY_DOUBLE and BINARY_FLOAT.

 

Oracle NVL Function Syntax and Parameters

The NVL function is structured like this:

NVL( check_value, replace_value )

The parameters of the NVL function are:

  • check_value (mandatory): This is the value that is displayed to the user. It is also the value that is checked for NULL.
  • replace_value (mandatory): This is the value that appears if the check_value is NULL.

So, first of all, the check_value value is checked to see if it is NULL. If it is not NULL, it is returned by the function. If it is NULL, the replace_value is returned by the function.

If the replace_value is NULL, it is still returned. This can often happen if you use another column as the replace_value instead of a static value or string.

The Oracle NVL function can use string, date, and number values, for both the check_value and replace_value. However, they both need to be the same type. If a string is used for the check_value, a string also needs to be used for the replace_value.

 

SQL ISNULL Function Syntax and Parameters

The ISNULL function syntax is different in MySQL and SQL Server

MySQL

In MySQL, it has one parameter:

ISNULL (input_value)

The parameters of the ISNULL function are:

  • input_value (mandatory): This is the value to check to see if it is NULL or not.

The ISNULL function returns 1 if the value is NULL, and 0 if it is not NULL.

SQL Server

In SQL Server, it has two parameters:

ISNULL (input_value, replace_value)

The parameters of the ISNULL function are:

  • input_value (mandatory): This is the value to check to see if it is NULL or not.
  • replace_value (mandatory): This is the value to show if the input_value is NULL.

The ISNULL function returns the replace_value if the input_value is NULL, and returns input_value if it is not NULL.

 

Oracle NVL2 Function Syntax and Parameters

The syntax of the Oracle NVL2 function is:

NVL2( value_to_check, value_if_not_null, value_if_null )

The parameters of the NVL2 function are:

  • value_to_check (mandatory): This is the value to check to see if it is NULL or not.
  • value_if_not_null (mandatory): If value_to_check is not null, then NVL2 returns this value.
  • value_if_null (mandatory): If value_to_check is null, then NVL2 returns this value.

Some notes about this function:

  • These parameters are called different names by Oracle in their NVL2 function definition. The value_to_check is called expr1, the value_if_not_null is called expr2, and the value_if_null is called expr3.
  • The value_to_check parameter can have any data type.
  • The value_if_not_null and value_if_null can have any data type except LONG.
  • The data type of value_to_check determines the output.
  • If value_if_not_null  is a character type, then value_if_null is converted to the data type of value_if_not_null before comparing, unless value_if_null is a NULL constant.
  • If value_if_not_null  is numeric, then Oracle determines the type with the higher precedence.

 

Oracle LNNVL Function Syntax and Parameters

The syntax of the LNNVL function is:

LNNVL (condition)

The parameters of the LNNVL function are:

  • condition (mandatory): This is the condition to evaluate as TRUE or FALSE.

 

Oracle NANVL Function Syntax and Parameters

The syntax of the NANVL function is:

NANVL ( input_value, replace_with )

The parameters of the Oracle NANVL function are:

  • input_value (mandatory): This is the value to be checked to see if it is NaN.
  • replace_with (mandatory): If the input_value is NaN, then this value is returned. Otherwise, the input_value is returned.

The NANVL function can take any numeric data type as an input.

 

Oracle NVL2, NVL, CASE, DECODE, or COALESCE?

Why would you use NVL2 instead of NVL, CASE, DECODE, or COALESCE?

Well, there seems to be a lot of functions that you can use to get the same result.

  • NVL – Checks one value and returns it, if it is null, return another value
  • NVL2 – Checks one value, if it is null, return a different value, if it is not null, return another different value
  • CASE – Allows for IF-THEN-ELSE statements in SQL
  • DECODE – Allows for IF-THEN-ELSE statements in SQL.
  • COALESCE – Returns the first non-null expression in your list

So, it depends on what you need the function for.

In my article on the Oracle DECODE function, I recommend that you use CASE instead of DECODE for several reasons.

  • If you need just a simple NULL comparison to show a different value, use NVL.
  • NVL2 is an Oracle-only function, so this is something you may want to keep in mind.
  • COALESCE is a standard SQL function, and also has a bit more functionality.

So, your choice will depend on what you need the function for and how much you need it to do.

For NVL2 vs CASE, I would use NVL2 only if I was checking for NULL. If it was something more complicated, I would use CASE.

For NVL2 vs DECODE, I would also use NVL2 only if I was checking for NULL, as with CASE. I would also use CASE instead of DECODE if possible.

 

Can You Use Oracle NVL Function in WHERE Clause?

Well, yes, you can use the NVL function in a WHERE clause. However, it’s generally not a good idea.

NVL functions are used to check if one value is NULL and return another. The same functionality could be done with a JOIN, and is easier to understand.

Also, if two NVLs are used (for example, WHERE NVL() = NVL()) then it could give undesired results, as a NULL on the left does not equal a NULL on the right.

 

Can You Use Oracle NVL Function For Date Values?

Yes, you can, as long as both the check_value and replace_value are the same. You may need to use a TO_DATE function if you’re specifying another date to return if the first parameter is NULL. See the examples below for how this is done.

 

How Does The Oracle LNNVL Function Work?

The LNNVL function returns either TRUE or FALSE. This table demonstrates how the return value is calculated.

Let’s use our student table, which has a value of fees_required and fees_paid. Let’s say the values for a row are:

fees_required: 1000

fees_paid: NULL

Condition Is condition true? LNNVL Returns
fees_required=1000 TRUE FALSE
fees_required = 500 FALSE TRUE
fees_required IS NULL FALSE TRUE
fees_paid = 100 UNKNOWN TRUE
fees_paid IS NULL TRUE FALSE
fees_required = fees_paid UNKNOWN TRUE

You can see that it kind of gets the opposite of what is provided to it. If the condition returns TRUE, LNNVL returns FALSE.

The only exception is when null values are considered.

 

Why Would I Use the LNNVL Function?

The main reason to use the LNNVL is if you have data that contains NULL values, and you want to use the NULL value in your logic.

Let’s say we have this student table here.

FIRST_NAME LAST_NAME FEES_PAID
John Smith 100
Susan Johnson 150
Tom Capper 320
Mark Holloway 410
Steven Webber 80
Julie Armstrong 0
Michelle Randall (null)
Andrew Cooper 400
Robert Pickering 100
Tanya Hall 150
Jarrad Winston 300
Mary Taylor 100
Mark Anderson 45
John Rogers 700

You can see there is one record with a fees_paid value of NULL. You might want to query this table to find the names of students who have paid less than 100 of fees_paid.

Your query might look like this:

SELECT first_name, last_name, fees_paid
FROM student
WHERE fees_paid < 100;

Result:

FIRST_NAME LAST_NAME FEES_PAID
Steven Webber 80
Julie Armstrong 0
Mark Anderson 45

You can see that your results include a few rows, but don’t include the row where fees_paid is null. This is because NULL is not evaluated as a value.

But, what if you want NULL treated like another value?

You could use NVL. But, sometimes you may not want to use NVL.

You can use LNNVL.

If you want to see all rows where the fees_paid is less than 100, and including those where fees_paid is NULL, you can use this query:

SELECT first_name, last_name, fees_paid
FROM student
WHERE LNNVL(fees_paid >= 100);

Result

FIRST_NAME LAST_NAME FEES_PAID
Steven Webber 80
Julie Armstrong 0
Michelle Randall (null)
Mark Anderson 45

You can see it now shows the records where fees_paid is NULL.

So, that’s a situation where you might need to use the LNNVL function.

 

Examples of the NVL Function

Here are some examples of the NVL function. I find that examples are the best way for me to learn about code, even with the explanation above.

The values in red indicate those that have been translated from NULL, so you can see where the impacted values are.

 

Example 1 – NVL with String

SELECT
first_name, last_name, country,
NVL(country, 'No country') AS country_val
FROM customers;
FIRST_NAME LAST_NAME COUNTRY COUNTRY_VAL
John Smith USA USA
Sally Jones USA USA
Steve Brown Canada Canada
Mark Allan UK UK
Adam Cooper USA USA
Josh Thompson No country
Peter Manson France France

This example uses a string value for both parameters. Notice how the name of the last column has been set to the function name, which isn’t ideal. This can be changed, though.

 

Example 2 – NVL with Number

SELECT
first_name, last_name, employees,
NVL(employees, 0) AS "Fixed Employees"
FROM customers;
FIRST_NAME LAST_NAME EMPLOYEES Fixed Employees
John Smith 4 4
Sally Jones 10 10
Steve Brown 15 15
Mark Allan 23 23
Adam Cooper 55 55
Josh Thompson 1 1
Peter Manson 0

This example uses the NVL function with a number. It sets the value of 0 if a NULL value is found for employees. Notice how the name of the column has been updated as well, to “Fixed Employees”.

 

Example 3 – With Dates

SELECT first_name,
last_name,
start_date,
NVL(start_date, TO_DATE('01-JAN-2000')) AS "Fixed Start Date"
FROM customers;
FIRST_NAME LAST_NAME START_DATE Fixed Start Date
John Smith 12-Apr-10 12-Apr-10
Sally Jones 4-Jul-11 4-Jul-11
Steve Brown 21-Mar-09 21-Mar-09
Mark Allan 1-Feb-01 1-Feb-01
Adam Cooper 1-Jan-00
Josh Thompson 10-Feb-12 10-Feb-12
Peter Manson 16-Oct-12 16-Oct-12

This example uses the NVL function with date values. Notice how the TO_DATE needs to be used, as I have specified a date manually, but it needs to be converted from string to date.

 

Example 4 – Another Column Returned

SELECT
first_name, last_name, country,
NVL(country, last_name) AS country_ln
FROM customers;
FIRST_NAME LAST_NAME COUNTRY COUNTRY_LN
John Smith USA USA
Sally Jones USA USA
Steve Brown Canada Canada
Mark Allan UK UK
Adam Cooper USA USA
Josh Thompson Thompson
Peter Manson France France

This example shows you can return another column if the first parameter is NULL, and not just a specific value. I’ve returned the last_name if the country is null, which might not make sense in the real world, but this is just an example.

 

Example 5 – Multiple Columns

SELECT first_name, last_name,
NVL(country, 'No country') "Country",
NVL(employees, 0) "Employees",
NVL(start_date, TO_DATE('01-JAN-2000')) "Start Date"
FROM customers;
FIRST_NAME LAST_NAME Country Employees Start Date
John Smith USA 4 12-Apr-10
Sally Jones USA 10 4-Jul-11
Steve Brown Canada 15 21-Mar-09
Mark Allan UK 23 1-Feb-01
Adam Cooper USA 55 1-Jan-00
Josh Thompson No country 1 10-Feb-12
Peter Manson France 0 16-Oct-12

This example uses multiple NVL functions on multiple columns, and they have all been renamed.

 

Examples of the NVL2 Function

Here are some examples of the Oracle NVL2 function to help explain how to use the NVL2 function in Oracle SQL. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example shows the basic usage of the NVL2 function.

SELECT first_name,
NVL2(first_name, first_name, 'No name provided') AS First_Name_Check
FROM customers;

Result:

FIRST_NAME FIRST_NAME_CHECK
John John
Sally Sally
Steve Steve
Mark Mark
Adam Adam
Josh Josh
Peter Peter
(null) No name provided

All of the records have the first_name showing, except for the last record, where it has been replaced with the specified value.

 

Example 2

This example shows a similar example, but we’ve specified NULL as the third parameter, to see what happens.

SELECT first_name,
NVL2(first_name, first_name, NULL) AS First_Name_Check
FROM customers;

Result:

FIRST_NAME FIRST_NAME_CHECK
John John
Sally Sally
Steve Steve
Mark Mark
Adam Adam
Josh Josh
Peter Peter
(null) (null)

This output shows NULL being used to replace the NULL value. No error is shown, it just doesn’t change the data.

 

Example 3

This example uses numeric data types to show the NVL2 function.

SELECT first_name, employees,
NVL2(employees, employees, 0) AS Employee_Check
FROM customers;

Result:

FIRST_NAME EMPLOYEES EMPLOYEE_CHECK
John 4 4
Sally 10 10
Steve 15 15
Mark 23 23
Adam 55 55
Josh 1 1
Peter (null) 0
(null) (null) 0

The employee_check column shows the employees, or 0 if none are found. It works in a similar way to the character examples.

 

Example 4

This example uses NVL2 with DATE values.

SELECT first_name, start_date,
NVL2(start_date, start_date, '01-JAN-2000') AS Date_Check
FROM customers;

Result:

FIRST_NAME START_DATE DATE_CHECK
John 12/APR/2010 12/APR/2010
Sally 04/JUL/2011 04/JUL/2011
Steve 21/MAR/2009 21/MAR/2009
Mark 01/FEB/2001 01/FEB/2001
Adam (null) 01/JAN/2000
Josh 10/FEB/2012 10/FEB/2012
Peter 16/OCT/2012 16/OCT/2012
 (null) 16/OCT/2012 16/OCT/2012

It shows the date of 1 Jan 2000 if a NULL value is found.

 

Example 5

This example uses a mix of data types. The value_to_check is a number, and the other two parameters are strings.

SELECT first_name, employees,
NVL2(employees, 'Has some employees', 'Zero') AS Employee_Check
FROM customers;

Result:

FIRST_NAME EMPLOYEES EMPLOYEE_CHECK
John 4 Has some employees
Sally 10 Has some employees
Steve 15 Has some employees
Mark 23 Has some employees
Adam 55 Has some employees
Josh 1 Has some employees
Peter (null) Zero
(null) (null) Zero

The output is shown as expected. Employee_check shows two different text values, depending on the value of the employees column.

 

Examples of the NANVL Function

Here are some examples of the NANVL function. I find that examples are the best way for me to learn about code, even with the explanation above.

Let’s create a table to store some data first.

CREATE TABLE nanvl_test (
num_val NUMBER(10,3),
f_val FLOAT(3),
bf_val BINARY_FLOAT,
bd_val BINARY_DOUBLE);

INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (123.456, 234.567, 987.654, 135.789);

INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (0, 0, 'NaN', 'NaN');

Now, let’s see our examples.

Let’s see how the NANVL function works with each of the values in the table when translating to 0.

SELECT num_val, f_val, bf_val, bd_val,
NANVL(num_val, 9999) AS nan_num,
NANVL(f_val, 9999) AS nan_f,
NANVL(bf_val, 9999) AS nan_bf,
NANVL(bd_val, 9999) AS nan_bd
FROM nanvl_test;

Result:

NUM_VAL F_VAL BF_VAL BD_VAL NAN_NUM NAN_F NAN_BF NAN_BD
123.456 200 987.654 135.789 123 200 987.654 135.789
0 0 NaN NaN 0 0 9999.0 9999.0

The first row shows the original table columns and the NANVL function used on each of these columns. The values returned by NANVL are the same as each of the original columns because they all have a valid numeric value.

The second row is different. Two values are set to 0, and two are NaN (not a number). When the NANVL function runs against each of them, the two 0 values remain the same. They are unchanged.

However, the two NaN values are changed to 9999 as part of the NANVL function.

So, this example demonstrates that the NANVL function changes NaN values to other values. I could have entered any number in the second parameter other than 9999.

 

Similar Functions

Some functions which are similar to these functions are:

  • CASE – You can use the CASE statement, which provides IF THEN ELSE functionality, to do the same thing, but might not be as efficient.
  • DECODE – You can also use the DECODE function to do the same thing, but might not be as efficient.
  • COALESCE – Returns the first non-null expression in your list
  • REPLACE – This function replaces one value inside a string with another value.
  • NULLIF – Returns NULL if both parameters are equal.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.