SQL NVL

What is SQL NVL?

The SQL NVL function, often referred to as COALESCE in some database systems, is used to handle NULL values in a database query. It allows you to replace NULL values with a specified default value. NVL stands for "null value logic" and is used primarily to provide more meaningful data when NULL values are encountered in query results.

When you would use it

You would use the SQL NVL function when you want to deal with NULL values in a query by substituting them with a default value. This is particularly useful when you want to ensure that the result set displays meaningful data, or when NULL values might disrupt calculations, comparisons, or presentation.

Syntax

The syntax for the SQL NVL function is as follows:

NVL(expression, default_value)

• expression: The expression or column that you want to evaluate for NULL.
• default_value: The value to replace NULL with if the expression is NULL.

Parameter values

• expression: The expression or column you want to evaluate for NULL values.
• default_value: The value to use as a replacement when expression is NULL.

Example query

Suppose we have a table named "employees" with columns "employee_id," "employee_name," and "employee_salary." We want to retrieve the employee names and their salaries, replacing NULL salaries with 0:

SELECT employee_id, employee_name, NVL(employee_salary, 0) AS adjusted_salary
FROM employees;

In the above query, we use the NVL function to replace NULL salaries with 0, ensuring that all employees have a salary value in the result set.

Example table response

Assuming the "employees" table contains the following data:

| employee_id | employee_name | employee_salary |
|------------ |-------------- | --------------- |
| 1          | John         | 55000           |
| 2          | Alice        | NULL            |
| 3          | Bob          | 60000           |
| 4          | Carol        | 0               |
| 5          | David        | 75000           |

The query mentioned earlier would return the following result:

| employee_id | employee_name | adjusted_salary |
|------------ |-------------- | --------------- |
| 1          | John         | 55000           |
| 2          | Alice        | 0               |
| 3          | Bob          | 60000           |
| 4          | Carol        | 0               |
| 5          | David        | 75000           |

This result includes employee names and adjusted salaries, with NULL salaries replaced by 0.

Use cases

• Handling NULL values to ensure consistent and meaningful data in query results.
• Avoiding disruptions in calculations, comparisons, or presentation due to NULL values.
• Customizing how NULL values are displayed in the result set.

SQL languages this is available for

The SQL NVL function is not a standard SQL function, and its availability may vary between database management systems. It is commonly used in Oracle Database, but other database systems might have similar functions with different names. For example, in some systems, you may use the COALESCE function to achieve the same result. If you are using a specific database system, consult its documentation for the equivalent function or approach for handling NULL values.