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.