NULLIF Function in Oracle SQL

Topic Introduction: In Oracle SQL, the NULLIF function is used to compare two expressions and return a NULL value if the two expressions are equal. If the expressions are not equal, it returns the first expression. Essentially, NULLIF is a way to handle cases where you want to return NULL if two values are equal, otherwise, you return the first value. This function is particularly useful when you want to avoid division by zero errors or handle specific cases where NULL should be returned.

The syntax for the NULLIF function is as follows:

NULLIF(expression1, expression2)

expression1: The first expression to compare.

expression2: The second expression to compare.

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

SELECT employee_name, 

       NULLIF(salary, 0) AS adjusted_salary

FROM employees;

In this example:

If the salary column is not equal to 0 for an employee, the NULLIF function will return the actual salary value.

If the salary column is equal to 0 for an employee, the NULLIF function will return NULL.

This can be useful to avoid division by zero errors or to return NULL in cases where certain values should be treated as missing or invalid.

Here's another example where NULLIF is used to handle NULL values:

SELECT product_name,

       NULLIF(sale_price, cost_price) AS profit_margin

FROM products;

In this example:

If sale_price is equal to cost_price for a product, the NULLIF function will return NULL for the profit_margin.

If sale_price is not equal to cost_price for a product, it will return the actual profit margin value.

NULLIF is a simple but handy function for handling specific cases where you want to return NULL when two values are equal.