NVL Function in Oracle SQL

Topic Introduction: In Oracle SQL, NVL (short for "Null Value") is a function used to replace a NULL value with a specified default value. It is often used to handle situations where you want to treat NULL values as if they were something else, such as a zero or an empty string. The NVL function takes two arguments: the first is the expression that may be NULL, and the second is the value to be returned if the expression is NULL.

The syntax for the NVL function is as follows:

NVL(expression, default_value)

expression: The expression or column that may contain NULL values.

default_value: The value to return if the expression is NULL.

Here's an example of how to use the NVL function in Oracle SQL:

SELECT employee_name, NVL(salary, 0) AS modified_salary

FROM employees;

In this example, if the salary column contains NULL for any employee, the NVL function will replace it with 0 in the result set. This ensures that you get a numeric value (0) instead of a NULL for the salary of employees with missing salary information.

You can also use NVL with non-numeric data types, such as strings. For instance, if you want to replace NULL values in a text column with an empty string:

SELECT NVL(first_name, 'No Name') AS modified_name

FROM employees;

In this case, if the first_name column contains NULL for any employees, NVL will replace it with 'No Name' in the result set.

It's important to note that Oracle provides an alternative to NVL called COALESCE, which can handle multiple expressions and return the first non-NULL value in the list. Here's an example using COALESCE:

SELECT employee_name, COALESCE(salary, 0) AS modified_salary

FROM employees;

In this example, if salary is NULL, COALESCE will return 0, just like NVL. However, COALESCE allows you to handle multiple columns or expressions in one go. we will discuss in detail about COALESCE function