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.