NVL function in SQL

3 min read · Feb 18, 2023

The NVL function in SQL is a tool for handling NULL values in database management. The NVL function takes two arguments, the first being the expression that is to be evaluated, and the second being the value that will be returned if the expression evaluates to NULL. The purpose of the NVL function is to provide a way to replace NULL values with a more meaningful or appropriate value in the context of a query.

The syntax of the NVL function is as follows:

NVL (expression, replace_value)

Where expression is the value that is to be evaluated and replace_value is the value that will be returned if expression is NULL.

The NVL function is useful in a variety of situations where NULL values need to be handled. For example, consider a database table that contains a column for the salary of employees. If an employee’s salary is not yet known, the value in the salary column might be NULL. However, when creating a query that summarizes the average salary of all employees, it may be more appropriate to use a default value instead of a NULL value. The NVL function can be used to accomplish this, as follows:

SELECT AVG(NVL(salary, 0)) FROM employees;

In this example, the NVL function is used to replace any NULL values in the salary column with a default value of 0. The AVG function is then used to calculate the average salary of all employees, including those for whom the salary value is NULL.

Another example of where the NVL function is useful is when concatenating strings. In some database management systems, concatenating a NULL value with a string will result in a NULL value being returned. This can be problematic in cases where a default value is desired instead of a NULL value. The NVL function can be used to replace any NULL values with a default string before concatenation, as follows:

SELECT first_name || ' ' || NVL(last_name, 'Unknown') FROM employees;

In this example, the NVL function is used to replace any NULL values in the last_name column with the string “Unknown”. The concatenation operator “||” is then used to concatenate the first_name and last_name columns.

The NVL function can also be used to handle NULL values in comparisons. In some database management systems, comparing a NULL value with any other value will result in NULL being returned, even if the comparison should evaluate to true or false. The NVL function can be used to replace NULL values with a default value before making comparisons, as follows:

SELECT * FROM employees WHERE NVL(salary, 0) > 50000;

In this example, the NVL function is used to replace any NULL values in the salary column with a default value of 0. The comparison operator “>” is then used to compare the salary column with the value 50000.

NVL, Oracle, SQL Server and Other Databases

The NVL Function is a widely used function in the Oracle database management system, and similar functions exist in other database management systems as well.

NVL is not available in SQL Server. NVL is a function that is specific to Oracle databases. In SQL Server, the equivalent function to NVL is ISNULL.

NVL is not available in MySQL. NVL is a function that is specific to Oracle databases. In MySQL, the equivalent function to NVL is IFNULL.

In conclusion, the NVL function in SQL is a powerful tool for handling NULL values in database management. The NVL function is commonly used to replace NULL values in calculations, string concatenations, and comparisons, but can also be used in a variety of other situations where NULL values need to be handled.