Updated March 8, 2023
Introduction to RedShift NVL
RedShift NVL expression will return the first expression value from the argument list which was not null, NVL function will return the empty result when our argument contains all null values. NVL function is equivalent to the coalesce function while using a big query, at the time of migrating our code from redshift we need to rewrite the function in a big query. The function is identical to the coalesce function, NVL expression is the synonym of coalesce function or expression. We can say that NVL and coalesce expressions are synonyms to each other.
Syntax of RedShift NVL
Below is the syntax of NVL expression in RedShift:
NVL (expression1, expression2,…., expressionN);
NVL (Argument1, Argument2,…., ArgumentN);
Select NVL (column_name1, column_name2) From table_name;
Parameter description syntax of NVL function:
- Expression1 to expressionN: This is defined as any expression which we have using with NVL function in redshift. For multiple values, the NVL function will return a first not-null value in the result.
- Table name: This is nothing but the name of the table which was we have using with the NVL function to retrieve the first not-null value.
- Select: Select is used to fetch data from a table by using the NVL function in redshift. Using column name with NVL function we can retrieve data from a table which was we have used in our query.
- Argument 1 to Argument N: Argument is nothing but an integer value that we have passing with NVL function in redshift. If suppose we have passing three-argument with NVL function and if the first argument contains null value then NVL function will return next not null value.
- Column name: This is the column name of the table which was we have used with NVL function. If we want to fetch data from a table using the NVL function in redshift then we need to pass column name.
- NVL: NVL is the function or expression name which returns the first not-null value as a result. NVL function is the identical to coalescing function in redshift.
How NVL works in RedShift?
- RedShift NVL function will evaluate values or arguments from left to right to find the not-null value or argument.
- The function is used to return the first, not null value from the table column or from the list of arguments which was we have used in the query.
- At the time of finding the first, not null value, the NVL function will not evaluate the remaining argument from the column or argument list which was used in our query.
- NVL function will not treat zero as a not null value, zero is considered as valid, not null value with NVL in redshift.
The below example shows that the NVL function will not treat zero as a not null value, zero is considering as valid, not null value is as follows.
Code:
Select NVL (NULL, 0, NULL, 10, 20);
Output:
- In the above example, we have used the argument list as (NULL, 0, NULL, 10, 20). In the first argument, we have used null value and in the second argument, we have used zero value. So NVL function will return the result as zero which was not null because the NVL function is considering zero as a not a null value.
- We can also use order by and group by condition with NVL function to extract the data from a table.
- As we know that NVL function is identical with coalescing function in redshift. NVL function will return the null values if our argument list contains all the null values.
- NVL function will not evaluate all lists when it has found the not null value from the argument list. If suppose our argument list contains five numbers like (NULL, NULL, 10, NULL, 50).
- In the above list, we can see that we have provided 10 values which were not null in the third position so the result of this argument using the NVL function is 10. After retrieving the result as 10 it will not evaluate the other argument list.
- The default column name of NVL expression in redshift coalesces. At the time of executing the NVL function on the argument list it will show the output column name as coalesce.
The below example shows the column name of NVL expression in RedShift is coalesce.
Code:
Select NVL (15, 20, NULL, 25, NULL);
Output:
- In the above example, we can see that we have used NVL expression with argument list but in result, it will showing column name as coalesce.
Examples of RedShift NVL
Given below are the examples mentioned:
Example #1
NVL function using a single value as the argument list.
- The below example shows that the NVL function using a single value as argument list is as follows. We have using 20 values as an argument list with the NVL function.
Code:
Select NVL (20);
Output:
Example #2
NVL function using multiple values as the argument list.
- The below example shows that NVL functions using multiple values as argument lists is as follows. We have used (20, 25, 30, 35, 40) values as argument lists with NVL function.
Code:
Select NVL (20, 25, 30, 35, 40);
Output:
Example #3
NVL function using null value as the argument list.
- The below example shows that the NVL function using all null values as argument list is as follows. We have used (NULL, NULL, NULL, NULL) values as argument list with NVL function.
Code:
Select NVL (NULL, NULL, NULL, NULL);
Output:
Example #4
NVL function using two-column as the argument list.
- Below example shows that NVL function using two-column as argument list are as follows. We have using date1 and date2 columns as argument lists with NVL functions.
Code:
Select nvl (date1, date2) from nvl_test;
Output:
Example #5
NVL function using the single-column as the argument list.
- The below example shows that the NVL function using a single column as an argument list is as follows. We have using the date1 column as an argument list with the NVL function.
Code:
Select nvl (date1, date2) from nvl_test;
Output:
Conclusion
RedShift NVL function is very useful and important to find the first not null value from column or argument list. NVL function is identical to the coalesce function in redshift. If suppose all column value contains the not null value then NVL function will return the empty result.
Recommended Articles
This is a guide to RedShift NVL. Here we discuss the introduction, how NVL works in RedShift? and examples for better understanding. You may also have a look at the following articles to learn more –
