NULLIF function usage in Oracle

The NULLIF function in SQL is used to compare two expressions. It returns NULL if the two expressions are equal; otherwise, it returns the first expression. It's essentially the opposite of the NVL function.

Here's the syntax:

NULLIF(expr1, expr2)

• expr1: The first expression to compare.
• expr2: The second expression to compare.

If expr1 is equal to expr2, the function returns NULL. Otherwise, it returns expr1.

Example:

SELECT NULLIF(column_name, 0) AS result FROM your_table;

This query returns NULL if the column_name is equal to 0; otherwise, it returns the value of column_name.

NULLIF is particularly useful when you want to avoid certain values in your result set, such as dividing by zero:

SELECT NULLIF(dividend, 0) / divisor AS result FROM your_table;

In this query, if dividend is 0, the NULLIF function returns NULL, preventing division by zero. Otherwise, it performs the division.