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 theexpression
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 whenexpression
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.