Oracle NVL,NVL2, COALESCE , NULLIF functions to handle NULL values

NVL functions are used in SQL (primarily in Oracle) to handle NULL values by replacing them with a specified value. Here’s how they work:

1. NVL Function (Oracle)

The NVL function replaces NULL with a specified value.

Syntax:

NVL(expression, replacement_value)

• expression: The value or column to be checked.
• replacement_value: The value to replace NULL if the expression is NULL.

Example:

SELECT NVL(salary, 0) AS adjusted_salary FROM employees;

• If salary is NULL, it is replaced with 0.

---

2. NVL2 Function

The NVL2 function provides more flexibility by allowing different values based on whether the expression is NULL or not.

NVL2(expression, value_if_not_null, value_if_null)

• If expression is NOT NULL, it returns value_if_not_null.
• If expression is NULL, it returns value_if_null.

Example:

SELECT NVL2(commission, 'Has Commission', 'No Commission') AS status
FROM employees;

• If commission is NULL, it returns "No Commission", otherwise "Has Commission".

---

3. COALESCE Function (Alternative)

The COALESCE function returns the first non-null value from a list of expressions.

COALESCE(expression1, expression2, ..., expressionN)

• Returns the first non-null value from the list.

Example:

SELECT COALESCE(salary, bonus, 0) AS final_income FROM employees;

• If salary is NULL, it checks bonus; if both are NULL, it returns 0.

---

4. NULLIF Function

The NULLIF function returns NULL if both expressions are equal; otherwise, it returns the first expression.

Syntax:

NULLIF(expression1, expression2)

Example:

SELECT NULLIF(sales, target) FROM performance;

• If sales equals target, it returns NULL, otherwise sales.

---

Which One to Use?

Function	Use Case
NVL	Replace NULL with a specific value
NVL2	Return different values based on NULL status
COALESCE	Return the first non-null value from multiple columns
NULLIF	Compare two values and return NULL if they are equal