MySQL NULLIF() Function

The NULLIF() function compares two expressions and returns NULL if they are equal. Otherwise, it returns the first expression.

Syntax

NULLIF(expr1, expr2)

Parameter Values

• expr1: The first expression to compare.
• expr2: The second expression to compare with the first one.

Definition and Usage

• The NULLIF() function is used to compare two expressions.
• If the two expressions are equal, the function returns NULL.
• If they are not equal, it returns the first expression.

This can be useful in scenarios where you want to avoid returning a specific value under certain conditions (like division by zero).

Technical Details

• Works in: From MySQL 4.0

Examples

Example 1: Compare two identical numbers

SELECT NULLIF(25, 25);

Result:

NULL

In this case, since the two numbers are equal, the result is NULL.

Example 2: Compare a number and a string

SELECT NULLIF(25, "Hello");

Result:

25

Here, since the number 25 and the string "Hello" are not equal, the result is the first expression, which is 25.

Example 3: Compare two different strings

SELECT NULLIF("Hello", "world");

Result:

Hello

Since "Hello" and "world" are not equal, the function returns the first expression, "Hello".

Example 4: Compare two identical date values

SELECT NULLIF("2017-08-25", "2017-08-25");

Result:

NULL

Since the two dates are equal, the result is NULL.

Usage Notes

• NULLIF() is often used to prevent division by zero errors. For example, if you want to divide a number by a value, but if the value is zero, you can use NULLIF() to return NULL instead of attempting the division.