MySQL NULLIF Function with Examples

How to Use the MySQL NULLIFF() Function

In this tutorial, you will learn how to use the MySQL NULLIF() function, which compares two expressions and returns NULL if equal; otherwise returns the first expression.


The syntax of the NULLIF() function is as follow:

NULLIF (expression_1, expression_2)

NULLIF Examples

Example 1

The following statement compares two integers and returns as Message:

SELECT NULLIF (1, 2) AS Message;

Since integer 1 is not equal to 2, the NUlLIF() function returns 1, the first expression.

Example 2

The following statement returns NULL because both expressions of the NULLIF() function are equal:

SELECT NULLIF ("Apple", "Apple") AS Message;

Example 3

The following statement returns NULL because both expressions are equal:

SELECT NULLIF ("The tutorial of NULLIF function.", "THE TUTORIAL of NULLIF Function.") AS Message;

As you can see, both expressions are not the same if you consider that lowercase and uppercase are different; NULLIF() is not case-sensitive, so lowercase and uppercase are treated the same.

Example 4

Here is an example of using the SUM() function in the NULLIF():

SELECT NULLIF(SUM(1+2), 2) AS Message;

This statement returns 3. The integer 3 is the return value of SUM(1+2), which is greater than 2.

In this tutorial, you have learned how to use the MySQL NULLIF function. NULLIF() returns a null value if both expressions are equal; otherwise, it returns the first expression.

See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment